How many non clustered index in a table?

Ashok

8y9.3k

Hi All,

I have 150 columns in my table,

in that table how many non-clustered indexs are created by manually?

Thanks All

7 Replies

Know the answer? Post it — somebody with the same question will find it here.

Rajesh Gami8y ago
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 Nikkhah8y ago
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 Pandya8y ago
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
Debasis Saha8y ago
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.
Ashok8y ago
shell i create 999 non-clustered indexes in 150 columns r not
n how many i created in that table?
Debasis Saha8y ago
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.
Manav Pandya8y ago
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