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
*/
@Deprecated
public void doStuff() {
// ...
}
In .NET
[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
08: UNPIVOT
09: Dynamic TOP
10: Upsert by using the Merge statement
11: DML statements with the OUTPUT clause
13: DDL Triggers
14: EXCEPT and INTERSECT SET Operations
15: Joins
16: CROSS APPLY and OUTER APPLY
17: varchar(max)
19: Filtered Indexes
20: Indexes with Included Columns
21: TRY CATCH
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
Denis has been working with SQL Server since version 6.5. Although he worked as an ASP/JSP/ColdFusion developer before the dot com bust, he has been working exclusively as a database developer/architect since 2002. In addition to English, Denis is also fluent in Croatian and Dutch, but he can curse in many other languages and dialects (just ask the SQL optimizer) He lives in Princeton, NJ with his wife and three kids.
