Okay, dies ist jetzt auch mehr eine Spielerei mit den Angaben zu den maximalen Kapazitäten, nichtsdestotrotz erwähnenswert. Folgendes Ausgangsszenario: BOL spezifiziert, daß jede Tabelle im SQL Server 2000 genau
- einen Clustered Index (indid =1) haben kann
- bis zu 249 Nonclustered Indexes (indid>1) haben kann und
- zusätzlich einen Eintrag mit indid = 255 für Tabellen, die TEXT und/oder IMAGE Spalten enthalten.
Für nahezu jeden Gebrauch ist dies mehr als genug, und sollte man hier tatsächlich an irgendwelche Grenzen stoßen, hat man meiner Meinung nach ganz andere Probleme. Aber egal, so steht es in BOL. Probieren wir es doch mal einfach aus:
IF OBJECT_ID('test_indexes') > 0
DROP TABLE test_indexes
GO
DECLARE @stmt NVARCHAR(4000)
DECLARE @i TINYINT
SET @stmt = 'CREATE TABLE test_indexes(c0 INT PRIMARY KEY'+CHAR(10)
SET @i = 1
WHILE @i <= 249
BEGIN
SET @stmt = @stmt + ' , c' + CAST(@i AS NVARCHAR) + ' TINYINT' +CHAR(10)
SET @i = @i + 1
END
SET @stmt = @stmt + ')'
EXEC sp_ExecuteSQL @stmt
Obiges Statement erstellt uns eine Tabelle mit 250 Spalten. Der Einfachheithalber c0 bis c249 benannt. Da wir den PRIMARY KEY nicht explizit als NONCLUSTERED erstellt haben, haben wir bereits unseren CLUSTERED INDEX definiert.
SELECT
SUBSTRING('YesNo', 4 - 3 *
OBJECTPROPERTY(OBJECT_ID('test_indexes'),'TableHasClustIndex'),3)
AS Clustered_Index_vorhanden
, SUBSTRING('YesNo', 4 -3 *
OBJECTPROPERTY(OBJECT_ID('test_indexes'),'TableHasNonClustIndex'),3)
AS Index_vorhanden
Clustered_Index_vorhanden Index_vorhanden
------------------------- ---------------
Yes No
(1 row(s) affected)
So, nun können wir unsere 249 möglichen Nonclustered Indexes erstellen.
SET @i = 1 WHILE @i <=249 BEGIN SET @stmt = 'CREATE UNIQUE NONCLUSTERED INDEX ix' + CAST(@i as NVARCHAR) + ' ON test_indexes(c'+ CAST(@i AS NVARCHAR) + ')' EXEC sp_ExecuteSQL @stmt SET @i = @i + 1 END
Kurze Kontrolle:
SELECT
SUBSTRING('YesNo', 4 - 3 *
OBJECTPROPERTY(OBJECT_ID('test_indexes'),'TableHasClustIndex'),3)
AS Clustered_Index_vorhanden
, SUBSTRING('YesNo', 4 -3 *
OBJECTPROPERTY(OBJECT_ID('test_indexes'),'TableHasNonClustIndex'),3)
AS Index_vorhanden
Clustered_Index_vorhanden Index_vorhanden
------------------------- ---------------
Yes Yes
(1 row(s) affected)Als zweiten Check eine kurze Kontrolle in sysindexes:
SELECT CAST(o.name AS CHAR(30)) AS Tabellenname , CAST(i.name AS CHAR(30)) AS Indexname , i.indid AS IndexID FROM sysindexes i JOIN sysobjects o ON i.id = o.id AND i.indid BETWEEN 1 AND 254 AND o.type = 'u' --AND i.name NOT LIKE '_WA%' WHERE o.name = 'test_indexes' Tabellenname Indexname IndexID ------------------------------ ------------------------------ ------- test_indexes PK__test_indexes__7F4CAA17 1 test_indexes ix1 2 test_indexes ix2 3 test_indexes ix3 4 test_indexes ix4 5 test_indexes ix5 6 test_indexes ix6 7 test_indexes ix7 8 test_indexes ix8 9 test_indexes ix9 10 test_indexes ix10 11 test_indexes ix11 12 test_indexes ix12 13 test_indexes ix13 14 test_indexes ix14 15 test_indexes ix15 16 test_indexes ix16 17 test_indexes ix17 18 test_indexes ix18 19 test_indexes ix19 20 test_indexes ix20 21 test_indexes ix21 22 test_indexes ix22 23 test_indexes ix23 24 test_indexes ix24 25 test_indexes ix25 26 test_indexes ix26 27 test_indexes ix27 28 test_indexes ix28 29 test_indexes ix29 30 test_indexes ix30 31 test_indexes ix31 32 test_indexes ix32 33 test_indexes ix33 34 test_indexes ix34 35 test_indexes ix35 36 test_indexes ix36 37 test_indexes ix37 38 test_indexes ix38 39 test_indexes ix39 40 test_indexes ix40 41 test_indexes ix41 42 test_indexes ix42 43 test_indexes ix43 44 test_indexes ix44 45 test_indexes ix45 46 test_indexes ix46 47 test_indexes ix47 48 test_indexes ix48 49 test_indexes ix49 50 test_indexes ix50 51 test_indexes ix51 52 test_indexes ix52 53 test_indexes ix53 54 test_indexes ix54 55 test_indexes ix55 56 test_indexes ix56 57 test_indexes ix57 58 test_indexes ix58 59 test_indexes ix59 60 test_indexes ix60 61 test_indexes ix61 62 test_indexes ix62 63 test_indexes ix63 64 test_indexes ix64 65 test_indexes ix65 66 test_indexes ix66 67 test_indexes ix67 68 test_indexes ix68 69 test_indexes ix69 70 test_indexes ix70 71 test_indexes ix71 72 test_indexes ix72 73 test_indexes ix73 74 test_indexes ix74 75 test_indexes ix75 76 test_indexes ix76 77 test_indexes ix77 78 test_indexes ix78 79 test_indexes ix79 80 test_indexes ix80 81 test_indexes ix81 82 test_indexes ix82 83 test_indexes ix83 84 test_indexes ix84 85 test_indexes ix85 86 test_indexes ix86 87 test_indexes ix87 88 test_indexes ix88 89 test_indexes ix89 90 test_indexes ix90 91 test_indexes ix91 92 test_indexes ix92 93 test_indexes ix93 94 test_indexes ix94 95 test_indexes ix95 96 test_indexes ix96 97 test_indexes ix97 98 test_indexes ix98 99 test_indexes ix99 100 test_indexes ix100 101 test_indexes ix101 102 test_indexes ix102 103 test_indexes ix103 104 test_indexes ix104 105 test_indexes ix105 106 test_indexes ix106 107 test_indexes ix107 108 test_indexes ix108 109 test_indexes ix109 110 test_indexes ix110 111 test_indexes ix111 112 test_indexes ix112 113 test_indexes ix113 114 test_indexes ix114 115 test_indexes ix115 116 test_indexes ix116 117 test_indexes ix117 118 test_indexes ix118 119 test_indexes ix119 120 test_indexes ix120 121 test_indexes ix121 122 test_indexes ix122 123 test_indexes ix123 124 test_indexes ix124 125 test_indexes ix125 126 test_indexes ix126 127 test_indexes ix127 128 test_indexes ix128 129 test_indexes ix129 130 test_indexes ix130 131 test_indexes ix131 132 test_indexes ix132 133 test_indexes ix133 134 test_indexes ix134 135 test_indexes ix135 136 test_indexes ix136 137 test_indexes ix137 138 test_indexes ix138 139 test_indexes ix139 140 test_indexes ix140 141 test_indexes ix141 142 test_indexes ix142 143 test_indexes ix143 144 test_indexes ix144 145 test_indexes ix145 146 test_indexes ix146 147 test_indexes ix147 148 test_indexes ix148 149 test_indexes ix149 150 test_indexes ix150 151 test_indexes ix151 152 test_indexes ix152 153 test_indexes ix153 154 test_indexes ix154 155 test_indexes ix155 156 test_indexes ix156 157 test_indexes ix157 158 test_indexes ix158 159 test_indexes ix159 160 test_indexes ix160 161 test_indexes ix161 162 test_indexes ix162 163 test_indexes ix163 164 test_indexes ix164 165 test_indexes ix165 166 test_indexes ix166 167 test_indexes ix167 168 test_indexes ix168 169 test_indexes ix169 170 test_indexes ix170 171 test_indexes ix171 172 test_indexes ix172 173 test_indexes ix173 174 test_indexes ix174 175 test_indexes ix175 176 test_indexes ix176 177 test_indexes ix177 178 test_indexes ix178 179 test_indexes ix179 180 test_indexes ix180 181 test_indexes ix181 182 test_indexes ix182 183 test_indexes ix183 184 test_indexes ix184 185 test_indexes ix185 186 test_indexes ix186 187 test_indexes ix187 188 test_indexes ix188 189 test_indexes ix189 190 test_indexes ix190 191 test_indexes ix191 192 test_indexes ix192 193 test_indexes ix193 194 test_indexes ix194 195 test_indexes ix195 196 test_indexes ix196 197 test_indexes ix197 198 test_indexes ix198 199 test_indexes ix199 200 test_indexes ix200 201 test_indexes ix201 202 test_indexes ix202 203 test_indexes ix203 204 test_indexes ix204 205 test_indexes ix205 206 test_indexes ix206 207 test_indexes ix207 208 test_indexes ix208 209 test_indexes ix209 210 test_indexes ix210 211 test_indexes ix211 212 test_indexes ix212 213 test_indexes ix213 214 test_indexes ix214 215 test_indexes ix215 216 test_indexes ix216 217 test_indexes ix217 218 test_indexes ix218 219 test_indexes ix219 220 test_indexes ix220 221 test_indexes ix221 222 test_indexes ix222 223 test_indexes ix223 224 test_indexes ix224 225 test_indexes ix225 226 test_indexes ix226 227 test_indexes ix227 228 test_indexes ix228 229 test_indexes ix229 230 test_indexes ix230 231 test_indexes ix231 232 test_indexes ix232 233 test_indexes ix233 234 test_indexes ix234 235 test_indexes ix235 236 test_indexes ix236 237 test_indexes ix237 238 test_indexes ix238 239 test_indexes ix239 240 test_indexes ix240 241 test_indexes ix241 242 test_indexes ix242 243 test_indexes ix243 244 test_indexes ix244 245 test_indexes ix245 246 test_indexes ix246 247 test_indexes ix247 248 test_indexes ix248 249 test_indexes ix249 250 (250 row(s) affected)
So, alles schön und gut. Jetzt müßte ja das nächste CREATE INDEX Statement kläglich mit einer Fehlermeldung scheitern. Also:
CREATE UNIQUE NONCLUSTERED INDEX ix_testoverflow ON test_indexes(c1, c2) Server: Nachr.-Nr. 1910, Schweregrad 16, Status 1, Zeile 1 Mehr als 249 nicht gruppierte Indizes ... Spaltenstatistiken können für eine Tabelle nicht erstellt...
Stimmt, SQL Server läßt nicht mehr Indexes zu. Das ist ja immerhin schon etwas. Zu dumm allerdings ist die Tatsache, daß sysindexes nicht nur die Informationen zu den Indexes speichert, sondern auch Informationen zu Statistiken. Diese wiederum sind lebenswichtig für den effektiven Einsatz der vohandenen Indexes. Dies wird aber so explizit nicht in BOL erwähnt oder ich habe die entsprechdende Stelle bisher noch nicht gefunden.
Versucht man jetzt also auf unserer Tabelle eine Statistik zu erstellen
CREATE STATISTICS test_me_indexes ON test_indexes (c1, c2 ) WITH SAMPLE 5 PERCENT GO
passiert folgendes:
Server: Nachr.-Nr. 1910, Schweregrad 16, Status 1, Zeile 1 Mehr als 249 nicht gruppierte Indizes ... Spaltenstatistiken können für eine Tabelle nicht erstellt...
Daß hätte man ja auch aus der ersten Fehlermeldung ableiten können...
Fazit: Nun, immerhin hat BOL Recht, daß man bis zu 249 (und nicht mehr!) Nonclustered Indexes pro Tabelle erstellen kann. Leider kann man danach aber anscheinend keine Statistiken zu seiner Tabelle mehr erstellen, was wiederum auch nicht wirklich prickelnd ist. Erstellt man wiederum zuerst seine Statistiken kann man anderseits keine 249 Nonclustered Indexes erstellen. BOL sollte hier vielleicht etwas genauer sein und von 249 Einträgen in sysindexes pro Tabelle zzgl. einem für den Clustered Index und einem für BLOB Daten sprechen.
Wie schon weiter oben gesagt, ist daß in fast allen Fällen kein wirklicher Beinbruch, aber interessant zu wissen ist es schon.
Eine weitere Frage mag man sich vielleicht aber auch noch stellen. Wie der obige Auszug aus sysindexes zeigt, hört die indid bei 250 auf. 255 ist reserviert für BLOB Daten. Was ist eigentlich mit den freien Zahlen dazwischen? Reserviert für zukünftige Erweiterungen? Wohl kaum! indid ist vom Datentyp SMALLINT und kann somit weit mehr als 256 Ausprägungen annehmen. Für sachdienliche Hinweise wofür indid 251, 252, 253 und 254 dienen könnten, bin ich dem geneigten Leser dankbar.
Bookmarke/Suche diesen Beitrag mit:
