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

The objects "DBA_2014.Sch2.Table_1" and "DBA_2014.Sch1.Table_1" in the FROM clause have the same exposed names. Use correlation names to distinguish them. — user2811633, Oct 29 '15 at 21:45
Can you try your code with SQL Server 2000 or compatibility level 80? — Lukasz Szozda, Oct 29 '15 at 22:01
Unfortunately that's not an option as we are working with newer versions of SQL (2005 onwards). Thanks for the suggestion though. I wonder if schemaname in name qualification is ignored for some reason — user2811633, Oct 29 '15 at 22:14

score 3 · Accepted Answer · answered Oct 29 '15 at 22:13

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

Thanks for pointing to the original post. Some extra research and details in the post helped me in understanding this little more. — user2811633, Oct 30 '15 at 18:23

Emacs User · Answer 2 · 2015-10-29T22:04:13.487

1

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

edited Oct 29 '15 at 22:04

answered Oct 29 '15 at 21:47

Emacs User

1,457
1
12
19

I have added the error SQL server produces for the above query in the comment – user2811633 Oct 29 '15 at 21:50
"Correlation naming". Ok, interesting SQL error message. Most everyone I know just refers to these (your t1, t2) as "table aliases", and they are indeed the fix to this problem. – Philip Kelley Oct 29 '15 at 22:07
1

@PhilipKelley alias is right, but I'm referring to the alias's purpose here, which is correlating exposed names. – Emacs User Oct 30 '15 at 02:48

score 0 · Answer 3 · answered Oct 29 '15 at 21:50

0

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

Does this work?

answered Oct 29 '15 at 21:50

proka

21
1
4

SQL Server Object Names

3 Answers3

Linked