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.
| Author |
Topic |
|
Ankit Mathur
Starting Member
24 Posts |
 Posted - 2008-01-16 : 07:53:57
|
| Hi,I have a query regarding indexing of a table.While discussing a topic of speeding up data retrieval operations by using a clustered index on datetime column I was told by somebody that its rather better to create a new column & store a Julian value of the datetime column in this column & use clustered index on this julian column.The new julian column being int type would further speed up data retrieval operations.I on my part was arguing about the authenticity of this claim.Would appreciate if fellow members can throw some light on this topic about their opinions.ThanksAnkit Mathur |
|
|
RickD
Slow But Sure Yak Herding Master
3608 Posts |
Posted - 2008-01-16 : 09:06:09
|
| I would say it would be quicker, the only problem is that it could not be a unique constraint due to the likelyhood of having two dates the same. The better way to do this would be to have another (unique) column as your clustered index and just put a non-clustered index on your date field. You may also want to think about sorting the non-clustered index descending, although this would depend on how you use the field in your main queries. |
 |
|
|
pootle_flump
1064 Posts |
Posted - 2008-01-16 : 11:15:55
|
I would have to disagree with all that I'm afraid - sorry  I would expect the datetime column should be fine on its own. I would test both and see how you get on - proof of the pudding and all that.If your searches on date will return a lot of rows (e.g. you select ranges), and by a lot I mean 5% + of the table then this should either be a clustered index OR a covering nonclustered index. A nonclustered, noncovering index will be useless for queries that return lots of rows.I'm afraid I would not make it descending either. Assuming new records will typically have higher dates than prior entries then ordering descending will mean that you will constantly be splitting the first page of the index on inserts. Rick is right though that it would speed up selects if you are typically interested in the more recent data.You would also need to consider other aspects. You wouldn't usually want a clustered index on a non unique column. You may already have a good clustered index in place that is suitable for other queries etc etc.HTH |
|
|
|
RickD
Slow But Sure Yak Herding Master
3608 Posts |
Posted - 2008-01-16 : 11:51:41
|
| Sorry, are you disagreeing with me or the OP?!?If me, then fair enough, but as you say, it would depend on inserts and what data you would be interested in, as I said. |
|
|
|
|
|
|
Question on Indexing?