Clustered or NonClustered

Question

Clustered or NonClustered

wsze

Mr or Mrs. 500

Points: 519
More actions
September 3, 2002 at 3:07 am

#79020

Hi there,
I am pretty new to SQLServer. Can someone explain the difference between Clustered and Non Clustered index? When would they be used? And are there difference in terms of speed of search between the two index?
thanks
/sze

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

crosspatch Hall of Fame Points: 3168 More actions · Answer 1

Hi wsze. There are two main differences between a clustered and non-clustered index.

The first has to do with what the clustering actually does. It basically alters the default sort for your table. Normally a straight select without an order by clause will present the records as they were chronologically inserted. However if there's a clustered index on the table they will be presented in the order of the clustered index. So for example, if you placed had a aname and address table and you placed a clustered index on the Surname then your table would appear to be sorted in surname order.

To do this a clustered index is stored differently to a normal index, and this is the second main difference. The clustered index is stored as actuall data so you will need more space to store and use such an index.

I've not realy found much significant difference in search and return timing but then I have the luxury of working on large beasts of servers, perhaps others have a a different experience.

As to when to use them, well that's more up to convenience, though they're usefulness depends on the granularity of the column/s they're referencing. A combination of First Name/Surname is a good example where there's likey to be a reasonable degree of granularity

I have found it is possible to please all of the people all of the time if you do exactly what they want. Harold Macmillan 1961

wsze Mr or Mrs. 500 Points: 519 More actions · Answer 2

Is it usually adviced to place Clustered Index for Primary Key values?

Chris Hedgate One Orange Chip Points: 25041 More actions · Answer 3

quote:
The clustered index is stored as actuall data so you will need more space to store and use such an index.

The leaf nodes of the clustered index is the actual data, not a copy of it. You are right in that the leaf nodes of the clustered index takes more space than the leaf nodes of the nonclustered index (which are either the clustered index key values, or the physical location of the rows if no clustered index exists on the table).

wsze, check out the part about not having clustered indexes in this article http://www.sql.nu/read.asp?id=12 for some useful info.

Chris Hedgate @ Apptus Technologies (http://www.apptus.se)

http://www.sql.nu

Edited by - chrhedga on 09/04/2002 01:48:40 AM

--
Chris Hedgate http://www.hedgate.net/
Contributor to the Best of SQL Server Central volumes
Articles: http://www.sqlservercentral.com/columnists/chedgate/

katravax Old Hand Points: 353 More actions · Answer 4

quote:
Is it usually adviced to place Clustered Index for Primary Key values?

Not necessarily. The best column for the clustered index is usually the column which will be most frequently used in sorting/grouping/ranging queries. It's hard to determine index needs up front. You generally need to write the queries that will read/update the data first, and see which are used most frequently, and then optimize for those queries first.

- Troy King

kknudson SSCarpal Tunnel Points: 4986 More actions · Answer 5

First a clustered index does not alter the sort order of the DB, it does maintain the db physicaly in the order of the cluster index, at least within a data page.

You should never rely on any default order in a select, always include the ORDER BY if the data is required in a specific order. This is particularly true with SQL2k.

Basically the index structure is identical clustered vs. Non-clustered, except the leaf level, the lowest level, is the actual data pages in a clustered index.

I personally prefer to always include a clustered index, but that is the rule and there are exceptions.

If you are doing random reads / updates etc a clustered index will not necessarily help much. If you are doing range selects it "can" have dramatic effects.

That said though there is an inverse, inserts may take longer if you are not inserting sequentially by the cluster key, and / or you have not defined free space at he page level. As inserts, or updates that increase the row beyond the available space into the data page cause a split and a new page gets created at the end of the filespace.

klk, MCSE

KlK

wsze Mr or Mrs. 500 Points: 519 More actions · Answer 6

Thanks guys for all your valuable advice. I have been looking through my DB and seems to be alot of tables that needs tuning in terms of index.

Question. If non-clustered index has already been created for the table, and now I decide to place in the Clustered index, do i need to drop the current index and recreated back the entire thing?

//sze