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

T-SQL
1
2
3
4
select instance_name,cntr_value
from sys.dm_os_performance_counters
where Object_name = 'SQLServer:Deprecated Features'
and cntr_value > 0
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

Java
1
2
3
4
5
6
7
/**
 * @deprecated  Use this in the future and you will suffer
 */
@Deprecated  
public void doStuff() {  
  // ...  
} 
/**
 * @deprecated  Use this in the future and you will suffer
 */
@Deprecated  
public void doStuff() {  
  // ...  
} 

In .NET

C#
1
2
3
4
[Obsolete("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
08: UNPIVOT
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
16: CROSS APPLY and OUTER APPLY
17: varchar(max)
18: Table-valued Parameters
19: Filtered Indexes
20: Indexes with Included Columns
21: TRY CATCH
22: Dynamic Management Views
23: OBJECT_DEFINITION
24: Index REBUILD and REORGANIZE

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