在表变量上创建索引 [英] Creating an index on a table variable

问题描述

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

Can you create a index on a table variable in SQL Server 2000?

即

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

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

Can I create a index on Name?

推荐答案

这个问题被标记为SQL Server 2000，但为了使用最新版本开发人员的利益，我将解决该问题第一个。

The question is tagged SQL Server 2000 but for the benefit of people developing on the latest version I'll address that first.

SQL Server 2014

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

In addition to the methods of adding constraint based indexes discussed below SQL Server 2014 also allows non unique indexes to be specified directly with inline syntax on table variable declarations.

示例语法如下。

/*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 可能是包含列也是允许的情况但当前位置是由于资源限制，可能不会进入SQL16

Filtered indexes and indexes with included columns can not currently be declared with this syntax however SQL Server 2016 relaxes this a bit further. From CTP 3.1 it is now possible to declare filtered indexes for table variables. By RTM it may be the case that included columns are also allowed but the current position is that they "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?

<简短回答：是。

Short answer: Yes.

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

更详细的答案如下。

SQL Serv中的传统表er可以具有聚簇索引，也可以构造为堆。

Traditional tables in SQL Server can either have a clustered index or are structured as heaps.

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

Clustered indexes can either be declared as unique to disallow duplicate key values or default to non unique. If not unique then SQL Server silently adds a uniqueifier to any duplicate keys to make them unique.

非聚簇索引也可以显式声明为唯一。否则对于非唯一情况SQL Server 添加行定位器（聚簇索引键或 RID （堆）到所有索引键（不仅仅是重复）这再次确保它们是唯一的。

Non clustered indexes can also be explicitly declared as unique. Otherwise for the non unique case SQL Server adds the row locator (clustered index key or RID for a heap) to all index keys (not just duplicates) this again ensures they are unique.

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

In SQL Server 2000 - 2012 indexes on table variables can only be created implicitly by creating a UNIQUE or PRIMARY KEY constraint. The difference between these constraint types are that the primary key must be on non nullable column(s). The columns participating in a unique constraint may be nullable. (though SQL Server's implementation of unique constraints in the presence of NULLs is not per that specified in the SQL Standard). Also a table can only have one primary key but multiple unique constraints.

这两个逻辑约束都是使用唯一索引物理实现的。如果未明确指定，则 PRIMARY KEY 将成为聚簇索引和非聚簇的唯一约束，但可以通过指定 CLUSTERED 或 NONCLUSTERED 显式使用约束声明（示例语法）

Both of these logical constraints are physically implemented with a unique index. If not explicitly specified otherwise the PRIMARY KEY will become the clustered index and unique constraints non clustered but this behavior can be overridden by specifying CLUSTERED or NONCLUSTERED explicitly with the constraint declaration (Example syntax)

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

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

As a result of the above the following indexes can be implicitly created on table variables in 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                                 |
+-------------------------------------+-------------------------------------+

最后一个需要一些解释。在此答案开头的表变量定义中， c>名称上的非唯一非聚集索引由唯一 上的索引名称，Id （回想一下，SQL Server会默默地将聚集索引键添加到非唯一NCI键中。）

The last one requires a bit of explanation. In the table variable definition at the beginning of this answer the non unique non clustered index on Name is simulated by a unique index on Name,Id (recall that SQL Server would silently add the clustered index key to the non unique NCI key anyway).

也可以通过手动添加 IDENTITY 列来充当唯一符来实现非唯一聚簇索引。

A non unique clustered index can also be achieved by manually adding an IDENTITY column to act as a uniqueifier.

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中实现，因为这会将唯一化程序添加到所有行。不只是需要它的那些。

But this is not an accurate simulation of how a non unique clustered index would normally actually be implemented in SQL Server as this adds the "Uniqueifier" to all rows. Not just those that require it.

这篇关于在表变量上创建索引的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！