I have a presentation on 2012 T-SQL features and also one on Window functions (2005 through 2012) that I have been giving for SQL user groups and SQL Saturdays. I usually get a comment from the audience during the presentation or in the evaluations that sounds something like this “But couldn’t you just do it the old way?” My first experience with this was back in 2007 at a conference in Montreal. If I remember correctly, the session was on 2005 T-SQL features. I learned during that session that I can acknowledge the comment and then say that the session is about the new feature not the old, so let’s move on.
Of course, you can still do things the old way, especially if you find a performance decrease when changing to the newer syntax. There are some exceptions, however, where Microsoft has deprecated and eventually discontinued a feature. An obvious example of this is the old *= for outer joins. The only way to continue using this syntax on a later edition is to set the Compatibility Level of the database to SQL Server 2000. If you are still using this older join style, you will not be able to upgrade the database past 2008 R2. If fact, a SQL Server 2000 database cannot even be upgraded directly to 2012.
One technique I hear a lot of resistance against is Common Table Expressions (CTE). I hear two common (no pun intended) excuses for this. The first reason is “I don’t use CTEs because I care about performance.” The other is “I just haven’t had time to learn about CTEs.” The second reason is easily remedied because there are many blogs and books that can teach you how to use Common Table Expressions. I recommend taking a look at one of my Beginning T-SQL books or one of Itzik Ben-Gan’s T-SQL Fundamentals books.
I hate to get into arguments about performance because, obviously, I don’t know anything about the other person’s data or what they are trying to accomplish. But for the performance excuse, I usually mention that I sometimes see identical or better performance using CTEs instead of sub-queries. It really depends on the situation, sometimes the CTE is better, and sometimes it is the same or worse. For simple queries using 1 level of CTE, I generally see the identical execution plan as well. When the other person then brings up recursion, something that is rarely used, I realize that they have probably written off CTEs without really looking into them or doing much testing.
The main reason I like using CTEs is that they make the query so much easier to read. You cannot nest CTEs, but one CTE can be joined to a previous CTE. I find this much clearer to understand than multiple levels of nested sub-queries. Another nice feature is using the same CTE multiple times in the query. This is something that is probably not used too often, but it is something that sub-queries can’t do.
If a particular query must have optimum performance, be sure to test different techniques. On the other hand, why spend a lot of time squeezing out the best possible performance to save a second or two on a query that runs in a weekly report only to make it difficult to read?