Best pattern for State Constants in SQL Server + DBProj?

Question

Hi

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:

1) Just hard code it, and possibly just 'comment' the status

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

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

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

OR

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

3) And most recently have seen a 'fleet' 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()

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

I have been using the scalar function option in our DB and it's work fine and as per my view is the best way of this solution.

if more values related to one item then made lookup like if you load combobox or any other control with static value then use lookup that's the best way to do this.

Answer 2

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 ...;

Answer 3

You can also add more fields to your status table that act as unique markers or groupers for status values. For example, if you add an isLoaded field to your status table, record 87 could be the only one with the field's value set, and you can test for the value of the isLoaded field instead of the hard-coded 87 or status description.

Answer 4

While I agree with Remus Rusanu, IMO, maintainability of the code (and thus readability, least astonishment etc.) trump other concerns unless the performance difference is sufficiently significant as to warrant doing otherwise. Thus, the following query loses on readability:

Select ..
From Table
Where StatusId = 87

In general, when I have system dependent values which will be referenced in code (perhaps mimicked in an enumeration by name), I use string primary keys for the tables in which they are kept. Contrast this to user-changeable data in which I generally use surrogate keys. The use of a primary key that requires entry helps (albeit not perfectly) to indicate to other developers that this value is not meant to be arbitrary.

Thus, my "Status" table would look like:

Create Table Status
(
    Code varchar(6) Not Null Primary Key
    , ...
)
Select ...
From Table
Where StatusCode = 'Loaded'

This makes the query more readable, it does not require a join to the Status table, and does not require the use of a magic number (or guid). Using user-defined functions, IMO is a bad practice. Beyond the performance implications, no developer would ever expect UDFs to be used in this manner and thus violates the least astonishment criteria. You would almost be compelled to have a UDF for each constant value; otherwise, what you are passing into the function: a name? a magic value? If a name, you might as well keep the name in a table and use it directly in the query. If a magic value, you are back the original problem.