0

I'm sure this has to be documented SOMEWHERE but for the life of me I just can't seem to find any actual documentation explaining the behavior.

Taking the 4 ways to reference tables (I don't believe there are more but feel free to correct me):

  1. Current Database
  2. Remote Database
  3. Linked Server
  4. Synonym

Their behavior when using multi-part column identifiers seem to differ and I'm trying to understand the reasoning behind it. I've tested the various types of SELECT statements:

Current Database

Works

SELECT Column FROM Schema.Table;
SELECT Table.Column FROM Schema.Table;
SELECT Schema.Table.Column FROM Schema.Table;
SELECT Alias.Column FROM Schema.Table AS Alias;

Even this works! (Obviously only when using dbo Schema, but still)

SELECT Schema.Table.Column FROM Table;

Remote Database

Works

SELECT Column FROM RemoteDB.Schema.Table;
SELECT Table.Column FROM RemoteDB.Schema.Table;
SELECT RemoteDB.Schema.Table.Column FROM RemoteDB.Schema.Table;
SELECT Alias.Column FROM RemoteDB.Schema.Table AS Alias;

Fails

SELECT Schema.Table.Column FROM RemoteDB.Schema.Table;
The multi-part identifier "Schema.Table.Column" could not be bound.

Linked Server

Works

SELECT Column FROM LinkedServer.RemoteDB.Schema.Table;
SELECT Table.Column FROM LinkedServer.RemoteDB.Schema.Table;
SELECT Alias.Column FROM LinkedServer.RemoteDB.Schema.Table AS Alias;

Fails

SELECT Schema.Table.Column FROM LinkedServer.RemoteDB.Schema.Table;
The multi-part identifier "Schema.Table.Column" could not be bound.

SELECT RemoteDB.Schema.Table.Column FROM LinkedServer.RemoteDB.Schema.Table;
The multi-part identifier "RemoteDB.Schema.Table.Column" could not be bound.

SELECT LinkedServer.RemoteDB.Schema.Table.Column FROM LinkedServer.RemoteDB.Schema.Table;
The multi-part identifier "LinkedServer.RemoteDB.Schema.Table.Column" could not be bound.
**I believe this fails are you're only allowed a maximum of 4 parts in the identifier?**
**Read this somewhere but nothing authoritive.  Would appreciate a reference.**

Synonym

Works

SELECT Column FROM SynonymName;
SELECT Column FROM SynonymSchema.SynonymName;
SELECT SynonymName.ColumnName FROM SynonymSchema.SynonymName;
SELECT SynonymSchema.SynonymName.Column FROM SynonymSchema.SynonymName;
SELECT Alias.Column FROM SynonymSchema.SynonymName AS Alias;

Even this works! (Obviously only when using dbo Schema, but still)

SELECT SynonymSchema.SynonymName.Column FROM SynonymName;
  1. I'm trying to understand why certain multi-part identifiers work when used one way (like schema against local db) but then fail when used another way (like schema against a remote db/linked server).
  2. Should you rather always use aliases to ensure things will always work?

Any advice would be highly appreciated, especially pointers to official documentation as to the reason behind the design and best practice advice for a "one size fits all" scenario (which I'm currently going to surmise to be the alias route).

halfer
  • 19,824
  • 17
  • 99
  • 186
Storm
  • 1,848
  • 4
  • 20
  • 39
  • 2
    Don't ever use 3 part(+) naming with your column names in `SELECT`. Using syntax like `SELECT dbo.MyTable.MyColumn FROM dbo.MyTable;` is deprecated and your SQL will fail one day in the future. If you are referring to a column from a specific table, the best way is the alias the table, and then prefix the column with that alias: `SELECT MT.MyColumn FROM dbo.MyTable MT;`. The only time you're liking to see 3 part+ naming for a column is when using `sp_rename`, as it's required then, and that's because the column's name is a string literal parameter. – Thom A Oct 29 '19 at 11:48
  • @Larnu: Thanks for this, do you have any documentation link for the deprecation please? I feel that alone would provide quite a bit of insight.. – Storm Oct 29 '19 at 12:48
  • 1
    [Deprecated Database Engine Features in SQL Server 2017](https://learn.microsoft.com/en-us/sql/database-engine/deprecated-database-engine-features-in-sql-server-2017?view=sql-server-ver15): under *"Three-part and four-part column references."*. I can't recall what version it was added to the deprecation list – Thom A Oct 29 '19 at 12:53
  • So awesome, thanks, this is absolutely invaluable. – Storm Oct 31 '19 at 06:29

1 Answers1

2

Best practice - Alias your tables and use a two parts identifier for column names - first part is the table alias and the second one is the column name.

Why? because:

  • Using a single part identifier will break as soon as the query contains a join (or apply), and that column name happens to belong to more than one table.

  • Using more than two parts identifier for a column will force you to write most of the identifier twice - once for the column and once for the table. If anything changes (like table moved to a different schema, linked server name changed, synonym changes) you now have to change your query in (at least) two places.

  • Using a two parts identifier for a column means you know exactly what table this column belongs to, and even if you add a join / apply clause, or simply add a column with the same name to one of the existing tables in the query, you don't need to change the query at all. Also, you now have only one place that determines where the table comes from.

  • Using three or four parts identifier for columns is deprecated (thanks @larnu) for the link in the comments.

Most importantly - columns belong to tables. They don't belong to servers, databases or schemas.

Please note that the word table in this answer is interchangeable with view, table-valued function, table-variable etc'.

Zohar Peled
  • 79,642
  • 10
  • 69
  • 121