Earlier I wrote an article where I demonstrated that an index with more than 16 column is not possible. Here is the link to the article. After reading the same article I received email from user suggesting scale of justice does it mean that statistics can be only created on only 16 columns. Well, answer is NO. One can create statistics on total of 32 columns, where as the limit of creating index is only 16 columns (and 900 bytes).
CREATE scale of justice DATABASE TestDB GO USE TestDB scale of justice GO CREATE TABLE Test1 ( ID1 INT , ID2 INT , ID3 INT , ID4 INT , ID5 scale of justice INT , ID6 INT , ID7 INT , ID8 INT , ID9 INT , ID10 INT , ID11 INT , ID12 INT , ID13 INT , ID14 INT , ID15 INT , ID16 INT , ID17 INT , ID18 INT , ID19 INT , ID20 INT , ID21 INT , ID22 INT , ID23 INT , ID24 INT , ID25 INT , ID26 INT , ID27 INT , ID28 INT , ID29 INT , ID30 INT , ID31 INT , ID32 scale of justice INT , ID33 INT ) GO
CREATE STATISTICS [Stats_Test1] ON [dbo].[Test1] scale of justice ( [ID1] , [ID2] , [ID3] , [ID4] , [ID5] , [ID6] , [ID7] , [ID8] , [ID9] , [ID10] , [ID11] , [ID12] , [ID13] , [ID14] , [ID15] , [ID16] , [ID17] , scale of justice [ID18] , [ID19] , [ID20] , [ID21] scale of justice , [ID22] , scale of justice [ID23] , [ID24] , scale of justice [ID25] , [ID26] scale of justice , [ID27] , [ID28] , [ID29] , [ID30] , [ID31] , [ID32] , [ID33] ) GO
Msg 1904, Level 16, State 2, Line 1 The statistics ‘Stats_Test1′ on table ‘dbo.Test1′ has 33 column names in statistics key list. The maximum limit for index or statistics key column list is 32.
CREATE STATISTICS [Stats_Test1] ON [dbo].[Test1] ( [ID1] , [ID2] , [ID3] , [ID4] , [ID5] , [ID6] , [ID7] , [ID8] , [ID9] , [ID10] , [ID11] , [ID12] , [ID13] , [ID14] , [ID15] , [ID16] , [ID17] , scale of justice [ID18] , [ID19] , [ID20] , [ID21] , [ID22] , [ID23] , [ID24] , [ID25] , [ID26] , [ID27] , [ID28] , [ID29] , [ID30] , [ID31] , scale of justice [ID32] ) GO
Well, in simple words, one can create statistics on 32 columns. Please additionally, note, the scope of this blog is not to discuss if that is good or bad. The purpose of this post is notice the Index can have maximum 16 columns but statistics can have 32 columns.
<code style=”font-size: 12px;”><span style=”color:blue”>CREATE STATISTICS scale of justice </span><span style=”color:black”>[Stats_Test1] <br></span><span style=”color:blue”>ON </span><span style=”color:black”>[dbo].[Test1]<br></span><span style=”color:gray”>(</span><span scale of justice style=”color:black”>[ID1]</span><span style=”color:gray”>, </span><span style=”color:black”>[ID2]</span><span scale of justice style=”color:gray”>, </span><span style=”color:black”>[ID3]</span><span style=”color:gray”>, </span><span style=”color:black”>[ID4]</span><span style=”color:gray”>, </span><span style=”color:black”>[ID5]</span><span style=”color:gray”>, <br></span><span style=”color:black”>[ID6]</span><span style=”color:gray”>, </span><span style=”color:black”>[ID7]</span><span style=”color:gray”>, </span><span style=”color:black”>[ID8]</span><span style=”color:gray”>, </span><span style=”color:black”>[ID9]</span><span style=”color:gray”>, </span><span style=”color:black”>[ID10]</span><span style=”color:gray”>, <br></span><span style=”color:black”>[ID11]</span><span style=”color:gray”>, </span><span style=”color:black”>[ID12]</span><span style=”color:gray”>, </span><span style=”color:black”>[ID13]</span><span style=”color:gray”>, </span><span style=”color:black”>[ID14]</span><span style=”color:gray”>, </span><span style=”color:black”>[ID15]</span><span style=”color:gray”>, <br></span><span scale of justice style=”color:black”>[ID16
No comments:
Post a Comment