Hello Friends,
What is cluster and Non-Cluster index and why we need it? Do you know? Just reat this article you will know all about it.
Most database administrators are familiar with the potential performance benefits they can gain through the judicious use of indexes on database tables. Indexes allow you to speed query performance on commonly used columns and improve the overall processing speed of your database.
Microsoft SQL Server supports two types of indexes:
-> Clustered indexes define the physical sorting of a database table’s rows in the storage media. For this reason, each database table may have only one clustered index. If a PRIMARY KEY constraint is created for a database table and no clustered index currently exists for that table, SQL Server automatically creates a clustered index on the primary key.
-> Non-clustered indexes are created outside of the database table and contain a sorted list of references to the table itself.
SQL Server 2000 supports a maximum of 249 non-clustered indexes per table. However, it’s important to keep in mind that non-clustered indexes slow down the data modification and insertion process, so indexes should be kept to a minimum
One of the hardest tasks facing database administrators is the selection of appropriate columns for non-clustered indexes. You should consider creating non-clustered indexes on any columns that are frequently referenced in the WHERE clauses of SQL statements. Other good candidates are columns referenced by JOIN and GROUP BY operations.
You may wish to also consider creating non-clustered indexes that cover all of the columns used by certain frequently issued queries. These queries are referred to as “covered queries” and experience excellent performance gains.
SQL Server provides a wonderful facility known as the Index Tuning Wizard which greatly enhances the index selection process. To use this tool, first use SQL Profiler to capture a trace of the activity for which you wish to optimize performance. You may wish to run the trace for an extended period of time to capture a wide range of activity. Then, using Enterprise Manager, start the Index Tuning Wizard and instruct it to recommend indexes based upon the captured trace. It will not only suggest appropriate columns for queries but also provide you with an estimate of the performance increase you’ll experience after making those changes!
Reference Link: http://databases.about.com/od/sqlserver/a/indextuning.htm
Regards
Vijay Modi:)
“GOD IS LOVE”
Hiiii
Thanks for this article,many of time ur article help me.your article simple and infomative.So keep write
Thanks ones again….
very good article
HI Vijay,
Can u please explain in details about clustered and non clustered Index, specially non clustered index. Question is why we are creating non clustered index, what is use of it, how its work.
If you can explain in B-Tree from also with e.g that will be good to understand.
Thanks in Advance
Bhupendra S M
9848145680
Hello Bhupendra,
I first time came to this blog and seen that you wanted some detailed explanation so thought to share below link with you.
http://www.sqlhub.com/2011/06/list-of-articles-about-index-in.html
very straight forward expanation about the clustered and non-clustered index. Thanks
hi friend…..
to sort the data at limited order, Non-clustered index to be created on which column has to be searched frequently………
SQL Server 2008 support 999 Non Clustered Index on a Table.
Thanx vijay sir,
I was asked this question in one of the interviews. Your article is well articulated.
Thank you!
its really helpfull, keep it up sir
Clustered index doesn’t change the physical order of the table. Check out my blog post which has sample scripts to know how the data is stored internally.
http://vadivel.blogspot.com/2011/09/does-clustered-index-physically-orders.html
I have a table
id (PK) , name (nvarchar), join_date (date time), city_id (Int) ..
Now i often have run queries lilke.. select name from mytbl where city_id=12 order by join_date desc.. So i have created a Non-clustered index on City_id..
Now i m just wondering Adding join_date in the existing index will improve performance or not