SQL Server Object Names

Question

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

Answer 1

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

Does this work?

Answer 2

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

Answer 3

As far as I can tell, I don't see any errors in your sample code. Please explain in detail what errors you're encountering.

As for the four-part naming convention. the full object name syntax is:

server.database.schema.object

So a complete usage would be, for example:

select * from servername.databasename.Sch1.Table_1

or

select * from servername.databasename.Sch2.Table_2

from which you can ignore any part as long as there is no ambiguity. Therefore in your example you can ignore severname and databasename as they are the same. But you cannot ignore schema names as they are not.

Addendum:

Based on error message you posted later, you need to employ correlation naming on the join syntax:

select * 
from Sch1.Table_1 as t1
inner join Sch2.Table_1 as t2 on t1.ID=t2.ID