3+ part naming on Columns will be Deprecated

This is something that I’ve found a few people have missed and were surprised when I told them. This appears to have been snuck in with the Deprecated Database Engine Features in SQL Server 2016 under Features deprecated in a future version of SQL Server (note that in 2017 it is still under that heading and 2019 introduced no new deprecations). Specifically, from the documentation it states:

The following SQL Server Database Engine features are supported in the next version of SQL Server, but will be deprecated in a later version. The specific version of SQL Server has not been determined.

Category Deprecated feature Replacement Feature name Feature ID
Transact-SQL Three-part and four-part column references. Two-part names is the standard-compliant behavior. More than two-part column name 3

Very simply, this means you need to stop using 3+ part naming when you reference your columns. For example, writing SQL that looks like this:

SELECT dbo.Customer.[Name] AS CustomerName,
       dbo.Product.[Name] AS ProductName
FROM dbo.Customer 
     JOIN dbo.CustomerOrder ON dbo.Customer.ID = dbo.CustomerOrder.CustomerID
     JOIN dbo.Product ON dbo.CustomerOrder.ProductID = dbo.Product.ID
WHERE dbo.CustomerOrder.Quantity > 5;

In truth, using naming like this makes your code pretty cumbersome anyway. Ideally you should be aliasing your objects in the FROM and then using the aliases to qualify your columns. Switching to 2 part naming (with aliases) therefore has two major benefits:

  1. You stop using a feature that is going to be removed from SQL Server, meaning that your SQL won’t break when it is.
  2. Your code becomes more succinct, which means it becomes more readable.

Thus, for the prior example, you could write something like the above:

SELECT C.[Name] AS CustomerName,
       P.[Name] AS ProductName
FROM dbo.Customer C
     JOIN dbo.CustomerOrder CO ON C.ID = CO.CustomerID
     JOIN dbo.Product P ON CO.ProductID = P.ID
WHERE CO.Quantity > 5;

There’s really no excuse to not start using 2 part naming now. 🙂

Leave a Reply

Your email address will not be published. Required fields are marked *