During my on of the training session, I used a term that we can multiple NON clustered Indexes, instantly one of the participants was very specific about number and said, we can have upto 249 non clustered index in a table.
I instantly said, Your information in not upto date, and you need a course refresher and that’s the reason why you are attending my training.
YES, you can have upto 999 non clustered index from SQL Server 2008 onwards.
How many Non Clustered Index can be created on a single table??
So the correct answer is you can have upto 999 non clustered index per table from SQL Server 2008 onwards but in pervious versions this was limited to 249.
Here is an example, where we are creating 999 indexes on a single table.
use tempdb go CREATE TABLE IndexTest ( column1 int identity ) go -- Creation of Multiple indexes DECLARE @IndexID smallint, @Exec nvarchar(500) SELECT @IndexID = 1 WHILE @IndexID <= 999 BEGIN SELECT @Exec = 'CREATE INDEX [Test' + convert(varchar(5), @IndexID) + '] ON IndexTest (column1)' EXEC(@Exec) SELECT @IndexID = @IndexID + 1 END go -- Select number of indexes on a table SELECT count(*) FROM sys.indexes WHERE object_id = object_id('IndexTest') go -- DROP TABLE go
What if, we try to create another one ?
You will get an error message stating, that you have reached the maximum number of indexes allowed per table.
Msg 1910, Level 16, State 1, Line 1
Could not create nonclustered index ‘Test1000′ because it exceeds the maximum of 999 allowed per table or view.