sql server - Best pattern for Constants in SQL?

Question

I have seen several patterns used to 'overcome' the lack of constants in SQL Server, but none of them seem to satisfy both performance and readability / maintainability concerns.

In the below example, assuming that we have an integral 'status' classification on our table, the options seem to be:

• Just to hard code it, and possibly just 'comment' the status

-- StatusId 87 = Loaded
SELECT ... FROM [Table] WHERE StatusId = 87;

• Using a lookup table for states, and then joining to this table so that the WHERE clause references the friendly name.

SubQuery:

SELECT ... 
FROM [Table] 
WHERE 
  StatusId = (SELECT StatusId FROM TableStatus WHERE StatusName = 'Loaded');

or joined

SELECT ... 
FROM [Table] t INNER JOIN TableStatus ts On t.StatusId = ts.StatusId 
WHERE ts.StatusName = 'Loaded';

• A bunch of scalar UDF's defined which return constants, viz

CREATE Function LoadedStatus()
RETURNS INT
AS
 BEGIN
  RETURN 87
 END;

and then

SELECT ... FROM [Table] WHERE StatusId = LoadedStatus();

(IMO this causes a lot of pollution in the database - this might be OK in an Oracle package wrapper)

• And similar patterns with Table Valued Functions holding the constants with values as rows or columns, which are CROSS APPLIED back to [Table]

How have other SO users have solved this common issue?

Edit : Bounty - Does anyone have a best practice method for maintaining $(variables) in DBProj DDL / Schema scripts as per Remus answer and comment?

Answer 1

Hard coded. With SQL performance trumps maintainability.

The consequences in the execution plan between using a constant that the optimizer can inspect at plan generation time vs. using any form of indirection (UDF, JOIN, sub-query) are often dramatic. SQL 'compilation' is an extraordinary process (in the sense that is not 'ordinary' like say IL code generation) in as the result is determined not only by the language construct being compiled (ie. the actual text of the query) but also by the data schema (existing indexes) and actual data in those indexes (statistics). When a hard coded value is used, the optimizer can give a better plan because it can actually check the value against the index statistics and get an estimate of the result.

Another consideration is that a SQL application is not code only, but by a large margin is code and data. 'Refactoring' a SQL program is ... different. Where in a C# program one can change a constant or enum, recompile and happily run the application, in SQL one cannot do so because the value is likely present in millions of records in the database and changing the constant value implies also changing GBs of data, often online while new operations occur.

Just because the value is hard-coded in the queries and procedures seen by the server does not necessarily mean the value has to be hard coded in the original project source code. There are various code generation tools that can take care of this. Consider something as trivial as leveraging the sqlcmd scripting variables:

defines.sql:

:setvar STATUS_LOADED 87

somesource.sql:

:r defines.sql
SELECT ... FROM [Table] WHERE StatusId = $(STATUS_LOADED);

someothersource.sql:

:r defines.sql
UPDATE [Table] SET StatusId = $(STATUS_LOADED) WHERE ...;