Click here to Skip to main content
13,300,625 members (50,365 online)
Click here to Skip to main content
Add your own
alternative version

Tagged as


5 bookmarked
Posted 20 Jan 2011

Tips for SQL-Indexes

, 21 Jan 2011
Rate this:
Please Sign up or sign in to vote.
Tips for SQL-Indexes
Tips for SQL-Indexes

Clustered Index:
1. The queries that selected by a range of values or where you need sorted result.
2. The queries that use the “join” or “groupby” clause, SQL Server always uses internally merge sort.
3. The queries return large result set just not for few.
4. Table columns contain wide range of distinct values.
5. Don't create clustered index on a column that undergo frequent changes.
6. Always maintain proper “fillfactor” at the time of creating index. Normally the size of page is 6KB, “fillfactor” indicates to SQL keeps some free space in the page, if the data is not changing frequently then set “fillfactor” 60-70.
7. Avoid creating clustered index on composite key.
8. Keep the width of clustered index as narrow as possible.
9. Try to create unique clustered index, else internally SQL Server maintains 4 byte extra to maintain the unique.

Non-Clustered index:
1. Nonclustered in index are best for queries that return few rows it always goes to the clustered index to find the row.
2. If the same query is going to access over and over on the same table, consider creating a covering index on the table.
3. Contain columns frequently involves in search condition, such as where clause that returns exact result.
4. Queries that are use the “join” and “groupby”, and use filters.
5. Do not create more nonclustered index.


This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


About the Author

Mahendra Vishwakarma
Software Developer (Senior)
India India
No Biography provided

You may also be interested in...

Comments and Discussions

GeneralReason for my vote of 5 thanks for sharing - have 5 Pin
Pranay Rana24-Jan-11 2:02
memberPranay Rana24-Jan-11 2:02 
GeneralGood work mahendra. You can further enhance your knowledge o... Pin
Pravin Patil, Mumbai20-Jan-11 22:47
memberPravin Patil, Mumbai20-Jan-11 22:47 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

Permalink | Advertise | Privacy | Terms of Use | Mobile
Web01 | 2.8.171207.1 | Last Updated 21 Jan 2011
Article Copyright 2011 by Mahendra Vishwakarma
Everything else Copyright © CodeProject, 1999-2017
Layout: fixed | fluid