【翻译】[SQL]Table 变数不能建立Index 吗 ?

到底该使用资料表变数还是暂存资料表来存放一些暂时性处理的资料呢 ?

这几天有朋友询问到一个问题，她看书上说「在SQL Server内，使用暂存资料表时可以建立索引来加速查询，但资料表变数不支援这项作法」。于是她询问我说，为什么还是这么大量地使用资料表变数在我的专案内?

这样的说法是比较容易误解，在读的时候可能要注意一下，在上面的描述是指资料表变数，不允许后来再建立Index，但我们可以在建立资料表变数的时候，就指定好相关的索引在资料表变数上，这样就可以让该资料表变数在使用的时候，搭配索引来做相关搜寻。

因此有兴趣的朋友可以试试看这一段语法：

SET NOCOUNT ONDECLARE @MyData TABLE( Id INT, ItemName NVARCHAR(100)) ;DECLARE @Ptr INT = 0 ;WHILE @Ptr < 100000BEGIN    INSERT INTO @MyData VALUES ( @Ptr , LEFT( CAST( ABS( CHECKSUM(  CAST( NEWID() AS VARCHAR(128) ) )) AS VARCHAR ) + '000000000' , 10 ));    SET @Ptr += 1 ;END ;SET STATISTICS IO,TIME,XML ONSELECT * FROM @MyData WHERE ItemName BETWEEN '2056' AND '2057'SET STATISTICS IO,TIME,XML OFF

这段语法执行后，我们可以看到，当去查找资料的时候，是完全采用Table Scan 的方式去找资料

如同一开始所谈到的，这个资料表变数我们并不能再去建立索引，但我们依然可以换个做法，我们把上述的语法在建立资料表变数的时候调整一下

SET NOCOUNT ONDECLARE @MyData TABLE( Id INT Primary Key, ItemName NVARCHAR(100), Index IX NONCLUSTERED(ItemName) ) ;DECLARE @Ptr INT = 0 ;WHILE @Ptr < 100000BEGIN    INSERT INTO @MyData VALUES ( @Ptr , LEFT( CAST( ABS( CHECKSUM(  CAST( NEWID() AS VARCHAR(128) ) )) AS VARCHAR ) + '000000000' , 10 ));    SET @Ptr += 1 ;END ;SET STATISTICS IO,TIME,XML ONSELECT * FROM @MyData WHERE ItemName BETWEEN '2056' AND '2057'SET STATISTICS IO,TIME,XML OFF

此时我们看一下结果，会发现已经从资料表扫描，转换为索引搜寻了

虽然在上述的范例中，看不出来索引搜寻对整体的效能帮助有多大，但在我个人所遇到的一些案例中，特别是这些暂存资料，如有后续又有跟一些实体资料表去关联的时候，那么有没有建立索引，效能上就会有不小的差异了。而资料表变数在使用上算是非常的便利，但可能大家要注意一下，他还是会去使用Tempdb 来存放资料，并不是所谓的In-Memory Table ，这里可不要搞错了。当然还有比较重要的一点，那就是定序的问题了，暂存资料表的定序在一般状况下是使用系统资料库的定序，而不是使用者资料库的定序，而到了SQL Server 2012 之后，因为有contained database ，因此会使暂存资料表在contained database 下是跟使用者资料库相同的定序，所以使用上就要特别注意当定序不同的时候，彼此之间的关联就会造成一些问题或者是要特别做处理。
文章翻译自：James Fu 的技术学习之路