When do u use clustered index and non-clustered index?
- 3 Answers
- 7738 Views
- IBM, I also Faced
- E-Mail Answers
Answer Posted / aviral nalwat
Clustered and non-clustered indexes in SQL Server
Overview
When do I use a clustered index vs. a non-clustered index?
This is somewhat of a tough question to answer, and
frankly, the one I'm going to give is the age old standard
database administrator's answer; "It depends." There are
numerous factors into the when and where of index
placement. The good news is that there are only two options
per-sé. The bad news is that there are fairly esoteric
gains and limitations to both.
The basics
I'm not going to go into great detail here about how
indexes function at their lowest level. Whole books have
been written on that subject, and there are folks who
solely specialize in the placement of indexes. Just know
this; according to the "powers that be" a clustered index
is better than no index at all. The biggest difference
between a clustered index and a non-clustered index is that
when you use a clustered index, the section of the table
that comprises the index has its data pages linked
differently from those data pages comprising a non-
clustered index.
In SQL Server 2005 you'll probably hear the term "heap or B-
Tree." What's being referred to here is that a table with
non clustered index is generally referred to as a heap.
A "B-Tree" or "Balanced Tree" is the general structure that
clustered indexes take. They're kind of like a telephone
book. We know that in SQL Server 2005, there are 8K data
pages. There are also eight different kinds of them too.
Index data pages have pointers to smaller subsets of data,
which have pointers to even smaller subsets of data, etc.
For instance, when you open the phone book, what do you see
in the upper left or right hand corners of the opposing
pages? You see the range that the page covers. That's how B-
Trees function.
So what's the difference here? When a clustered index is
applied, the data at the "leaf level" contains the actual
data pages where you'll find the data being searched on. In
a non-clustered index, the data pages at the leaf level
merely have pointers to more data pages containing the
actual data being searched upon. This being the case, the
data pages at the leaf level in a clustered index are only
ordered one way, and "in order."
For instance, if you have a column with an IDENTITY
constraint applied to it as well as a clustered index, the
numbers comprising the IDENTITY constraint will always be
in order. Yet, that is not to say always contiguous, as you
can DELETE rows. But they will always be in order. This
makes for very fast searching, especially when using this
scenario for something like order or invoice ID's.
The trade-offs involved with using clustered or non-
clustered indexes are a world of give and take. One of the
most important things that I was ever taught is that since
clustered indexes keep all data within them at the leaf
level of the B-Tree, any modifications to the data require
a rearranging of the data pages. This means, if you add a
clustered index to a table that is heavily inserted,
updated, or deleted, you will probably need to rebuild or
de-fragment the index more often than when a non-clustered
index is used. This is due to all of the data page movement
that occurs. Once again, the gain you receive is faster
reads of the data, due to the orderly fashion in which the
data is laid out. The other major difference is that you
only get one clustered index per table. You can apply 249
non-clustered indexes.
Keep in mind that although you only get one clustered index
per table, it does not solely need to be comprised of a
single column. You are more than welcome to apply that
index to multiple columns creating a covering index. Think
about the search criteria on the table: What is being
queried? If you're querying multiple columns at the same
time, a covering index may be your answer. Another fine
compromise is evaluating what is being queried, and then
applying a clustered index to the column most commonly used
in WHERE clause(s). Then apply a covering non-clustered
index to the remaining columns in the SELECT statement(s).
Lastly there is also the option to use an Indexed view.
This is essentially Microsoft's implementation
of "materialized views" as seen in other platforms. Be
aware - when you apply an index to a view, you are now
creating a new database object, whereas the result sets
coming from a non-indexed view are only durable as long as
the session is open, and are completely virtual. An Indexed
view is not
| Is This Answer Correct ? | 4 Yes | 1 No |
Post New Answer View All Answers
What is a collation in ms sql server?
what are database files and filegroups? : Sql server database administration
What is 2nf example?
Find first and last day of current month in sql server
What is user-defined inline table-valued function?
What are the operating modes in which database mirroring runs?
What stored procedure can you use to display the current processes?
Determine when an index is appropriate?
What is the difference between clustered index and primary key?
Explain encryption of entire databases without the need for application changes in sql server 2008?
how to take backup bcp out for a column in table in sql server?
Does hive support indexing?
What are the types of containers in ssis?
What is primary key and example?
you notice that the transaction log on one of your databases is over 4gb the size of the data file is 2mb what could cause this situation, and how can you fix it? : Sql server administration
