Understanding Clustered Index - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Understanding Clustered Index

Author

Topic

uberman
Posting Yak Master

159 Posts

Posted - 2005-12-15 : 06:45:55

I am trying to understand more about the where, when and how of clustered indexes!

Given


CREATE TABLE PRODUCTS(
productid INTEGER IDENTITY(1,1),
product_name VARCHAR(100)
...more stuff...
)

CREATE TABLE PRODUCT_IMAGES(
imageid INTEGER IDENTIY(1,1),
productid INTEGER NOT NULL,
...image size n stuff...
)
FK on productid between the two table.

Where each product is going to have a number of images that will need to be displayed against it.

Is it best to have the clustered index on PRODUCT_IMAGES on the "productid" field so that when getting the images for a product all the records are "clustered" together rather that all over the place?

Or have I completely missed the point?

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2005-12-15 : 08:16:27

Putting the clustered index on productid will make retrival of the data by productid faster, since all the image info for a productid is "clustered together".
Putting the clustered index on imageid will benefit inserts into the table (less page splits, data fragmentation).

I would probably put the clustered index on productid + 1 column for uniqueness ("image_name","image_position",?),
then I would ask myself if I need the imageid column at all?

http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx

rockmoose

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources