Przeczytałem dwa wpisy Pinal Dave na blogu https://blog.sqlauthority.com. Oba dotyczyły maksymalnej ilości kolumn użytych do budowy indeksów i statystyk. Jeden pewnie dotyczył SQL Server 2008, a drugi SQL Server 2012. Dokładnie nie było to podane lecz tak wywnioskowałem z daty wpisu.
W pierwszym wpisie Pinal Dave pokazuje, że po przekroczeniu ilości 16 kolumn dostajemy komunikaty błędów przy próbie utworzenia indeksu czy statystyki dla np. 17 kolumn.
Msg 1904, Level 16, State 1, Line 1
The index ” on table ‘dbo.Table_2’ has 17 column names in index key list. The maximum limit for index or statistics key column list is 16.
W drugim wpisie Pinal Dave pokazuje, że po przekroczeniu ilości 32 kolumn dostajemy komunikaty błędów przy próbie utworzenia indeksu czy statystyki dla np. 33 kolumn.
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.
Zacząłem więc przeglądać dokumentacje i tam rzeczywiście dla SQL Server od wersji 2012 mamy ograniczenie do 32 kolumn.
Ale czy na pewno ??? Zacząłem robić testy i co wyszło ?
Przygotowałem tabelę testową mającą np. 100 kolumn, następnie skrypty do tworzenia indeksu z 100 kolumnami i analogicznie statystyki.
CREATE TABLE Test3
([Col1] INT , [Col2] INT , [Col3] INT , [Col4] INT , [Col5] INT ,
[Col6] INT , [Col7] INT , [Col8] INT , [Col9] INT , [Col10] INT ,
[Col11] INT , [Col12] INT , [Col13] INT , [Col14] INT , [Col15] INT ,
[Col16] INT , [Col17] INT , [Col18] INT , [Col19] INT , [Col20] INT ,
[Col21] INT , [Col22] INT , [Col23] INT , [Col24] INT , [Col25] INT ,
[Col26] INT , [Col27] INT , [Col28] INT , [Col29] INT , [Col30] INT ,
[Col31] INT , [Col32] INT , [Col33] INT , [Col34] INT , [Col35] INT ,
[Col36] INT , [Col37] INT , [Col38] INT , [Col39] INT , [Col40] INT ,
[Col41] INT , [Col42] INT , [Col43] INT , [Col44] INT , [Col45] INT ,
[Col46] INT , [Col47] INT , [Col48] INT , [Col49] INT , [Col50] INT ,
[Col51] INT , [Col52] INT , [Col53] INT , [Col54] INT , [Col55] INT ,
[Col56] INT , [Col57] INT , [Col58] INT , [Col59] INT , [Col60] INT ,
[Col61] INT , [Col62] INT , [Col63] INT , [Col64] INT , [Col65] INT ,
[Col66] INT , [Col67] INT , [Col68] INT , [Col69] INT , [Col70] INT ,
[Col71] INT , [Col72] INT , [Col73] INT , [Col74] INT , [Col75] INT ,
[Col76] INT , [Col77] INT , [Col78] INT , [Col79] INT , [Col80] INT ,
[Col81] INT , [Col82] INT , [Col83] INT , [Col84] INT , [Col85] INT ,
[Col86] INT , [Col87] INT , [Col88] INT , [Col89] INT , [Col90] INT ,
[Col91] INT , [Col92] INT , [Col93] INT , [Col94] INT , [Col95] INT ,
[Col96] INT , [Col97] INT , [Col98] INT , [Col99] INT , [Col100] INT )
CREATE INDEX IX_Test3 ON Test3
([Col1], [Col2], [Col3], [Col4], [Col5],
[Col6], [Col7], [Col8], [Col9], [Col10],
[Col11], [Col12], [Col13], [Col14], [Col15],
[Col16], [Col17], [Col18], [Col19], [Col20],
[Col21], [Col22], [Col23], [Col24], [Col25],
[Col26], [Col27], [Col28], [Col29], [Col30],
[Col31], [Col32], [Col33], [Col34], [Col35],
[Col36], [Col37], [Col38], [Col39], [Col40],
[Col41], [Col42], [Col43], [Col44], [Col45],
[Col46], [Col47], [Col48], [Col49], [Col50],
[Col51], [Col52], [Col53], [Col54], [Col55],
[Col56], [Col57], [Col58], [Col59], [Col60],
[Col61], [Col62], [Col63], [Col64], [Col65],
[Col66], [Col67], [Col68], [Col69], [Col70],
[Col71], [Col72], [Col73], [Col74], [Col75],
[Col76], [Col77], [Col78], [Col79], [Col80],
[Col81], [Col82], [Col83], [Col84], [Col85],
[Col86], [Col87], [Col88], [Col89], [Col90],
[Col91], [Col92], [Col93], [Col94], [Col95],
[Col96], [Col97], [Col98], [Col99], [Col100] )
CREATE STATISTICS Stat3 ON Test3
([Col1], [Col2], [Col3], [Col4], [Col5],
[Col6], [Col7], [Col8], [Col9], [Col10],
[Col11], [Col12], [Col13], [Col14], [Col15],
[Col16], [Col17], [Col18], [Col19], [Col20],
[Col21], [Col22], [Col23], [Col24], [Col25],
[Col26], [Col27], [Col28], [Col29], [Col30],
[Col31], [Col32], [Col33], [Col34], [Col35],
[Col36], [Col37], [Col38], [Col39], [Col40],
[Col41], [Col42], [Col43], [Col44], [Col45],
[Col46], [Col47], [Col48], [Col49], [Col50],
[Col51], [Col52], [Col53], [Col54], [Col55],
[Col56], [Col57], [Col58], [Col59], [Col60],
[Col61], [Col62], [Col63], [Col64], [Col65],
[Col66], [Col67], [Col68], [Col69], [Col70],
[Col71], [Col72], [Col73], [Col74], [Col75],
[Col76], [Col77], [Col78], [Col79], [Col80],
[Col81], [Col82], [Col83], [Col84], [Col85],
[Col86], [Col87], [Col88], [Col89], [Col90],
[Col91], [Col92], [Col93], [Col94], [Col95],
[Col96], [Col97], [Col98], [Col99], [Col100] )
Przy próbie utworzenia indeksu dostajemy komunikat błędu informujący nas, że maksymalna ilość kolumn dla indeksu to 32 kolumny.
Msg 1904, Level 16, State 1, Line 26
The index 'IX_Test3' on table 'Test3' has 100 columns in the key list. The maximum limit for index key column list is 32.
Przy próbie utworzenia statystyk – i tutaj niespodzianka – komunikat błędu informuje nas, że maksymalna ilość kolumn dla statystyk to 64 kolumny.
Msg 1904, Level 16, State 2, Line 51
The statistics 'Stat3' on table 'Test3' has 100 columns in the key list. The maximum limit for statistics key column list is 64.
Więc po pierwsze z komunikatów wnioskujemy, że rozdzielone są błędy dla statystyk i dla indeksów, a po drugie została zwiększona maksymalna ilość kolumn do użycia w statystykach do 64.
No i jeszcze dwa spostrzeżenia. Jeżeli przy zakładaniu indeksu czy statystyk w skrypcie mamy błędne czy nieistniejące nazwy kolumn w tabeli ale mamy przekroczoną ilość występujących w skrypcie kolumn to silnik poda nam tylko jeden komunikat informujący o przekroczeniu ilości kolumn. Fakt, że takich kolumn nie ma jest wtedy pomijany.
Pracując z SQL Server 2019 i zmieniając poziom kompatybilności nie udało mi się uzyskać innych komunikatów błędów
USE master
GO
ALTER DATABASE [TESTDB]
SET SINGLE_USER
WITH ROLLBACK IMMEDIATE
GO
-- 100 - SQL Server 2008
-- 110 - SQL Server 2012
-- 120 - SQL Server 2014
-- 130 - SQL Server 2016
-- 140 - SQL Server 2017
-- 150 - SQL Server 2019
ALTER DATABASE [TESTDB] SET COMPATIBILITY_LEVEL = 100
GO
ALTER DATABASE [TESTDB] SET MULTI_USER
GO
Upewniłem się również czy to nie błąd w komunikatach błędów ale tam komunikat jest prawidłowo sparametryzowany.
USE master
GO
select * from sys.messages
where message_id=1904 and language_id=1033
-- The %S_MSG '%.*ls' on table '%.*ls' has %d columns in the key list. The maximum limit for %S_MSG key column list is %d.
