Simon Worth’s SQL Server Weblog

SQL Server and its many complexities

SQL Server 2008 Deprecated Features

leave a comment »

With each release of SQL Server there are deprecated features – and 2008 is no exception. Deprecated features are features that will not be supported past the current version of the product. In the case of SQL Server 2008 there are many features found in 2005 that will still work in 2008 but will not work any further than that.

While there are many web sites that list the deprecated features in SQL Server 2008, it is difficult to look at all of them and figure out which ones will have impact. This is especially true in the case of T-SQL – simply because of the amount of code we put into it.

With SQL Server 2008 there is a new performance object called “SQLServer:Deprecated Features”. This performance object contains many counters – each containing a feature marked for removal in the next version.

Knowing what features are going to disappear in a future release is valuable from an upgrade path point of view. Knowing what needs to change in order to be error free in the next release will aid the transition to the newest product.

In order to use the SQL Server performance object of deprecated features it’s as easy as opening performance monitor and adding the counters you are interested in. Open the run dialog box and type in “Perfmon” and click OK. This will open Performance Monitor (depending on the OS you are using you may have to navigate around a bit to get to the counters display area – I’m looking at you Vista!). To add the deprecated Features object, right click on the graph and hit “Add Counters…”. Once the counter dialog box is open, scroll through the available counters until you reach “SQLServer:Deprecated Features” and click on it. This will expose the counters available for that performance object (there are many).

Microsoft has a good list of the available counters here – listing the counter name and a description of what it captures. Below is a list of features that will be removed from the product in a future release that I can see having an impact on current development.

  • ‘#’ and ‘##’ as the name of temporary tables and stored procedures
    • Currently you can create a temporary table just using # or ## (local or global) sign. Not good development practice to just use the # sign without any other characters, but if it can be done you can bet someone will do it.
  • ‘::’ function calling syntax
    • This one actually always bugged me in 2000. In 2005 you could exclude the ‘::’ function calling which made more sense to me. Adding the ‘::’ just seemed like a level of complexity that was not needed and a level of abstraction was removed to the call.
  • @’ and names that start with ‘@@’ as Transact-SQL identifiers
    • If you try to create a table called @TableName you would receive an error indicated incorrect syntax near @TableName, however, if you enclosed @TableName in double quotes the statement would execute and you would in fact have a table called @TableName. That would get very confusing when debugging code.
  • String literals as column aliases
    • You will no longer be able to write things like this ‘MyColumn’ = Column1 FROM…
  • DBCC ShowContig
    • Used to view index fragmentation information for tables – will be replaced by dynamic management view dm_db_index_physical_stats.
  • SET ROWCOUNT
    • Nice feature where you can set how many rows you want returned for a SQL Statement. No longer allowed for INSERT, UPDATE, or DELETE statements. Was once useful for getting rid of duplicate records. TOP can be used instead.
  • Table hint without WITH
    • Easy enough, just add WITH – so instead of SELECT * FROM sys.Objects (NOLOCK), it now becomes SELECT * FROM sys.Objects WITH (NOLOCK)
  • System catalog calls using old style prefix of sys
    • such as sysobjects and syscolumns, rather than sys.objects and sys.columns.
  • Many ‘sp_’ stored procedures will be deprecated in 2008.

Those are just a few of the deprecated features in 2008 for the database engine. You can find a complete list of deprecated features for

Database Engine = http://msdn.microsoft.com/en-us/library/ms143729(SQL.100).aspx

Replication = http://msdn.microsoft.com/en-us/library/ms143729(SQL.100).aspx

Integration Services = http://technet.microsoft.com/en-us/library/ms403408(SQL.100).aspx

Analysis Services = http://technet.microsoft.com/en-us/library/ms143346(SQL.100).aspx

Advertisements

Written by Simon Worth

May 27, 2008 at 9:26 pm

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

%d bloggers like this: