2005 and below:
250 indexes 249 NON clustered and 1 clustered
2008 and Above:
1000 indexes 999 NON cluustered and 1 clustered
1. A table can have 250 Indexes
2. One CLUSTERED Index
3. 249 NON-CLUSTERED Index
1. Script for Creating Table:
DECLARE @intA INT,
@SQLs NVARCHAR(MAX)
SELECT @intA = 1
SELECT @SQLs = 'CREATE TABLE TBL_LimitValidation('
WHILE(@intA<=1024)
BEGIN
SELECT @SQLs = @SQLs + 'COL' + CAST(@intA AS VARCHAR) + ' INT,'
SELECT @intA = @intA + 1
END
SELECT @SQLs = LEFT(@SQLs ,LEN(@SQLs)-1) + ')'
EXEC SP_EXECUTESQL @SQLs
2. Script for Creating NON-CLUSTERED Index :
DECLARE @intA INT,
@SQLs NVARCHAR(MAX)
SELECT @intA = 1
WHILE(@intA<=1024)
BEGIN
SELECT @SQLs = 'CREATE NONCLUSTERED INDEX IND_TBL_LimitValidation' + CAST(@intA AS VARCHAR) + ' ON TBL_LimitValidation('
SELECT @SQLs = @SQLs + 'COL' + CAST(@intA AS VARCHAR) + ')'
EXEC SP_EXECUTESQL @SQLs
SELECT @intA = @intA + 1
END
See the Err message
"Could not create nonclustered index 'IND_TBL_LimitValidation250' because it exceeds the maximum of 249 allowed per table or view."
250 indexes 249 NON clustered and 1 clustered
2008 and Above:
1000 indexes 999 NON cluustered and 1 clustered
1. A table can have 250 Indexes
2. One CLUSTERED Index
3. 249 NON-CLUSTERED Index
1. Script for Creating Table:
DECLARE @intA INT,
@SQLs NVARCHAR(MAX)
SELECT @intA = 1
SELECT @SQLs = 'CREATE TABLE TBL_LimitValidation('
WHILE(@intA<=1024)
BEGIN
SELECT @SQLs = @SQLs + 'COL' + CAST(@intA AS VARCHAR) + ' INT,'
SELECT @intA = @intA + 1
END
SELECT @SQLs = LEFT(@SQLs ,LEN(@SQLs)-1) + ')'
EXEC SP_EXECUTESQL @SQLs
2. Script for Creating NON-CLUSTERED Index :
DECLARE @intA INT,
@SQLs NVARCHAR(MAX)
SELECT @intA = 1
WHILE(@intA<=1024)
BEGIN
SELECT @SQLs = 'CREATE NONCLUSTERED INDEX IND_TBL_LimitValidation' + CAST(@intA AS VARCHAR) + ' ON TBL_LimitValidation('
SELECT @SQLs = @SQLs + 'COL' + CAST(@intA AS VARCHAR) + ')'
EXEC SP_EXECUTESQL @SQLs
SELECT @intA = @intA + 1
END
See the Err message
"Could not create nonclustered index 'IND_TBL_LimitValidation250' because it exceeds the maximum of 249 allowed per table or view."
No comments:
Post a Comment