clustered index - non-clustered index- size?

Question

clustered index - non-clustered index- size?

curious_sqldba

SSC-Dedicated

Points: 36502
More actions
May 10, 2010 at 8:56 am

#146931

As i was reading through few articles about clustered and non-clustered indexes, i had few questions...please advice
i) is it that clustered indexes can only be created on primary columns? if yes then i read that clustered index can be composite? how can a table have more than two primary columns?
ii) i came across this many times saying if there an update to the table then the index also has to tbe updated? does this mean that the index size is atleast the size of the table?
iii) if clustered indexes can be created on non-primary key columns then how does sql engine differentiate between clustered and non-clustered...just with the keywork "Clustered"?
sorry for dumb questions..but i have to get this cleared..thanks

Viewing 5 posts - 1 through 5 (of 5 total)

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

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 1

iqtedar (5/10/2010)
i) is it that clustered indexes can only be created on primary columns?

No. A clustered index can be created on any column other than a Lob data type or non-binary-ordered CLR. Limit is same as nonclustered indexes, 900 bytes or 16 columns.

If by primary, you meant primary key, you can have a multi-column primary key. Nothing limits the primary key to a single column.

ii) i came across this many times saying if there an update to the table then the index also has to tbe updated? does this mean that the index size is atleast the size of the table?

The clustered index is the table. It has at it's leaf level the actual data pages. That's the defining feature of a clustered index.

iii) if clustered indexes can be created on non-primary key columns then how does sql engine differentiate between clustered and non-clustered...just with the keywork "Clustered"?

If you're talking about the creation script, yes, just CLUSTERED keyword. If you're talking about architecture, there's a whole load of differences. maybe have a look at this series:

http://www.sqlservercentral.com/articles/Indexing/68439/ (3 parts in total)

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

curious_sqldba SSC-Dedicated Points: 36502 More actions · Answer 2

thanks ..so lets say there is a table of size 10 gb...with a clustered index...does that mean the clustered index size is 10 gb?

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 3

The clustered index is the table. If you have a table that's 10GB with a clustered index on it, you have a 10GB structure consisting of the clustered index with the data pages at its leaf level. Not two separate structures. When you create a clustered index on a table, it converts the table into a b-tree structure.

Did you read those articles?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

curious_sqldba SSC-Dedicated Points: 36502 More actions · Answer 4

i was reading through them now...yeah from the article..i do understand now that clustered index is actually the table itself....thanks for prompt response.