Deprecated Features in SQL Server 2008

As I have previously written about, Microsoft has exposed information in SQL Server 2008 in sys.dm_os_performance_counters about deprecated features in SQL Server 2008 (which are features that won’t be supported in the next or future versions of SQL Server). This info is also exposed in Perfmon.

When I run the following query against a SQL Server 2008 default instance, I get 228 rows back:

    -- Look for deprecated features in SQL Server 2008 
    SELECT object_name, instance_name, cntr_value 
    FROM sys.dm_os_performance_counters 
    WHERE object_name = 'SQLServer:Deprecated Features' 
    -- (Default instance) Alter this for named instances 

The online version of Books Online (BOL) has a link that explains in more detail what each of these features are and what their replacements are, broken down by whether they are not supported in the next version of SQL Server (SQL11) or whether they are not supported in a “Future” version of SQL Server.

Now, I know that many people are still running SQL Server 2000 and SQL Server 2005, so worrying about deprecated features in SQL Server 2008 may not be on the top of their priority list. Still, I like to plan and prepare for the future, and I think it is a good idea to be aware of keywords, commands and options that are going away in the future so you don’t use them for new development.

Being a “detail oriented” DBA, I would also start to change existing code to use the new methods whenever I had a chance. I would also make sure my developers were not using deprecated methods for new code. The prep work that you do now and over the next year or two will make it much easier to upgrade from an older version when you and your company are ready to move forward.

Here are a few features that you might want to avoid using now, since they will not be supported in SQL11:

The FASTFIRSTROW query hint should be replaced by OPTION (FAST n)

The SET ROWCOUNT command for INSERT, UPDATE, and DELETE statements should be replaced by the TOP(n) keyword

The old non-ANSI outer joins ( *= and =*) should be replaced by ANSI joins (LEFT OUTER JOIN, RIGHT OUTER JOIN)

Here are a few features that will not be supported in a future version (after SQL11):

Data types text, ntext and image are replaced by varchar(max), nvarchar(max), and varbinary(max) data types

The DBCC DBREINDEX command is replaced by the REBUILD option of ALTER INDEX

The DBCC INDEXDEFRAG command is replaced by the REORGANIZE option of ALTER INDEX

The DBCC SHOWCONTIG command is replaced by using the sys.dm_db_index_physical_stats DMV

Specifying table hints without using the WITH keyword. Make sure to use WITH (THEHINT)

Not ending Transact-SQL statements with a semicolon. Make sure to end T-SQL statements with a semicolon

The use of the vardecimal storage format. Use Page or Row compression instead

This entry was posted in SQL Server 2008. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s