sql - 在表变量上创建索引

Question

可以在 SQL Server 2000 中的表变量上创建索引吗？

即

DECLARE @TEMPTABLE TABLE (
     [ID] [int] NOT NULL PRIMARY KEY
    ,[Name] [nvarchar] (255) COLLATE DATABASE_DEFAULT NULL 
)

我可以在名称上创建索引吗？

Answer 1

这个问题被标记为 SQL Server 2000，但为了在最新版本上进行开发的人们的利益，我将首先解决这个问题。

SQL Server 2014

除了下面讨论的添加基于约束的索引的方法之外，SQL Server 2014 还允许在表变量声明上使用内联语法直接指定非唯一索引。

下面是示例语法。

/*SQL Server 2014+ compatible inline index syntax*/
DECLARE @T TABLE (
C1 INT INDEX IX1 CLUSTERED, /*Single column indexes can be declared next to the column*/
C2 INT INDEX IX2 NONCLUSTERED,
       INDEX IX3 NONCLUSTERED(C1,C2) /*Example composite index*/
);

过滤索引和包含列的索引目前无法使用此语法声明，但是SQL Server 2016进一步放宽了这一点。从 CTP 3.1 开始，现在可以为表变量声明过滤索引。通过 RTM，可能的情况是也允许包含列，但当前位置是它们 "will likely not make it into SQL16 due to resource constraints"

/*SQL Server 2016 allows filtered indexes*/
DECLARE @T TABLE
(
c1 INT NULL INDEX ix UNIQUE WHERE c1 IS NOT NULL /*Unique ignoring nulls*/
)

<小时/>

SQL Server 2000 - 2012

Can I create a index on Name?

简短回答:是的。

DECLARE @TEMPTABLE TABLE (
  [ID]   [INT] NOT NULL PRIMARY KEY,
  [Name] [NVARCHAR] (255) COLLATE DATABASE_DEFAULT NULL,
  UNIQUE NONCLUSTERED ([Name], [ID]) 
  ) 

更详细的答案如下。

SQL Server 中的传统表可以具有聚集索引，也可以采用heaps 结构。 。

聚集索引可以声明为唯一以禁止重复的键值，也可以默认为非唯一。如果不唯一，则 SQL Server 会默默添加 uniqueifier任何重复的键，使它们唯一。

非聚集索引也可以显式声明为唯一。否则对于非唯一情况 SQL Server adds the row locator (聚集索引键或 RID 对于堆)到所有索引键(不仅仅是重复项)，这再次确保它们是唯一的。

在 SQL Server 2000 - 2012 中，只能通过创建 UNIQUE 或 PRIMARY KEY 约束来隐式创建表变量的索引。这些约束类型之间的区别在于主键必须位于不可为空的列上。参与唯一约束的列可以为空。 (尽管 SQL Server 在存在 NULL 的情况下对唯一约束的实现并不符合 SQL 标准中指定的要求)。另外，一张表只能有一个主键，但有多个唯一约束。

这两个逻辑约束都是通过唯一索引物理实现的。如果没有明确指定，则 PRIMARY KEY 将成为聚集索引和非聚集的唯一约束，但可以通过指定 CLUSTERED 或 NONCLUSTERED 来覆盖此行为显式地使用约束声明(示例语法)

DECLARE @T TABLE
(
A INT NULL UNIQUE CLUSTERED,
B INT NOT NULL PRIMARY KEY NONCLUSTERED
)

由于上述原因，可以在 SQL Server 2000 - 2012 中的表变量上隐式创建以下索引。

+-------------------------------------+-------------------------------------+
|             Index Type              | Can be created on a table variable? |
+-------------------------------------+-------------------------------------+
| Unique Clustered Index              | Yes                                 |
| Nonunique Clustered Index           |                                     |
| Unique NCI on a heap                | Yes                                 |
| Non Unique NCI on a heap            |                                     |
| Unique NCI on a clustered index     | Yes                                 |
| Non Unique NCI on a clustered index | Yes                                 |
+-------------------------------------+-------------------------------------+

最后一个需要一些解释。在此答案开头的表变量定义中，Name 上的非唯一 非聚集索引由 上的唯一 索引模拟Name,Id(回想一下，SQL Server 无论如何都会默默地将聚集索引键添加到非唯一 NCI 键中)。

还可以通过手动添加 IDENTITY 列作为唯一符来实现非唯一聚集索引。

DECLARE @T TABLE
(
A INT NULL,
B INT NULL,
C INT NULL,
Uniqueifier INT NOT NULL IDENTITY(1,1),
UNIQUE CLUSTERED (A,Uniqueifier)
)

但这并不是对 SQL Server 中非唯一聚集索引通常实际实现方式的准确模拟，因为这会向所有行添加“唯一标识符”。不仅仅是那些需要它的人。