使用多部分列标识符 [英] Using multipart column identifiers

问题描述

我确定这必须在某处记录，但对于我的生活，我似乎无法找到任何解释该行为的实际文档.

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.

采用 4 种引用表格的方法(我认为没有更多方法，但请随时纠正我):

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

1. 当前数据库
2. 远程数据库
3. 链接服务器
4. 同义词

他们在使用多部分列标识符时的行为似乎有所不同，我试图理解其背后的原因.我已经测试了各种类型的 SELECT 语句:

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:

当前数据库

作品

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;

即使这样也行！(显然只有在使用 dbo Schema 时，但仍然如此)

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

SELECT Schema.Table.Column FROM Table;

远程数据库

作品

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;

失败

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

链接服务器

作品

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;

失败

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.**

同义词

作品

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;

即使这样也行！(显然只有在使用 dbo Schema 时，但仍然如此)

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

SELECT SynonymSchema.SynonymName.Column FROM SynonymName;

1. 我试图理解为什么某些多部分标识符在以一种方式使用时有效(例如针对本地数据库的架构)，但在使用另一种方式时会失败(例如针对远程数据库/链接服务器的架构).
2. 您是否应该始终使用别名来确保一切正常?

任何建议都将受到高度赞赏，尤其是有关一刀切"场景的设计背后原因和最佳实践建议的官方文档的指针(我目前推测这是别名路线).

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).

推荐答案

最佳实践 - 为您的表设置别名并对列名使用由两部分组成的标识符 - 第一部分是表别名，第二部分是列名.

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.

对列使用两部分标识符意味着您确切地知道该列属于哪个表，即使您添加了 join/apply 子句，或者只是将具有相同名称的列添加到现有表之一查询中的表，您根本不需要更改查询.此外，您现在只有一个位置来确定表的来源.

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.

对于评论中的链接，不推荐使用三部分或四部分标识符作为列(感谢@larnu).

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.

请注意，此答案中的 table 一词可与视图、表值函数、表变量等互换.

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

这篇关于使用多部分列标识符的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！