SQL SQL点符号

SQL dot notation

提问by user2343837

Can someone please explain to me how SQL Server uses dot notation to identify
the location of a table? I always thought that the location is Database.dbo.Table
But I see code that has something else in place of dbo, something like:
DBName.something.TableCan someone please explain this?

有人可以向我解释 SQL Server 如何使用点表示法来标识
表的位置吗？我一直认为该位置是Database.dbo.Table
但我看到代码中有其他东西代替dbo，例如：
DBName.something.Table有人可以解释一下吗？

回答by Szymon

This is a database schema. Full three-part name of a table is:

这是一个数据库模式。表的完整三部分名称是：

databasename.schemaname.tablename

For a default schema of the user, you can also omit the schema name:

对于用户的默认架构，您还可以省略架构名称：

databasename..tablename

You can also specify a linked server name:

您还可以指定链接服务器名称：

servername.databasename.schemaname.tablename

You can read more about using identifiers as table names on MSDN:

您可以在MSDN上阅读有关使用标识符作为表名的更多信息：

The server, database, and owner names are known as the qualifiers of the object name. When you refer to an object, you do not have to specify the server, database, and owner. The qualifiers can be omitted by marking their positions with a period. The valid forms of object names include the following:

server_name.database_name.schema_name.object_name

server_name.database_name..object_name

server_name..schema_name.object_name

server_name...object_name

database_name.schema_name.object_name

database_name..object_name

schema_name.object_name

object_name

An object name that specifies all four parts is known as a fully qualified name. Each object that is created in Microsoft SQL Server must have a unique, fully qualified name. For example, there can be two tables named xyz in the same database if they have different owners.

Most object references use three-part names. The default server_name is the local server. The default database_name is the current database of the connection. The default schema_name is the default schema of the user submitting the statement. Unless otherwise configured, the default schema of new users is the dbo schema.

服务器、数据库和所有者名称被称为对象名称的限定符。引用对象时，不必指定服务器、数据库和所有者。可以通过用句点标记它们的位置来省略限定符。对象名称的有效形式包括：

server_name.database_name.schema_name.object_name

server_name.database_name..object_name

server_name..schema_name.object_name

服务器名称...对象名称

database_name.schema_name.object_name

数据库名称..对象名称

schema_name.object_name

对象名称

指定所有四个部分的对象名称称为完全限定名称。在 Microsoft SQL Server 中创建的每个对象都必须具有唯一的完全限定名称。例如，如果它们的所有者不同，则同一个数据库中可以有两个名为 xyz 的表。

大多数对象引用使用由三部分组成的名称。默认 server_name 是本地服务器。默认的 database_name 是连接的当前数据库。默认 schema_name 是提交语句的用户的默认架构。除非另外配置，否则新用户的默认架构是 dbo 架构。

回答by Nicholas Carey

What @Szymon said. You should also make a point of alwaysschema-qualifying object references (whether table, view, stored procedure, etc.) Unqualified object references are resolved in the following manner:

@Szymon 所说的。您还应该注意总是模式限定的对象引用（无论是表、视图、存储过程等）。未限定的对象引用按以下方式解析：

• Probe the namespace of the current database for an object of the specified name belonging to the default schema of the credentials under which the current connection is running.

• If not found, probe the namespace of the current database for an object of the specified name belonging to the dboschema.

• 探测当前数据库的命名空间，以查找属于当前连接正在运行的凭据的默认架构的指定名称的对象。

• 如果未找到，则探测当前数据库的命名空间以查找属于该dbo模式的具有指定名称的对象。

And if the object reference is to a stored procedure whose name begins with sp_, it's worse, as two more steps are added to the resolution process (unless the references is database-qualified): the above two steps are repeated, but this time, looking in the database masterinstead of the current database.

如果对象引用是名称以 开头的存储过程sp_，则情况更糟，因为解析过程中添加了另外两个步骤（除非引用是数据库限定的）：重复上述两个步骤，但这一次，看在数据库中master而不是当前数据库中。

So a query like

所以像这样的查询

select *
from foo

requires two probes of the namespace to resolve foo(assuming that the table/view is actually dbo.foo): first under your default schema (john_doe.foo) and then, not being found, under dbo(dbo.foo'), whereas

需要解析命名空间的两个探测器foo（假设表/视图实际上是dbo.foo）：首先在您的默认架构 ( john_doe.foo) 下，然后在dbo( dbo.foo')下找不到，而

select *
from dbo.foo

is immediately resolved with a single probe of the namespace.

使用命名空间的单个探测立即解决。

This has 3 implications:

这有 3 个含义：

1. The redundant lookups are expensive.

2. It inhibits query plan caching, as every execution has to be re-evaluated, meaning the query has to be recompiled for every execution (and that takes out compile-time locks).

3. You will, at one point or another, shoot yourself in the foot, and inadvertently create something under your default schema that is supposed to exist (and perhaps already does) under the dboschema. Now you've got two versions floating around.

   At some point, you, or someone else (usually it happens in production) willrun a query or execute a stored procedure and get...unexpected results. It will take you quite some time to figure out that there are two [differing] versions of the same object, and which one gets executed depends on their user credentials and whether or not the reference was schema-qualified.

1. 冗余查找代价高昂。

2. 它禁止查询计划缓存，因为每次执行都必须重新评估，这意味着必须为每次执行重新编译查询（这会消除编译时锁定）。

3. 在某一时刻，您会用脚​​射击自己，并在不经意间在您的默认模式下创建一些应该存在（并且可能已经存在）在dbo模式下的东西。现在你有两个版本。

   在某些时候，您或其他人（通常发生在生产中）将运行查询或执行存储过程并得到...意外的结果。您需要花费相当长的时间才能确定同一个对象有两个 [不同的] 版本，执行哪个版本取决于他们的用户凭据以及引用是否符合模式。

Always schema-qualify unless you have a real reason not to.

除非你有真正的理由不这样做，否则总是模式限定。

That being said, it can sometimes be useful, for development purposes to be able to maintain the "new" version of something under your personal schema and the "current" version under the 'dbo' schema. It makes it easy to do side-by-side testing. However, it's not without risk (which see above).

话虽如此，出于开发目的，能够在您的个人模式下维护某些内容的“新”版本和“dbo”模式下的“当前”版本有时可能很有用。它使并行​​测试变得容易。然而，这并非没有风险（见上文）。

回答by yogeshwar gutte

When SQL sees the syntax it will first look at the current users schema to see if the table exists, and will use that one if it does. If it doesn't then it looks at the dbo schema and uses the table from there

当 SQL 看到语法时，它将首先查看当前用户模式以查看该表是否存在，如果存在则使用该模式。如果没有，那么它会查看 dbo 模式并使用那里的表