THE SQL Server Blog Spot on the Web

Welcome to - The SQL Server blog spot on the web Sign in | |
in Search

Rick Heiges

News about SQL Server and the SQL Server Community

Table and Index Partitioning - in Compatibility Mode???

Table and Index Partitioning in SQL Server 2005 is one if the neatest management features.  It allows you to slice up a large table and its associated indexes into more manageable chunks.  This is clearly a 2005 feature.

I saw a post on that was asking about using Table and Index Partitioning in '70' compatability mode.  My first thought was that it would not work.  However, I grabbed my Partitioning script from my SQLConnections session and tried it out in 2005 in '70' mode.  The creation of the Partition Function and Scheme did not work, but operations such as SWITCH did.  I also tried the script in '80' mode.  To my surprise, the entire script worked from creating a partition function and scheme to the SWITCH functionality. 

In '70' mode, you may use partitioning to SWITCH.  In '80' mode, you may define the elements that make up partitioning and use SWITCH as well.

Remember, SQL Server 2005 in a compatibility mode tries to act like the version that you set it to.  There are some things that just can't be emulated from version to version.  Earlier this year, I was one of several colleagues who went to various places around the world to conduct Application Compatibility Labs for ISVs who want to upgrade their apps to 2005.  We used a tool that would replay a 2000 Trace against SQL Server 2000 and 2005.  This tool would then compare the behavioral differences between the two so that the ISVs would not be surprised when releasing the new upgraded application to their users.  This tool is available for FREE from and is called the SQL Server Upgrade Assistant (not Advisor). 

SQL Server Upgrade Advisor is a FREE download from and does a great job looking at the definition of a database and trace file, but does not anticipate behavior.  Assistant focuses on behavior. Example:  One of my clients in South Africa had a Stored Procedure that created a temp table and then issued a SELECT statement with a column that was not in that temp table.  SQL Server 2000 accepted that without problem, but 2005 (in '80' mode) threw an error.  When you think about it, 2000 should have thrown an error as well.  So, did 2005 (in'80' mode) act properly because it threw and error or not?  My view is that it DID act properly because this is clearly an error that 2000 should catch but did not.  Do you want 2005 to emulate 2000 bugs?  I don't think so.

The bottom line is that 2005 may not act like you think it should.  Testing the obvious and the not-so-obvious (like Partitioning in compatibility modes) will help make your upgrade efforts go smoothely.  Therer are FREE tools out there to help you with this; use them.

Published Thursday, August 10, 2006 5:55 AM by RickHeiges

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS


No Comments

Leave a Comment


This Blog


Privacy Statement