I'm migrating my data warehouse from Oracle to SQL Server, and I don't know how to manage data without partitions. How do I delete a month of data in SQL Server as I did in Oracle (drop partition). Do I have to use a month table? Thanks.
QUESTION POSED ON: 12 OCT 2004
QUESTION ANSWERED BY: Ian Abramson
Thanks for the question. To me, partitions is a feature that truly shows the differences between Oracle and SQL Server. Oracle's implementation of partitions is very good -- it is easy to use and easy to manage. But when it comes to partitions in SQL Server, this is a different story.
The method that SQL Server uses to implement partitions is via a view. Each "partition" that you will want to create will be its own separate table. The tables are then brought together via a view. There are obvious shortcomings to the approach. Users will need to access the data via the view, which should not be a major stumbling block. However, administration will be much more complicated as you will need to alter the view each time you wish to add or remove partitions. Finally, you will need to perform your updates in the underlying tables as partition views do not allow updates in SQL Server 7, but this restriction has been removed in SQL Server 2000.
Enjoy your migration! I will be interested to hear how the functional differences between SQL Server and Oracle affect your application.
|
|
|
