September 11, 2006 at 8:56 am
I am looking to partition a table by year. The reason for this would be to make certain years read-only from the database backend access. I was thinking that if I can partition it to another filegroup, I can make the filegroup read-only for the historical years. Is there a way to do this in SQL 2000 without Analysis services? I see in SQL 2005 it can be done but not a lot about it in 2000.
Thanks in advance for the comments!
September 11, 2006 at 11:17 am
You could change the table to a view and write the appropriate instead of triggers.
September 11, 2006 at 12:21 pm
Look up Partioned Views in BOL. You'll wind up creating the separate tables to hold the different years (place them wherever you like, even on different servers). The partitioning is enforced by first placing CHECK constraints on the date column, including making that column part of the clustered key if it isn't already. (Hint: always use the "check existing data on creation" option whenver touching that constraint!)
Then you build a view that will UNION ALL each of your year tables together. You may need to place an INSTEAD OF trigger on the view to handle key generation depending on your scheme.
If all the proper table and database SET options are in place, then queries against the view will be routed to the appropriate tables. You can place additional indexes on the older read-only tables if you like.
-Eddie
Eddie Wuerch
MCM: SQL
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply