Constants in MS SQL Server

Even though TSQL does not allow constant declarations per se, there are several ways to handle constant values in SQL Server databases.

The simplest one is to document special values (apart from keeping a list on a piece of paper which drowns in the sediments on your desk) is to list them in the Description property of a column, although this is not what databases are made for.

Next is a check constraint on columns which only allow specified values and cause INSERT/UPDATE statements to fail if the new value does not match the constraint.

My favorite method is to have a table of constant values with a numeric identifier, a programmatic identifier for use in a programming language, and a natural-language string to be displayed to the user.

My application dbscript supports this method and generates code to link C# code to the values stored in the database. I described this mechanism earlier here and here.

As an example, take a simple table describing command execution states:

ID Name ProgID
10 Command Pending Pending
20 Command Running Running
30 Command Done Done

This table can be represented in C# as

public class ExecutionState
    public const int Pending = 10;
    public const int Running = 20;
    public const int Done = 30;

One disadvantage remains though: if you want to use the constant value in TSQL (for example, in a stored procedure), you still have to know the numeric value, or retrieve the numeric value by its programmatic identifier. In both cases, there is no way to check automatically that the values or identifiers actually exist.

Yet another method came to my mind recently: create a single-row view with the column names representing the constant identifiers and the fields containing the constant values:

CREATE VIEW V_Execution_State AS
SELECT 10 AS Pending, 20 AS Running, 30 AS Done

This way, constant identifiers can be checked by TSQL, and values easily assigned, like this:

SELECT @state = Pending FROM V_Execution_State

By the way, this is post # 200😉

One Response to Constants in MS SQL Server

  1. […] 3 years ago I described how to store application constants in SQL Server views so that T-SQL code can be checked against these constants be referring view […]

Leave a Reply

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

You are commenting using your 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

%d bloggers like this: