SQL Server对象名称 [英] SQL Server Object Names

问题描述

我想知道是否有人可以解释在联接中唯一标识sql服务器对象的概念.

I am wondering if someone can explain the concept of uniquely identifying sql server objects in a join.

在我的示例中，有2个模式和2个表(但名称相同).我的假设是，即使两个模式之间的表名可能相同，但只要使用全限定名databasename.schemaname.objectname引用它们，SQL Server就应该能够区分出它们. 但是似乎并非如此，解决方法是使用别名.

In my example there are 2 schemas and 2 tables (but with same name). My assumption was that even though table name might be same between 2 schemas, as long as they are referenced with their full qualified name databasename.schemaname.objectname, SQL server should be able to make out the difference. However that does not seem to be the case and the workaround for this is to use alias.

如果有人可以解释或指出一些有关为什么sql server无法唯一标识这些的文献，我将不胜感激.

I would appreciate If someone can explain or point out to some literature around why sql server cannot uniquely identity these.

CREATE SCHEMA [Sch1]
GO

CREATE SCHEMA [Sch2]
GO

CREATE TABLE [Sch1].[Table_1](
    [ID] [int] NULL,
    [DESC] [nchar](10) NULL
) ON [PRIMARY]
GO

CREATE TABLE [Sch2].[Table_1](
    [ID] [int] NULL,
    [DESC] [nchar](10) NULL
) ON [PRIMARY]
GO


Select *
From Sch1.Table_1 
Join Sch2.Table_1
    on Sch1.Table_1.Id = Sch2.Table_1.Id

推荐答案

SQL Server 支持多路部件标识符:

The SQL Server supports muliti-part identifiers:

linked_server.db_name.schema.table_name

您的情况是:

Select *
From Sch1.Table_1 
Join Sch2.Table_1
    on Sch1.Table_1.Id = Sch2.Table_1.Id

现在您想知道为什么SQL Server无法区分它们:

Now you wonder why SQL Server cannot differentiate between them:

Sch1.Table_1  != Sch2.Table_1

这种情况是因为SQL Server使用了称为 exposed name 的东西.

The case is because of SQL Server use something called exposed name.

公开名称

是多部分表名称的最后一部分(如果没有) 别名)或别名(如果有的话)

which is the last part of the multi-part table name (if there is no alias), or alias name when present

返回查询时，您已经公开了名称Table_1和Table_1，它们是重复的，您需要使用别名.

Returning to your query you have exposed names Table_1 and Table_1 which are duplicates and you need to use aliases.

来自 SQL Server 2005+ :

From SQL Server 2005+:

重复表格检测算法已相应更改， 因此，具有相同公开名称的任何表都将被考虑 重复

Duplicate table detection algorithm has been changed correspondingly, so that any tables with the same exposed names will be considered duplicates

我怀疑您的代码可以与SQL Server 2000一起使用，但我无法确定是否对其进行检查.

I suspect that your code could work with SQL Server 2000 but I cannot check it for sure.

有关更多信息，请阅读 Msg 1013

For more info read Msg 1013

这篇关于SQL Server对象名称的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！