How many non clustered index in a table?

SQL Server 2005: 1 Clustered Index + 249 Nonclustered Indexes = 250 Indexes per table

SQL Server 2008: 1 Clustered Index + 999 Nonclustered Indexes = 1000 Indexes per table

Syntax to create Non Clustered Index----

CREATE NONCLUSTERED INDEX IX_tblBooks_Auhthor_id

on tblBooks (Auhthor_id)

In above syntax tblBooks is Table Name and Auhthor_id is Column name on which index is created.

The datatype of index_id in sysindexes means:

·         0 for heap

·         1 - clustered index

·         >1 nonclustered

·         >=3200 - XML indexes

So, we can still observe increase up to 3198(3199-1) in future versions of SQL Server.

I thought previously that sys.indexes is synonym to sysindexes but I found just now that they are different, sysindexes has indid (instead of index_id) and does not contain rows for XML indexes!

index_id from sys.indexes has type int(4bytes) and indid from sys.sysindexes has type smallint (2bytes) (SQL Server 2008, probably increased from previous versions)