SQL Server Index Tuning / Clustered vs Non-Clustered Indexes

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:)

Advertisements

About Vijay Modi

Having 12+ years of experience in web application development. Expertise in various domains like E-Commerce, E-Learning, Insurance. I have expertise in web application development, window application development, Performance improvement, bug fixing etc. I have worked on various Microsoft technologies including ASP.Net, MVC, WebApi, WCF, Entity Framework, WPF, Window Application Development, AWS, Azure, AngularJS 2.0 / 1.5.2, .NetCore framework, jQuery. I am believing in quality work and achieving deadlines. Also like to work on new technologies and quick learner.
This entry was posted in Uncategorized and tagged . Bookmark the permalink.

13 Responses to SQL Server Index Tuning / Clustered vs Non-Clustered Indexes

  1. sumesh says:

    “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….

  2. Giribabu says:

    very good article

  3. 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

  4. Suresh Kumar K says:

    very straight forward expanation about the clustered and non-clustered index. Thanks

    • vino says:

      hi friend…..

      to sort the data at limited order, Non-clustered index to be created on which column has to be searched frequently………

  5. Hemraj Gujar says:

    SQL Server 2008 support 999 Non Clustered Index on a Table.

  6. Ritesh says:

    Thanx vijay sir,

  7. rentc says:

    I was asked this question in one of the interviews. Your article is well articulated.

    Thank you!

  8. Rajeev says:

    its really helpfull, keep it up sir

  9. 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

  10. surajjain says:

    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

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s