Clustered Index order

[moleboy]

Having a disagreement at the moment and hopefully someone can help.

I have a database with 2 million records in a main table.

The table has a clustered index on areaID, date, userID, typeid in this order.

I have been told that i should change the order by swapping the the date and areaID as this will speed up performance (there are 1000s of records inserted each hour) due to the table forcing the data to change order after each insert.

Is this right??

 

[jbenson001]

Any index, clusterd or not, will cause perfomance issues on an Insert or Update. A clusterd index more because of the physical order of the data.
I am not sure why someone thinks swapping those 2 columns will matter. Is it that you have lots of queries based on date? Even so, it will not matter to the query optimizer. Why do they think it will matter?

Jim

 

[moleboy]

Jim,

I'm not 100% sure how SQL server uses indexes but
i've been told it's a snaphot of the table data but in the the specified index order.

They're implying that if you use the datetime at the top then this will always order the records by date as opposed to the areaID which can be anything from 1-200. Therefore less reordering will be required

The majority of queries are inserts and selects.

Thanks

Derek

 

[jbenson001]

Well I would say that have a good point if you only had the clusterd index on date, but you have a composite cluted index on areaID, date, userID, typeid. So it orders on all the columns, not just the first, so it would order by date, then arieaID ..etc, so I don't really think it would matter what order the columns are in.
Hopefully some SQL Server guru can weigh in on this...

Jim

 

[mrdenny]

The clustered index orders the data in the index based on the order of the columns in the index. In this case the index would first be ordered by areaID, then Date, then UserID, then typeID.

For better insert performance I would build this index as Date, areaID, userID, typeID (keep in mind I'm not sure which has more values areaID or UserID; they may need to be switched). What is the fill factor set to on the index?

Something to keep in mind with indexes the order of the Clustered index does not actually effect the layout of the data in the physical table. The last time that the order of the clustered index effected the order of the data in the table was in SQL 6.5.

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005) / MCITP Database Administrator (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[vbkris]

>>Something to keep in mind with indexes the order of the Clustered index does not actually effect the layout of the data in the physical table.

wow, that is something new to me. i was thinking that alwas the physical data representation was the same as that of the clustered index.

can you give me some links regarding the same???

Known is handfull, Unknown is worldfull

 

[mrdenny]

Someone from Microsoft pointed it out to me in BOL. I've been trying to find it again on and off but haven't been able to. I'll look again when I get to work. I don't have SQL installed at home.

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005) / MCITP Database Administrator (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[vbkris]

no issues. do it when you are free...

Known is handfull, Unknown is worldfull