How many non clustered index in a table?

Question

Hi All, I have 150 columns in my table, in that table how many non-clustered indexs are created by manually? Thanks All

Rajesh Gami · Answer

hello.. They are making pretty (rounded) digits... 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 Update : You should have asked why 999 in SQL Server 2008. This had been explained in answer to my question. This increase was explained by introduction of filtered indexes in SQL Server 2008. 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) Regards, Rajesh Gami

Shahriar Nikkhah · Answer

they are some standards in creating indexes, generally i have tables with 50years of data and i only have 4 indexes on the table, You must understand and track the way that this table is been queried, then you can change and optinize the number of indexes and on what field Another thing you shpould look before you create an index - how many records you have? - How wide is your table (number of columns)? - what are the data type? how many [int], [Char], [img] etc... - how may index you have? what is the cluster index on? etc...... The most important thing is that some cases you don't need to create an index on 2 or 3 fields (complex index) you can create an index on one field and add the other fields as an INCLUDE within the index Google for CREATE INDEX and check the INCLUDE Clause Good luck Nik

Manav Pandya · Answer

Hello @Askhok Like i can give you example , we have table Product and you want to create 10 non-clustered index for that Than Point to be noted that : If you create 50 index in table than "DML and DDL" statements will be slow [I mean performance is decreased] So i would suggest , try to use as many as indexes that you actually need it Thanks

Manav Pandya · Answer

Hello Non-clustered index is used to enhance performance for searching any data than normal queries I think there are no any by default index created , we can create as per our requirement After SQL Server 2008 Released : 999 Maximum non-clustered index are allowed per table For more reference that how to create it , you can follow : https://docs.microsoft.com/en-us/sql/relational-databases/indexes/create-nonclustered-indexes I hope it will help you Thanks

Debasis Saha · Answer

U can create maximum 999 non clustered index on any number columns. In that case your index column combination must be unique for each index key.

Ashok · Answer

shell i create 999 non-clustered indexes in 150 columns r not n how many i created in that table?

Debasis Saha · Answer

SQL Server allows maximum up to 249 NON-CLUSTERED indexes that can be created in single table. This has been increased to 999 in SQL Server 2008 R2 and above version for both 32-bit and 64-bit server.

How many non clustered index in a table?

Answers (7)