SQL 在表变量上创建索引

Creating an index on a table variable

提问by GordyII

Can you create a indexon a table variable in SQL Server 2000?

你能index在表中创建一个变量SQL Server 2000吗？

i.e.

IE

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

Can I create a index on Name?

我可以在 Name 上创建索引吗？

回答by Martin Smith

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 2000，但为了在最新版本上开发的人们的利益，我将首先解决这个问题。

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 还允许使用表变量声明上的内联语法直接指定非唯一索引。

Example syntax for that is below.

示例语法如下。

/*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*/
);

Filtered indexes and indexes with included columns can not currently be declared with this syntax however SQL Server 2016relaxes this a bit further. From CTP 3.1 it is now possible to declare filtered indexes for table variables. By RTM it maybe 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进一步放宽了这一点。从 CTP 3.1 开始，现在可以为表变量声明过滤索引。通过 RTM，可能也允许包含的列，但目前的情况是它们“由于资源限制可能不会进入 SQL16”

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

SQL Server 2000 - 2012

Can I create a index on Name?

我可以在 Name 上创建索引吗？

Short answer: Yes.

简短的回答：是的。

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

A more detailed answer is below.

更详细的答案如下。

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

SQL Server 中的传统表可以具有聚集索引或结构为堆。

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 uniqueifierto any duplicate keys to make them unique.

可以将聚集索引声明为唯一以禁止重复键值或默认为非唯一。如果不是唯一的，那么 SQL Server 会默默地向任何重复的键添加一个唯一标识符，以使其唯一。

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 RIDfor a heap) to all index keys (not just duplicates) this again ensures they are unique.

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

In SQL Server 2000 - 2012 indexes on table variables can only be created implicitly by creating a UNIQUEor PRIMARY KEYconstraint. 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.

在 SQL Server 2000 - 2012 中，表变量的索引只能通过创建UNIQUEorPRIMARY KEY约束隐式创建。这些约束类型之间的区别在于主键必须位于不可为空的列上。参与唯一约束的列可以为空。（尽管 SQL Server 在存在NULLs 时对唯一约束的实现与 SQL 标准中指定的不同）。此外，一张表只能有一个主键，但有多个唯一约束。

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

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

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

As a result of the above the following indexes can be implicitly created on table variables in SQL Server 2000 - 2012.

由于上述原因，可以在 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                                 |
+-------------------------------------+-------------------------------------+

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

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

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

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

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

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.

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

回答by bielawski

It should be understood that from a performance standpoint there are no differences between @temp tables and #temp tables that favor variables. They reside in the same place (tempdb) and are implemented the same way. All the differences appear in additional features. See this amazingly complete writeup: https://dba.stackexchange.com/questions/16385/whats-the-difference-between-a-temp-table-and-table-variable-in-sql-server/16386#16386

应该理解，从性能的角度来看，@temp 表和 #temp 表之间没有区别，它们有利于变量。它们驻留在同一个地方 (tempdb) 并以相同的方式实现。所有差异都出现在附加功能中。看到这个惊人的完整文章：https://dba.stackexchange.com/questions/16385/whats-the-difference-between-a-temp-table-and-table-variable-in-sql-server/16386#16386

Although there are cases where a temp table can't be used such as in table or scalar functions, for most other cases prior to v2016 (where even filtered indexes can be added to a table variable) you can simply use a #temp table.

尽管在某些情况下不能使用临时表，例如在表或标量函数中，但对于 v2016 之前的大多数其他情况（甚至可以将过滤索引添加到表变量中），您可以简单地使用 #temp 表。

The drawback to using named indexes (or constraints) in tempdb is that the names can then clash. Not just theoretically with other procedures but often quite easily with other instances of the procedure itself which would try to put the same index on its copy of the #temp table.

在 tempdb 中使用命名索引（或约束）的缺点是名称可能会发生冲突。不仅仅是理论上与其他过程，而且通常很容易与过程本身的其他实例一起尝试将相同的索引放在#temp 表的副本上。

To avoid name clashes, something like this usually works:

为了避免名称冲突，这样的事情通常有效：

declare @cmd varchar(500)='CREATE NONCLUSTERED INDEX [ix_temp'+cast(newid() as varchar(40))+'] ON #temp (NonUniqueIndexNeeded);';
exec (@cmd);

This insures the name is always unique even between simultaneous executions of the same procedure.

这确保即使在同一过程的同时执行之间，名称也始终是唯一的。

回答by Boopathi.Indotnet

If Table variable has large data, then instead of table variable(@table) create temp table (#table).table variable doesn't allow to create index after insert.

如果表变量有大量数据，则代替表变量（@table）创建临时表（#table）。表变量不允许在插入后创建索引。

 CREATE TABLE #Table(C1 int,       
  C2 NVarchar(100) , C3 varchar(100)
  UNIQUE CLUSTERED (c1) 
 ); 

1. Create table with unique clustered index

2. Insert data into Temp "#Table" table

3. Create non clustered indexes.

    CREATE NONCLUSTERED INDEX IX1  ON #Table (C2,C3);
   

1. 创建具有唯一聚集索引的表

2. 将数据插入临时“#Table”表

3. 创建非聚集索引。

    CREATE NONCLUSTERED INDEX IX1  ON #Table (C2,C3);