question

qamooos avatar image
qamooos asked

whats difference between clustered index and nonclustered??

can any one tell me what i should set in clustered index and what i should set in nonclustered index with simple and small example please? i have search on the net , but all are not help ful , over than 1000000000 words
sql-server-2005clustered-indexnonclustered-index
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

SirSQL avatar image
SirSQL answered
The first result from a Bing.com search sums things up quite nicely. Go check out [ http://mssqlcity.com/FAQ/General/clustered_vs_nonclustered_indexes.htm][1] Your clustering key should, wherever possible, be an ever incrementing key (frequently but not always this will be your primary key). Non-clustered indexes should be added to provide performance improvements for query support. [1]: http://mssqlcity.com/FAQ/General/clustered_vs_nonclustered_indexes.htm
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Magnus Ahlkvist avatar image
Magnus Ahlkvist answered
I often use a paper Phone Book as an example. The phone book is mostly sorted by lastname, Andersson comes before Axelsson etc. Firstname is also a key, if two persons have the same lastname, they sort on firstname. Adam Smith comes before Axel Smith. The actual data is sorted by (lastname,firstname). In SQL Server, a tables datapages are sorted by the clustered index. (Lastname,firstname) is the clustered index of a phonebook. After the actual data in a phonebook, you often have a streetname-registry pointing out the names of the people living on the street. That's a non-clustered index. In SQL Server, instead of being stored along with the datapages, the non-clustered index is stored in its own datapages, with pointers to the clustered index of the datapages where the actual data is stored. When choosing a candidate column (or columns) for a clustered index, there are a few things to consider. One is fragmentation. To avoid fragmentation, you can use an identity-column as clustered index. But on the other hand, you really have no use of an identity-column - it's redundant data stored in the table and it won't help you search. I usually look at how much the data will change when choosing between an identity-column and a natural column as primary key (and clustered index). If I take a Country-table for example, it's pretty static. The UN might add or remove a country once a year or so. In that case, I'd choose the two-digit ISO-name for the table (UK for United Kingdom, SE for Sweden). That way, when I add a City-table to my database, and want to search for all cities in Sweden, I don't need to use a join to find "Sweden" as a name. Instead I can just query the City-table WHERE CountryID='SE'. If I've chosen (CountryID, CityName) as primary key (and clustered index) for the City-table, the search will be really fast - just a clustered index-seek, instead of a key lookup. On the other hand again, the clustered index is stored along with the non-clustered index, so you'll get bigger non-clustered indexes if you use a wide clustered index. I could go on and on about clustered versus non-clustered indexes, natural versus surrogate keys etc. It's a big topic... The headline of you rquestion is "Whats difference between clustered index and nonclustered". I think I've answered that with the phone-book example. But in your question-text you ask "..tell me what i should set in clustered index and what i should set in nonclustered index with simple and small example please". And the answer to that is, like so often, "it depends". It depends on so many things that it's not really possible to give a general advice. There's also many "religious" thoughts on this topic. Some people say "Never use natural keys" and some people say "Never use surrogate keys". I try to consider how static the content of a table is, if the database is to be online and in heavy use 24/7 (and therefore not very suitable for running index maintenance jobs often), if there's a good natural key in the data and how much fragmentation that key would cause.
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

HayathKhan avatar image
HayathKhan answered
Clustered index is created on Primary Key. Since a table can have a single primary key, a clustered index can be present only once for a table. Where as there can be mutiple Non-Clustered indexes for a table.
3 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Magnus Ahlkvist avatar image Magnus Ahlkvist commented ·
A primary key is clustered by default, but it can just as well be a non clusterered index. If the primary key is added after a clustered index was defined it becomes a non-clustered index. If the Primary Key is created with the NONCLUSTERED keyword it also becomes non-clustered: CREATE TABLE t (ID int identity(1,1) PRIMARY KEY NONCLUSTERED, ...
0 Likes 0 ·
Sacred Jewel avatar image Sacred Jewel commented ·
@Magnus Ahlkvist Slight change in your statement :) > If the primary key is added after a clustered index was defined it becomes a **unique** non-clustered index
0 Likes 0 ·
Magnus Ahlkvist avatar image Magnus Ahlkvist commented ·
Oh well, that kinda comes with it being the primary key.
0 Likes 0 ·
RajnishKumar avatar image
RajnishKumar answered
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.