Reduce no. of non clustered index

Question

Hi, How to reduce no. of non clusterd indexes in sql server table???

Jignesh Trivedi · Answer

hi, for example you have table and has five column, out of five, one is primary key. Table1 Column A, B, C, D, E Here A is primary key so cluster index create by default. now this all four column B, C, D and E are used with where or order by clause or it is used with join. so we have to create noncluster index on these columns to improve performance of our sql query. instead of creating 4 different noncluster index of on table you can create combine noncluster index using Include keyword not sure whether it is useful or not.

Ravi Shekhar · Answer

okay if one column exists in 5 indexes then how to remove this?? all 5 indexes required for 5 different queries then?

Jignesh Trivedi · Answer

hi, I think, these are indexes that really required. but base on project requiremetn you may remove some of them. like table 's column which rarely used in where or in join. i think this is manually process, you have to identify index which really required.... :)

Ravi Shekhar · Answer

Actually i put all indexes by using activity moniter like... http://www.c-sharpcorner.com/Blogs/12986/how-to-get-required-index-in-sql-server-table.aspx I n this scienario there are multiple indexes which contain same columns .thats y i want to reduce no. of indexes

Jignesh Trivedi · Answer

hi, As per my knowledge, non cluster index is not create automatically. base on table structure and table 's column used in join and where clause you have to create non cluster index on that column to speed up you SQL query. here question is why you want to reduce no of non clustered indexed?

Reduce no. of non clustered index

Insert Link

Embed YouTube Video

Table Options

Insert Image

Answers (5)