This is day twenty of the SQL Advent 2012 series of blog posts. Today we are going to look at why you should use the new features of the product or language.

Every now and then I get to look at some code that is recently written, looking at it you would think the code is written in 1999. I see insert into temp tables with an identity column in order to return a row number. I also see the old style way of doing a transpose/pivot. There are many more examples I could write down but I am not going to bore you with it here. Why is this bad? I can think of two examples.

  • You are not keeping your skills up to date
  • Even though your code is still valid, it might not be in the next version of the product

Let’s take a quick look at both of these scenarios

Even though your code is still valid, it might not be in the next version of the product

Even though the text data type is deprecated, you can still use the text data type, however in the next version of SQL Server it might not be allowed anymore. Now you have a problem if you want to move to the next version. Luckily there is a dynamic management view that you can use to see everything that has been deprecated.

To quickly find out if you are using features that have been deprecated, you can run the following query

select instance_name,cntr_value
from sys.dm_os_performance_counters
where Object_name = 'SQLServer:Deprecated Features'
and cntr_value > 0

That might give you back something like the following

SET ROWCOUNT                                                   1485
Database compatibility level 80                                 21
DATABASEPROPERTYEX('IsFullTextEnabled')                         369
DATABASEPROPERTY                                                22576
INDEX_OPTION                                                    25
DBCC SHOWCONTIG                                                 4
Table hint without WITH                                         603
Data types: text ntext or image                                 546
More than two-part column name                                  2

You can read more about this in the following blog post: Find Out If You Are Using Deprecated Features In SQL Server 2008. You can now use the output of that query to figure out what not to use in the future.

In java and .NET you can also check if code has been deprecated, here is what it would look like

In java

 * @deprecated  Use this in the future and you will suffer
public void doStuff() {  
  // ...  


[Obsolete("Use this in the future and you will suffer")]
public void doStuff() {  
  // ...  

Now that you know you are still using some of these features in your code, it is time to start abandoning the use of code like that for new development.

You are not keeping your skills up to date

In the Stay relevant and marketable post I already explained why it is important to use the latest version. However just using the latest version doesn’t mean you are also using the latest T-SQL features or the latest Java or .NET libraries. So you might be “using” SQL Server 2012 but if all your code in the database was restored from a 2005 backup you are not using any SQL Server 2012 code. Now when you go for an interview and they ask you about some of the new things that they added like the MERGE statement.

Here are some of my posts about the new stuff that has been added to SQL Server since version 2005

01: Date and time

02: System tables and catalog views

03: Partitioning

04: Schemas

05: Common Table Expressions

06: Windowing functions

07: Crosstab with PIVOT


09: Dynamic TOP

10: Upsert by using the Merge statement

11: DML statements with the OUTPUT clause

12: Table Value Constructor

13: DDL Triggers

14: EXCEPT and INTERSECT SET Operations

15: Joins


17: varchar(max)

18: Table-valued Parameters

19: Filtered Indexes

20: Indexes with Included Columns


22: Dynamic Management Views



That is all for day twenty of the SQL Advent 2012 series, come back tomorrow for the next one, you can also check out all the posts from last year here: SQL Advent 2011 Recap