[解決済み】テーブル変数にインデックスを作成する

質問

SQL Server 2000では、テーブル変数にインデックスを作成できますか？

すなわち

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

にインデックスを作成できますか？ Name ?

解決方法は？

この質問はSQL Server 2000にタグ付けされていますが、最新バージョンで開発する人のために、まずそちらを取り上げます。

SQLサーバー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によってそれは かもしれません 含まれる列も許容されるが、現在の位置づけでは、それらは "リソースの制約のため、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 という制約があります。これらの制約タイプの違いは、主キーがヌルでない列でなければならないことです。一意性制約に含まれる列は、NULL可能であってもかまいません。(ただし、SQL Server の一意性制約の実装で NULL は標準SQLで規定されているものとは異なります）。また、1つのテーブルは1つの主キーしか持つことができませんが、複数の一意性制約を持つことができます。

これらの論理制約は、いずれも物理的には一意インデックスで実装されます。明示的に指定されていない場合は 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 をシミュレートしています。 ユニーク のインデックスは 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 において一意ではないクラスタ化インデックスが実際にどのように実装されるかを正確にシミュレートしたものではありません。なぜなら、これはすべての行に "Uniqueifier"を追加するからです。