When do u use clustered index and non-clustered index?
- 3 Answers
- 7975 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
Can you index views?
What is the maximum size of a row in sql server?
Call by value and call by reference in procedure and function, with NOCOPY.
How to get a list of columns using the "sys.columns" view in ms sql server?
How to start and end transact-sql statements?
What is apply operator in sql?
How to find the list of fixed hard drive and free space on server?
What do you understand by recursive stored procedure?
Are semicolons required at the end of sql statements in sql server 2005?
create index myindex on mytable(mycolumn) what type of index will get created after executing the above statement? : Sql server database administration
How to drop existing indexes in ms sql server?
What kind of problems occurs if we do not implement proper locking strategy?
What is the data type of time?
How each E-R model constructs can be mapped to the relational model?
Does order by actually change the order of the data in the tables or does it just change the output?
