TIL: T-SQL doesn't have constants and variables aren't a good idea

Today I learned how to use constants in SQL Server stored procedures. While getting a stored procedure reviewed, I got one comment to remove literal values. This is how to bring constants in T-SQL.

SQL Server doesn’t have a keyword for constants. To introduce constants in stored procedures, write literal values next to an explaining comment or use single-row views with the constant values as columns.

1. Don’t use variables as constants

From C# and other programming languages, we’ve learned to use constants or enums instead of magic values all over our code. Often, we would like to bring constants to our T-SQL queries. But…

T-SQL doesn’t have a keyword for constants. And SQL Server engine doesn’t inline variables when executing stored procedures.

The first thing we try by mistake to emulate constants is to use variables.

For example, let’s find all StackOverflow users with two reputation points. That’s not a popular reputation among StackOverflow users. We write something like this,

/* An index to speed things up a bit */
CREATE INDEX Reputation ON dbo.Users(Reputation)
GO

CREATE OR ALTER PROC dbo.usp_GetUsers
AS
BEGIN
    /* This is how we often emulate constants */
    DECLARE @Reputation INT = 2;

    SELECT *
    FROM dbo.Users u
    WHERE u.Reputation = @Reputation
    ORDER BY u.CreationDate DESC;
END
GO

This is the execution plan. Let’s keep an eye on the number of estimated users.

But, there’s a downside. Variables inside stored procedures trigger a different behavior in SQL Server.

Variables and execution plans

When executing a stored procedure, SQL Server creates an execution plan for the first set of parameters it sees. And, the next time we run that stored procedure, SQL Server reuses the same execution plan, even if we use different parameters. We call this behavior Parameter Sniffing.

SQL Server uses statistics (histograms built from samples of our data) to choose the shape of execution plans. SQL Server has to choose the first table to read, the number of threads, and the amount of memory, among other things.

But, when there are variables in a stored procedure, SQL Server builds execution plans, not from statistics (samples of our data), but from an “average value.”

Variables make SQL Server build different execution plans, probably not suited for the set of parameters we’re calling our stored prcedures with. That’s why variables aren’t a good idea to replace constants.

2. Literal values and comments

The simplest solution to constants in T-SQL is to use literal values.

To make stored procedures more maintainable, it’s a good idea to write an explaining comment next to the literal value.

Let’s rewrite our stored procedure with a literal and a comment.

CREATE OR ALTER PROC dbo.usp_GetUsers
AS
BEGIN
    SELECT DisplayName, Location, CreationDate
    FROM dbo.Users u
    WHERE u.Reputation = /* Interesting reputation */2
    ORDER BY u.CreationDate DESC;
END
GO

This is the execution plan.

Do you remember the estimated number of users from our example with variables? It was 123 users. Now, we have a more accurate estimated number. It’s 1,854 users. SQL Server isn’t using an average value anymore. It has better estimates this time!

We even have an index recommendation in our execution plan. By the way, don’t blindly follow index recommendations, just listening to them. They’re only a list of columns to consider indexing.

3. Create a view for constants

The hardcoded value and an explanatory comment are OK if we have our “constant” in a few places.

A more maintainable solution to literal values is to create a single-row view with columns named after the constants to declare.

CREATE OR ALTER VIEW dbo.vw_Constant
AS
SELECT (2) InterestingReputation
GO

With that view in place, we can replace the hardcoded values in our stored procedure.

CREATE OR ALTER PROC dbo.usp_GetUsers
AS
BEGIN
    SELECT *
    FROM dbo.Users u
    /* The view with our constant */
    INNER JOIN dbo.vw_Constant c 
    ON u.Reputation = c.InterestingReputation
    ORDER BY u.CreationDate DESC;
END
GO

A more maintainable alternative while keeping good estimates.

Voilà! That’s how to use constants with a view in SQL Server. I found a proposal to introduce a constant keyword in SQL Server. I learned about the trick with views from this StackOverflow question and in this one too.

For more content on SQL Server, check my other posts on functions and WHERE clauses, implicit conversions and case-sensitive searches.

Happy coding!