With each version of SQL server certain items are discontinued (removed) from that release, and other items are marked for deprecation (removal in a future release). Earlier this week I saw some code that I remembered being on the deprecation list so I thought it would be a good idea to put together a quick post of some of the more interesting development focused items and provide people links for more information.

Mainstream support for SQL 2008 R2 ended in 2014, and extended support ends in 2019. As that date nears many of our customers re looking at upgrading to SQL 2012 or SQL 2014; where mainstream support ends in 2017 and 2019 respectively.

As we continue to support our software products across the platform it is good to have an understanding of features that are being removed each release that may require support work on our part for legacy code installed on site.

Discontinued Features

SQL 2012

Reference: https://msdn.microsoft.com/en-us/library/ms144262(v=sql.110).aspx

  • Old style Non-ANSI joins, which has been on the list for years, has finally been removed.  Example: FROM tableA, tableB WHERE A.Col =* B.Col
  • Compatibility Level 80 (SQL 2000) is no longer supported
  • RAISERROR in the format RAISERROR integer ‘string’ is discontinued.

SQL 2014

Reference: https://msdn.microsoft.com/en-us/library/ms144262(v=sql.120).aspx

  • Compatibility Level 90 (SQL 2005)  is no longer supported

SQL 2016

Reference: https://msdn.microsoft.com/en-us/library/ms144262.aspx

  • Nothing announced yet

Deprecated Features

Below is the deprecation list for SQL 2016.  Many items are carried forward from previous versions when not discontinued.

Reference: https://msdn.microsoft.com/en-us/library/ms143729(v=sql.130).aspx

Here is the list that may be more common at development shops though you should review the entire published list.

  • SQL will require statements be terminated with a ;
  • String literals as column aliases
  • SET FMTONLY (handy trick for certain SSRS issues) is being replaced
  • DROP INDEX IndexName ON TableName syntax is replaced
  • Text, ntext, image datatypes are no supported
  • SET ROWCOUNT for UPDATE / INSERT / DELETE
  • GROUP BY ALL
  • Returning a result set from a trigger
  • Many DBA feature syntax options are replaced
  • SQL Profiler replaced by extended events

When Microsoft puts something on the deprecation list that typically add an event to capture its usage on your instance.

There are a few ways you can review to see if you are logging any.

From the last time the SQL Server started you can check the sys.dm_os_performance_counters DMV (starting in SQL 2005).

You can also do it using an Extended Event trace or a SQL Profiler trace (why aren’t you using extended events yet?)

 

Have you been burned before with syntax being deprecated that was used in production code? Or perhaps you just have some general comments on what Microsoft has marked for deprecation.

Let us know in the comments below.