#0165-SQL Server 2012-Discontinued Features-Use of OUTER operators – *= and =*; Msg: 4147


Often, I have seen developers use code that does not conform to the ANSI standards. The reasons are many, but that is not we are discussing today. What we would discuss is that these non-standard practices need to be stopped, in some cases, by eliminating the feature all-together. The result of this effort is that the code ends up in a lot of rework because the product (in this case, Microsoft SQL Server) ends up either adopting newer standards or discontinuing support of the older, non-standardized code. 


One such coding practice is the use of non-ANSI standard OUTER operators  (*= and =*, for right outer join and left outer join respectively). Such code would typically look like:

USE Northwind
GO
SELECT EmployeeTerritories.EmployeeID,
Territories.TerritoryID,
Territories.TerritoryDescription,
Territories.RegionID
FROM EmployeeTerritories, Territories
WHERE EmployeeTerritories.TerritoryID =* Territories.TerritoryID

Output of an OUTER operator (=*) - similar to RIGHT OUTER JOIN


Effectively, the operator *= indicates that all rows from the left table need to be taken and matched with those on the right-hand side table, i.e. it is similar to a LEFT OUTER JOIN. Similarly, the operator =* is the RIGHT OUTER JOIN.


Running such code on any database with a compatibility level of 90 or higher (SQL Server 2005 or higher)  will result in the following error:


Msg 4147, Level 15, State 1, Line 3
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.


The solution to this is to use the ANSI standard, SQL-92 syntax of LEFT and RIGHT OUTER JOIN. The query above, can therefore be represented in ANSI standard code as:

USE Northwind
GO
SELECT EmployeeTerritories.EmployeeID,
Territories.TerritoryID,
Territories.TerritoryDescription,
Territories.RegionID
FROM EmployeeTerritories
RIGHT OUTER JOIN Territories ON EmployeeTerritories.TerritoryID = Territories.TerritoryID

If yours is a product that has evolved from the days when Microsoft SQL Server was in it’s infancy, then chances are that fragments of your code still uses such non-ANSI standard code. It would be a good idea to take a couple of days to revisit the code and make it ANSI compliant to avoid further rework.


Until we meet next time,


Be courteous. Drive responsibly.

Advertisements

Let me know what you think about this post by leaving your feedback here!

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s