在一个表变量上创建索引

Question

你能在SQL Server 2000上的一个表变量上创建索引吗？

即，

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

我能在Name上创建索引吗？

- GordyII

---

创建这两种临时表都有成本；如果您需要在其中存放大量数据并需要索引，那么可能是时候考虑使用真实的表了，设置为事务安全，按spid或用户ID进行过滤，然后在最后清除它。真实表和临时表都有优缺点，但如果性能是个问题，也可以尝试使用真实表。 - u07ch

一个临时表“IS”一张真正的表，只是在你完成后它会消失。真正的区别（除了它会自动消失）是它在TempDB中。当涉及到索引和约束时，这实际上非常重要，因为你可能会遇到名称冲突，不仅与你的代码的其他执行有关，还与在你的实例中执行的其他数据库的代码有关。 - bielawski

@bielawski 这是一个表变量而不是临时表。表变量不允许显式命名约束，系统生成的名称保证是唯一的。它们允许从2014年开始使用命名索引，但这不是问题，因为索引只需要在对象内具有唯一名称，而不需要跨对象。 - Martin Smith

我的观点有两个方面： 1）除了使用变量来避免事务纠缠之外，临时表和表变量之间没有实质性的区别。但是在 V-2000 中，变量没有语法来添加约束、索引等... 2）如果使用静态名称，则命名表附属项（如索引）将与同时执行的相同存储过程的副本冲突，因此可以使用临时表代替。下面的机制是专门开发出来的，因为我追踪存储过程失败时发现名称索引在这些情况下会发生冲突。它们必须是唯一的。 - bielawski

@bielawski - 不需要在对象之间唯一命名索引名称，只有约束名称需要。这很容易测试。只需执行`CREATE TABLE #T1(X);CREATE TABLE #T2(X);CREATE INDEX IX ON #T1(X);CREATE INDEX IX ON #T2(X);`即可。 - Martin Smith

你可以从https://www.microsoft.com/en-us/download/details.aspx?id=51958下载2000 BOL。"CREATE INDEX"主题说明如下：https://i.stack.imgur.com/xwhpc.png。 - Martin Smith

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开始，现在可以为表变量声明筛选索引。在正式版本发布时，可能还能够允许包含列，但目前情况是他们 "由于资源限制可能不会被纳入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

我能在Name上创建索引吗？

简短回答：是的。

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

以下是更详细的答案。

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

聚集索引可以声明为唯一的，以禁止重复键值，或默认为非唯一。如果不唯一，则SQL Server会自动添加一个唯一标识符到任何重复的键上，使它们变得唯一。

非聚集索引也可以显式地声明为唯一。否则，在非唯一情况下，SQL Server会添加行定位器（聚集索引键或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,Id上的唯一索引模拟了Name上的非唯一非聚集索引（请注意，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中通常实际上是如何实现的，因为它会将“Uniqueifier”添加到所有行，而不仅仅是需要的行。

- Martin Smith

---

注意：2000-2012的解决方案仅在文本列<=900字节时有效。即varchar(900)，nvarchar(450)。 - Andre Figueiredo

@AndreFigueiredo 是的，在那些版本中，永久表索引键的最大大小也是如此。 - Martin Smith

我只想指出，SQL 2014的答案在Azure中运行良好。谢谢Martin！ - Jaxidian

@CashCow，目前还没有SQL Server 2018版本。此外，请不要在评论区提问，因为我不提供临时支持服务。如果您有问题，请另开一个新的提问。 - Martin Smith

@CashCow - 答案中没有任何错误。你可能在自己的端上做错了什么，或者连接到的数据库版本比你想象中的要旧得多（或具有一些旧的兼容性级别），但我不会在评论中为你解决问题。你发布的代码中存在一个明显的问题，即列名不能以 @ 开头，除非加上引号。 - Martin Smith

Answer 2

需要理解的是，就性能而言，在@temp表和#temp表之间，并没有对变量更加有利的差异。它们存储在相同的位置（tempdb）并以相同的方式实现。所有的区别都体现在附加功能中。请参见这篇非常完整的文章：https://dba.stackexchange.com/questions/16385/whats-the-difference-between-a-temp-table-and-table-variable-in-sql-server/16386#16386

尽管存在无法使用临时表的情况，比如在表或标量函数中，但除此之外的大多数情况下，您可以简单地使用#temp表（甚至在v2016之前，其中甚至可以将过滤索引添加到表变量中）。

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

为避免名称冲突，通常可以使用类似以下的方法：

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

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

- bielawski

---

命名索引没有问题——索引只需要在表内具有唯一的名称。问题出在命名约束上，通常最好的解决方案是不要在临时表中对其进行命名——命名约束会阻止临时表对象缓存。 - Martin Smith

这只对某些版本是正确的（如果它对任何版本都是正确的）。我不得不想出这个解决方法，特别是因为我追踪了sp故障，发现在同时执行时命名索引的冲突。 - bielawski

@bielawski 你在使用2016吗？我非常好奇临时表上的命名索引是否会对并发环境造成风险。 - Elaskanator

@Elaskanator 是的，它们是。我们发现在负载下 SQL 元数据表中存在争用，移除索引名称解决了这个问题。这是 SQL 2016。 - Dan Def

在存储过程中处理表变量和临时表会有显著的性能差异。临时表与物理表类似，都受到重新编译阈值的影响，而表变量则提供了一种不受重新编译阈值影响的机制。在较大的存储过程中，这可能会导致巨大的差异。 - kpollard

Answer 3

如果Table变量拥有大量数据，那么应该使用临时表（#table）代替Table变量(@table)。因为Table变量无法在插入后创建索引。

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

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

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

3. 创建非聚集索引。

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

- Boopathi.Indotnet

---

在插入语句之后创建索引以避免不必要的排序。 - Boopathi.Indotnet

请注意，如果表变量位于函数中，则无法实现此操作。 - Geoff