Monday 15 July 2013

A table can have how many indexes

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."

No comments:

Post a Comment

C# LINQ Joins With SQL

There are  Different Types of SQL Joins  which are used to query data from more than one database tables. In this article, you will learn a...