In part 1 of this series, I discussed how Microsoft notifies us of changes between product versions and provided links to the “Backward Compatibility” sections in BOL (Books Online) for SQL Server 2000/2005/2008/2008R2. In part 2, I discussed some compatibility issues with deprecated features which I’d run across. In part 3, I covered tools to detect the usage of deprecated features. Now, in part 4, I’ll take a look at what is coming in the next release of SQL Server – code-named “Denali” aka SQL 11.
While there is not an announced release date yet for Denali, the first CTP (Community Technology Preview) was released in November 2010 and included the availability of BOL for Denali. This includes the “Backward Compatibility” section for Denali. When reviewing any of the Denali BOL articles, you’ll note that there is a disclaimer at the top of each page that this is “preview only” and “subject to change”. So, you may want to check back frequently between now and RTM for any changes – especially as some sections still contain either placeholders or content from 2008/R2 – particularly for components other than the database engine.
If you are currently in the process of upgrading to SQL Server 2008 or R2 and making changes to your applications, then you’ll probably want to go ahead and review the changes announced for Denali and beyond. If it makes sense, go ahead and start incorporating those changes now. You should focus first on the discontinued features in Denali – meaning you will have to make a change to move to Denali if you rely on these features; so why not get rid of them now? (Side note: the good news is that this list is currently shorter than expected based on the SQL 2008 R2 list of “Features Not Supported in the Next Version of SQL Server”. But that could change by RTM since it appears to have been Microsoft’s intent to fully remove them in Denali when R2 BOL was released.)
Currently on the discontinued list:
- Databases in 80 compatibility mode; must be in 90 compatibility mode to upgrade to Denali (SQL 11)
- PWDCOMPARE function parameter for to designate a login prior to SQL 2000
- No AWE support; No support for over 4GB of physical memory on 32-bit OS
Next, tackle the Denali deprecated features list in the section labeled “Features Not Supported in the Next Version of SQL Server”. This would include the features expected for removal in SQL 12. Yes, that is a long way off, but most of the items in the list have been announced for deprecation since at least SQL 2005, so it’s time to consider their replacement and/or retirement. Items up for consideration:
- Databases in 90 compatibility mode; expected to require 100 compatibility mode to upgrade to SQL 12
- replace the DATABASEPROPERTY function with the DATABASEPROPERTYEX function
- replacing sp_dboption stored procedure with ALTER DATABASE
- remove usage of *= or =* (use the ANSI Join syntax)
- sqlmaint utility
- SET ROWCOUNT for INSERT, UPDATE, DELETE (use TOP)
- COMPUTE / COMPUTE BY (use ROLLUP)
- And more…considering many of these were carry-overs from SQL 2008/R2 and expected for removal in Denali, they should already be on your priority list as they may still be removed in Denali since the Denali BOL notice still includes the “subject to change” wording.
If you are really ambitious, then start perusing the list of “Features Not Supported in a Future Version of SQL Server”. And, yes, some of these look familiar as they have been previously announced. Most notable that you may have missed in SQL 2008 deprecation announcements: Not ending Transact-SQL statements with a semicolon. Yes – you read that right! At some point in a future release of SQL Server, T-SQL statements will finally be required to end with a semicolon (;). That’s an easy one – this is an ANSI SQL standard and has been supported since at least SQL Server 2000. It is already required for some T-SQL statements (such as the statement prior to a WITH defining a CTE and the MERGE statement), so just start typing that extra character and get used to it.
As RTM for Denali gets closer, I’ll revisit this topic to note any significant changes.