June 25, 2008 at 10:58 am
Hi, I am working with an existing table with no clustered index, just a non-clustered index. The column has multiple columns, but the key ones it a unique rowID, a userID, and Insert_Date.
The nonclustered is on the rowID, which we don't query on. UserID is searched on the most, and I thought by sorting the Insert_Date would benefit too (each UserID can appear in the table multiple times).
So even thought the UserId isn't unique, I thought putting the Clustered Index on that would benefit my queries the most. If I want to sort by Insert_Date then I would do:
CREATE CLUSTERED INDEX (UserID, Update_Date)
Is this okay? What do you guys recommend and why?
June 25, 2008 at 11:07 am
Try the clustered index you are proposing on a test copy of the database. Test it out, see if it works okay. If so, use it.
The main thing is that you want your clustered index to include the columns you most often query ranges from. If that's not applicable, then the most common Where can work out okay.
Keep in mind that a clustered index on a column that isn't your sequential insert will result in table and index fragmentation as you insert/delete/update data. There are ways to deal with that (leave some padding in the index, etc.). (By "sequential insert", I mean something like an ID column, or a "DateAdded" column. If something like that is your clustered index, data always gets added at the end of the table, which makes for less fragmentation.) If you can defrag frequently enough, that might not matter too much.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
June 25, 2008 at 11:25 am
In the least, you can take a very very cursory look at how the query optimizer has been viewing queries involving the table in question by downloading and using the SQL Server performance dashboard http://www.microsoft.com/downloads/details.aspx?familyid=1d3a4a0d-7e0c-4730-8204-e419218c1efc&displaylang=en; and click on "missing indexes" at the bottom left of the dashboard_main page. BTW, You need to be running SQL Server 2005 SP2 for this dashboard to work.
Certainly, trial, error, review, rewrite, repeat the trial is how we've done things for years anyway, but if you can use a free tool to take a quick look at how the table is being referenced (if you're on SQL 2005 SP2) then why not?
Cheers.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply