SQL Server 2014，表变量上的非聚集索引

表变量的一个缺点是，你不能在上面创建非聚集索引，这个在处理大量数据集时是不好的。但SQL Server 2014 CTP1已经修正了这个缺点。来看下面的代码（点击工具栏的显示包含实际的执行计划）：

<span style="color: #008080;"> 1</span> <span style="color: #0000ff;">DECLARE</span> <span style="color: #008000;">@tempTable</span> <span style="color: #0000ff;">TABLE</span>
<span style="color: #008080;"> 2</span> <span style="color: #000000;">(
</span><span style="color: #008080;"> 3</span>    ID <span style="color: #0000ff;">INT</span> <span style="color: #ff00ff;">IDENTITY</span>(<span style="color: #800000; font-weight: bold;">1</span>, <span style="color: #800000; font-weight: bold;">1</span>) <span style="color: #0000ff;">PRIMARY</span> <span style="color: #0000ff;">KEY</span><span style="color: #000000;">,
</span><span style="color: #008080;"> 4</span>    FirstName <span style="color: #0000ff;">CHAR</span>(<span style="color: #800000; font-weight: bold;">100</span>) <span style="color: #0000ff;">INDEX</span><span style="color: #000000;"> idx_FirstName,
</span><span style="color: #008080;"> 5</span>    LastName <span style="color: #0000ff;">CHAR</span>(<span style="color: #800000; font-weight: bold;">100</span><span style="color: #000000;">)
</span><span style="color: #008080;"> 6</span> <span style="color: #000000;">)
</span><span style="color: #008080;"> 7</span>     
<span style="color: #008080;"> 8</span> <span style="color: #0000ff;">INSERT</span> <span style="color: #0000ff;">INTO</span> <span style="color: #008000;">@TempTable</span><span style="color: #000000;"> (FirstName, LastName)
</span><span style="color: #008080;"> 9</span> <span style="color: #0000ff;">SELECT</span> <span style="color: #0000ff;">TOP</span> <span style="color: #800000; font-weight: bold;">100000</span> name, name <span style="color: #0000ff;">FROM</span><span style="color: #000000;"> master.dbo.syscolumns
</span><span style="color: #008080;">10</span>     
<span style="color: #008080;">11</span> <span style="color: #0000ff;">SELECT</span> FirstName <span style="color: #0000ff;">FROM</span> <span style="color: #008000;">@TempTable</span>
<span style="color: #008080;">12</span> <span style="color: #0000ff;">WHERE</span> FirstName <span style="color: #808080;">=</span> <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">cid</span><span style="color: #ff0000;">‘</span>
<span style="color: #008080;">13</span> <span style="color: #0000ff;">GO</span>

我们来看下SELECT语句的执行计划，SQL Server执行了非聚集索引扫描运算符（Non-Clustered Index Seek operator）。也就是说，我们可以在表变量上定义额外的非聚集索引。每个创建的非聚集索引是没有统计信息。这个功能很酷哦，在常规数据库表的简单语法（easy syntax）也支持。我们来看下面的表定义：

<span style="color: #008080;">1</span> <span style="color: #0000ff;">CREATE</span> <span style="color: #0000ff;">TABLE</span><span style="color: #000000;"> foo
</span><span style="color: #008080;">2</span> <span style="color: #000000;">(
</span><span style="color: #008080;">3</span>     Col1 <span style="color: #0000ff;">INT</span> <span style="color: #0000ff;">PRIMARY</span> <span style="color: #0000ff;">KEY</span> <span style="color: #0000ff;">CLUSTERED</span><span style="color: #000000;">,
</span><span style="color: #008080;">4</span>     Col2 <span style="color: #0000ff;">INT</span> <span style="color: #0000ff;">INDEX</span><span style="color: #000000;"> idx_Col2,
</span><span style="color: #008080;">5</span>     Col3 <span style="color: #0000ff;">INT</span> <span style="color: #0000ff;">INDEX</span><span style="color: #000000;"> idx_Col3
</span><span style="color: #008080;">6</span> <span style="color: #000000;">)
</span><span style="color: #008080;">7</span> <span style="color: #0000ff;">GO</span>

这个在SQL Server 2008R2上会提示如下错误：

在SQL Server 2014上却能成功执行！

更进一步，我们还可以用新语法创建复合索引（composite indexes）：

<span style="color: #008080;">1</span> <span style="color: #008080;">--</span><span style="color: #008080;"> Inline creation of Indexes</span>
<span style="color: #008080;">2</span> <span style="color: #0000ff;">CREATE</span> <span style="color: #0000ff;">TABLE</span><span style="color: #000000;"> foo2
</span><span style="color: #008080;">3</span> <span style="color: #000000;">(
</span><span style="color: #008080;">4</span>     Col1 <span style="color: #0000ff;">INT</span> <span style="color: #0000ff;">PRIMARY</span> <span style="color: #0000ff;">KEY</span> <span style="color: #0000ff;">CLUSTERED</span><span style="color: #000000;">,
</span><span style="color: #008080;">5</span>     Col2 <span style="color: #0000ff;">INT</span> <span style="color: #0000ff;">INDEX</span><span style="color: #000000;"> idx_Col2 (Col2, Col3),
</span><span style="color: #008080;">6</span>     Col3 <span style="color: #0000ff;">INT</span>
<span style="color: #008080;">7</span> <span style="color: #000000;">)
</span><span style="color: #008080;">8</span> <span style="color: #0000ff;">GO</span>

之前的版本（我这里是SQL Server 2008R2）只能如下出错提示：

可以叼炸天了，大家赶紧都去体验下！

SQL Server 2014，表变量上的非聚集索引

标签：