 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
SQL Server Forum / General / Data Warehousing / December 2005Partition views vs partition tables
I was just going over the new partitioned tables features in SQL2005. They are very proud of how you can move a partition from one table to the other and it's just metadata so it happens quickly and efficiently. But then it struck me that with partitioned views as in SQL2000, you put the data into one table, and you can move that table from one partitioned view to another with very minimal fuss also. There are various constraints in the new partitioned tables regarding keeping indexes aligned with the partitions. The new partition function is neat, of course. But again, the bottom line doesn't seem to me all that clear, is there really something to be gained with the new "partitioned tables" that wasn't pretty nearly there already with "partitioned views"? At this point, even putting some new stuff onto SQL2005, I seem very tempted to use partitioned views. Thanx. Josh >I was just going over the new partitioned tables features in SQL2005. > They are very proud of how you can move a partition from one table to [quoted text clipped - 15 lines] > At this point, even putting some new stuff onto SQL2005, I seem very > tempted to use partitioned views. Some problems with partitioned views: The seperation of the data into seperate tables was never perfectly hidden by the partitioned view. Some transactions could be coded against the partitioned views, but some still had to be coded against the base tables. This required poluting application code with complicated partition-by-partition logic, and made the partitioning scheme impossible to change. Partition management required a lot of custom code, and there was not standard methodology to acomplish it. It required more of a developer than a DBA to implement and manage it. The text of the partitioned view definition imposed artifical limitations on the partitioning scheme. With partitioned views indexes _had_ to be aligned. With partitioned tables you can have non-partitioned or differently-partitioned indexes. EG you can have a global customer index across a partitioned sales table. Partitioned views could not be referenced by a foreign key. David >Some problems with partitioned views: > [quoted text clipped - 4 lines] >partition-by-partition logic, and made the partitioning scheme impossible to >change. Not all views are updatable, I guess. >Partition management required a lot of custom code, and there was not >standard methodology to acomplish it. It required more of a developer than >a DBA to implement and manage it. Hmm. >The text of the partitioned view definition imposed artifical limitations on >the partitioning scheme. > >With partitioned views indexes _had_ to be aligned. With partitioned tables >you can have non-partitioned or differently-partitioned indexes. EG you can >have a global customer index across a partitioned sales table. So with views, each partipating table would need a separate customer index, and I could write a select against the view and (I hope!) SQLServer would use all the separate indexes ... but, for instance, you'd have trouble enforcing uniqueness across tables, right? >Partitioned views could not be referenced by a foreign key. Right, right, DRI would be tough, wouldn't it? >David I could generate a few more questions, but this was very helpful, thanks! Josh >>Some problems with partitioned views: >> [quoted text clipped - 7 lines] > > Not all views are updatable, I guess. The problem extends beyond updates. Some SELECT statements ran so poorly against the partitioned view that they had to be replaced by cursors, temp tables and dynamic SQL against the base tables. Very ugly. . . . > So with views, each partipating table would need a separate customer > index, and I could write a select against the view and (I hope!) > SQLServer would use all the separate indexes For simple queries that usually worked, but for more complex queries you would end up with a lot of table scans. David >> So with views, each partipating table would need a separate customer >> index, and I could write a select against the view and (I hope!) >> SQLServer would use all the separate indexes > >For simple queries that usually worked, but for more complex queries you >would end up with a lot of table scans. Well, I hear you (and have heard vague rumors of the sort before), but this sounds more like a bug than a feature, do we know if it's any better in 2K5? Josh >>> So with views, each partipating table would need a separate customer >>> index, and I could write a select against the view and (I hope!) [quoted text clipped - 6 lines] > this sounds more like a bug than a feature, do we know if it's any > better in 2K5? 2k5 added table partitioning to replace local partitioned views, so I don't imagine much work was done on them. From BOL Local partitioned views are included in SQL Server 2005 for backward compatibility purposes only, and are in the process of being deprecated. The preferred method for partitioning data locally is through partitioned tables. For more information, see "Partitioned Tables and Indexes". David >2k5 added table partitioning to replace local partitioned views, so I don't >imagine much work was done on them. [quoted text clipped - 4 lines] >preferred method for partitioning data locally is through partitioned >tables. For more information, see "Partitioned Tables and Indexes". Thanks again. Josh |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.