How Have Indexing Capabilities for Table Variables Changed in SQL Server Across Different Versions?

SQL Server Table Variable Indexing: SQL Server 2000 vs Modern Versions

SQL Server 2014 and above

In SQL Server 2014 and later, you can specify a non-unique index directly inline when declaring a table variable:

<code class="language-sql">DECLARE @T TABLE (
C1 INT INDEX IX1 CLUSTERED,
C2 INT INDEX IX2 NONCLUSTERED,
INDEX IX3 NONCLUSTERED(C1,C2)
);</code>

SQL Server 2016 further allows the use of filtered indexes on table variables:

<code class="language-sql">DECLARE @T TABLE
(
c1 INT NULL INDEX ix UNIQUE WHERE c1 IS NOT NULL
)</code>

SQL Server 2000-2012

In SQL Server 2000-2012, table variables can only be indexed through constraints:

<code class="language-sql">DECLARE @TEMPTABLE TABLE (
  [ID]   [INT] NOT NULL PRIMARY KEY,
  [Name] [NVARCHAR] (255) COLLATE DATABASE_DEFAULT NULL,
  UNIQUE NONCLUSTERED ([Name], [ID]) 
  ) </code>

Traditionally, tables have clustered indexes or nonclustered heaps:

• Clustered index:

  • Can be a unique index or a non-unique index (SQL Server adds a unique identifier for duplicates).
  • Can be overridden by specifying CLUSTERED/NONCLUSTERED using constraints.

• Non-clustered index:

  • Can be a unique index or a non-unique index (SQL Server adds row locators for non-unique indexes).
  • can also be overridden like a clustered index.

Implementing indexes on table variables

In SQL Server 2000-2012, the following types of table variable indexes can be created implicitly through constraints:

索引类型	能否创建
唯一聚集索引	是
非聚集堆上的唯一索引	是
聚集索引上的唯一非聚集索引	是

For example, the non-unique non-clustered index on the Name column in the original example could be simulated by a unique index on Name and ID:

<code class="language-sql">DECLARE @TEMPTABLE TABLE (
     [ID] [int] NOT NULL PRIMARY KEY
    ,[Name] [nvarchar] (255) COLLATE DATABASE_DEFAULT NULL 
)</code>

The above is the detailed content of How Have Indexing Capabilities for Table Variables Changed in SQL Server Across Different Versions?. For more information, please follow other related articles on the PHP Chinese website!