Click here to Skip to main content
Click here to Skip to main content

Tagged as

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.

License

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

Share

About the Author

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

Comments and Discussions

 
GeneralReason for my vote of 5 thanks for sharing - have 5 PinmemberPranay Rana24-Jan-11 1:02 
GeneralGood work mahendra. You can further enhance your knowledge o... PinmemberPravin Patil, Mumbai20-Jan-11 21:47 

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

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

| Advertise | Privacy | Mobile
Web04 | 2.8.140827.1 | Last Updated 21 Jan 2011
Article Copyright 2011 by Mahendra Vishwakarma
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid