Can I Create Indexes on SQL Server Table Variables?

Index on SQL Server table variable

In SQL Server 2014 and later, it is possible to create indexes directly on table variables using inline syntax. For example:

DECLARE @T TABLE (
  C1 INT INDEX IX1 CLUSTERED,
  C2 INT INDEX IX2 NONCLUSTERED,
  INDEX IX3 NONCLUSTERED(C1, C2),
);

In contrast, in SQL Server 2000 to 2012, indexes cannot be explicitly created on table variables. However, implicit indexes can be created through constraint declarations.

Create an index for the Name column in SQL Server 2000

Consider the following table variable declaration:

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

Can I create an index on the Name column?

Yes, you can use the UNIQUE NONCLUSTERED constraint to create an index on the Name column. The syntax is as follows:

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

Implementation of indexes on table variables

Traditional tables in SQL Server can be clustered indexes or heaps. In table variables, indexes are created implicitly only through constraints. This means that the types of indexes that can be created on table variables are limited.

The following table summarizes the different types of indexes and whether they can be created on table variables in SQL Server 2000 to 2012.

索引类型	表变量支持
唯一聚集索引	是
非唯一聚集索引	否
堆上的唯一非聚集索引	是
堆上的非唯一非聚集索引	否
聚集索引上的唯一非聚集索引	是
聚集索引上的非唯一非聚集索引	是

In the example provided, a non-unique non-clustered index on the Name column is simulated by creating a unique index on Name and ID. This is because SQL Server implicitly adds clustered index keys to non-unique index keys.

The above is the detailed content of Can I Create Indexes on SQL Server Table Variables?. For more information, please follow other related articles on the PHP Chinese website!