SQL Server Object Names

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

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.

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

Solution

The SQL Server supports muliti-part identifiers:

linked_server.db_name.schema.table_name

In your case you have:

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

Now you wonder why SQL Server cannot differentiate between them:

Sch1.Table_1  != Sch2.Table_1

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

exposed name

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

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

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

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

For more info read Msg 1013