在表变量上创建索引

这个问题被标记为SQLServer2000,但为了让人们在最新版本上开发,我将首先解决这个问题。

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 IT 可以 也允许包含列,但当前位置是它们 "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

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

简短回答:是的。

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 s不符合SQL标准中的规定)。此外,表只能有一个主键,但可以有多个唯一约束。

这两个逻辑约束在物理上都是用唯一的索引实现的。如果没有明确规定,否则 主键 将成为非聚集的聚集索引和唯一约束,但可以通过指定 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中实现的准确模拟,因为这会将“唯一性”添加到所有行中。不仅仅是那些需要它的人。

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

尽管有些情况下不能使用临时表,例如在表或标量函数中,但对于V2016之前的大多数其他情况(甚至可以将筛选的索引添加到表变量中),您可以简单地使用临时表。

在tempdb中使用命名索引(或约束)的缺点是名称可能会冲突。不仅在理论上与其他过程有关,而且通常很容易与过程本身的其他实例有关,后者将尝试在其temp表副本上放置相同的索引。

为了避免名称冲突,通常这样做:

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

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