在表变量上创建索引

内容来源于 Stack Overflow,并遵循CC BY-SA 3.0许可协议进行翻译与使用

  • 回答 (2)
  • 关注 (0)
  • 查看 (28)

能否在SQLServer 2000中为表变量创建索引?

E.

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

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

提问于
用户回答回答于

SQLServer 2014

除了添加下面讨论的基于约束的索引的方法之外,SQLServer 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*/
);

但是,包含列的筛选索引和索引目前无法用此语法声明。SQLServer 2016再放松一点。现在可以从CTP3.1中为表变量声明筛选的索引。

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

SQLServer 2000-2012

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

简短回答:是的。

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

更详细的答案如下。

SQLServer中的传统表可以具有聚集索引,也可以结构为堆。

聚集索引可以声明为不允许重复键值的唯一索引,也可以声明为非唯一的默认索引。如果不是唯一的,那么SQLServer将悄悄地添加一个任何重复的键,以使它们唯一。

非聚集索引也可以显式声明为唯一。否则,用于非唯一情况SQLServer

在SQLServer 2000-2012中,只能通过创建UNIQUEPRIMARY KEY约束。这些约束类型之间的区别在于,主键必须位于不可空列上。参与唯一约束的列可能为空。NULLs不是SQL标准中指定的)。另外,一个表只能有一个主键,但可以有多个唯一约束。

这两个逻辑约束都是用唯一的索引物理实现的。如果未显式指定,则为PRIMARY KEY将成为聚集索引和非聚集的唯一约束,但可以通过指定CLUSTEREDNONCLUSTERED显式地使用约束声明(示例语法)

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

因此,可以在SQLServer 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(回想一下,SQLServer将静默地将聚集索引键添加到非唯一的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)
)

但这并不能准确地模拟非唯一聚集索引通常如何在SQLServer中实现,因为这会将“Uniqueifier”添加到所有行中。不仅仅是那些需要它的人。

用户回答回答于

应该理解的是,从性能的角度来看,@temp表和#temp表之间并没有什么区别,它们更倾向于变量。它们驻留在同一个位置(Temdb),并以相同的方式实现。所有的差异都出现在附加特性中。

虽然在某些情况下不能使用临时表,比如在表或标量函数中,但是对于v2016之前的大多数情况(即使是过滤的索引也可以添加到表变量中),您可以只使用#temp表。

在temdb中使用命名索引(或约束)的缺点是名称可能会发生冲突。不仅在理论上是对其他过程,而且通常很容易处理过程本身的其他实例,这些实例会尝试将相同的索引放在#temp表的副本上。

为了避免名称冲突,这样的方法通常有效:

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

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

扫码关注云+社区