I've been experimenting with the partitioned table feature in SQL 2008 (evaluation version). Nice work, I can see how the feature would be really useful. I am investigating it for an application that involves a scrolling window based on a datetime
column, so that the table holds one year's worth of data. The datetime is used as an event timestamp, recording it down to the millisecond. New data is loaded every hour. I would like to keep the impact of the new data load minimal, so I want to take
advantage of partition switching when a new hour's worth of data is ready. Trimming old data would only happen once a day. My questions/assertions based on experimenting a little with this and reading what I could find on the subject:
1) Both the partition function and the left-most column of the clustered index should be based on the datetime column value. This takes maximum advantage of the datetime range terms that are in every query generated by the UI (show me events of this
type in the last week) and also allows partition switching for adding new records and trimming old ones.
2) It doesn't make sense to keep an identity column on the partitioned table, despite their convenience as a surrogate key for many editing tools. You would have to add the datetime column to an identity column in a primary key or unique c
View Complete Post