T-SQL Tuesday #23 – Non-ANSI JOINs Discontinued in Denali

T-SQL Tuesday is a monthly SQL Server blogging event.  One of us sets a topic for the month’s event, and we all have a week in which to write a blog post that fits that topic.  This month’s event is hosted by Stuart Ainsworth (blog), and he’s chosen the subject of “JOINs”.

Denali

As usual, the new version of SQL Server, codenamed Denali, comes with a list of discontinued features.  (There’s a similar list of deprecated features.)  Among these are some types of join that may be in your applications and scripts, specifically *= and =*. These are used as JOIN operators inside an old cartesian join, such as:

select d.name, f.filename
from sysdatabases d, sysaltfiles f
where d.dbid *= f.dbid

The solution is to use the ANSI join syntax.

This isn’t news. In fact, these operators have been deprecated for best part of a decade (since SQL Server 2005), and only usable in databases set to compatibility of under 90. If you had tried the script above against a SQL Server 2008 R2 server, and your database was set to compatibility level 80, it would have worked. If you had the compatibility level 90 or higher, then you would have received an error:

Msg 4147, Level 15, State 1, Line 5
The query uses non-ANSI outer join operators ("*=" or "=*"). To run this query without modification, please set the compatibility level for current database to 80, using the SET COMPATIBILITY_LEVEL option of ALTER DATABASE. It is strongly recommended to rewrite the query using ANSI outer join operators (LEFT OUTER JOIN, RIGHT OUTER JOIN). In the future versions of SQL Server, non-ANSI join operators will not be supported even in backward-compatibility modes.

Notice that that error message not only tells you how to get round the problem, but also what you need to do in order to fix it. Very helpful.

If you run this against a Denali server, you just get the rather terser error:

Msg 102, Level 15, State 1, Line 5
Incorrect syntax near '*='.

Denali no longer understands this syntax. Note that Denali does, however, still recognise the “*=” token, but as an operator, eg:

declare @x int = 3
select @x *= 17
select @x

How do I know which deprecated features I’m using?

To find out, run the following query:

SELECT instance_name AS 'Deprecated Feature', cntr_value AS 'Times Used'
FROM sys.dm_os_performance_counters
WHERE object_name = 'SQLServer:Deprecated Features'
and cntr_value > 0

(freely adapted from a query on Allen White’s blog) Note that these values are across the instance of SQL Server, rather than against a database, and as for identifying *which* application is using them…