Managing / Using Constant Values in SQL Server

Question

Fixed or Seldom-changing values in SQL - How to best code them?

Writing some SQL code yesterday, I was about to type some fixed constant values (sarcastically often called "magic numbers") in to my code. Every time I do that, I feel a pang of guilt. It's like knowingly creating technical debt, as you just know that you're feeding the future questions of "How many places does StatusX = 6 matter in our system?" / "Where might the system break or need to change if we added in another status value?".

Answering this "where-used" question is always a scavenger hunt, as sometimes fixed values are used directly (StatusX =6), or sometimes in IN clauses Status X IN (2,6,7), or sometimes in ranges StatusX < 7. You can do a pretty good job finding them, but it's time consuming. There HAS to be a better way...

Solution: Fixed Value Views

I went in search of this and found two articles in particular (see links below - one of them is here, on StackOverflow) proposing using SQL Views with fixed values for this. At first I passed the idea by. It didn't click with me right away, but after taking a look at what approaches there are on offer and their drawbacks, and then finding this concept a few more times, I re-visited it. Upon closer review, I find this approach makes a LOT of sense and has a ton of benefits, and costs next to nothing performance-wise.

The scenario where this approach applies best is where one has a fixed set of values, usually stored in a codes table. These values often have foreign key constraints on them to make sure that dependent data structures are only using valid and/or active codes values. Traditional RI has the validity of the data covered, but it doesn't make it any easier to manage or write SQL code that uses the code values sitting in these mostly static tables. That's where these fixed-value views come in!

I'm reposting this idea here, as I think it has a LOT of merit, and could or should be used by many. Glad to share and hear of any improvements or variations on it. I also added a bit (literally - see below) to the idea that improves on its code-friendliness.

Benefits

Using this method, you can use SQL server's dependency information to detect where your fixed values exposed by these fixed value views have been utilized. If you ever actually needed to add a value, it's easy and you quickly know where you should check for usage. If you wanted to gasp remove a value, you could tell what code it would break, as the view would be modified. All in all, this idea is very appealing to me. My brain isn't left feeling a bit dirty inside every time I code a fixed value using this method.

This approach does not suggest that you not have actual codes tables, or use these views instead of them. As much as possible, it is best to have defined codes tables, with referential integrity enforced to them from tables that use them. Where the constant views discussed below really shine is in SQL coding : in functions, stored procedures, etc.

Example View

CREATE VIEW dbo.cvw_OrderStatusID AS 
SELECT 
 CAST(1 AS bit) AS [Match Exists]   <-- Helper column for Left Outer Joins to check for record presence.
,CAST(1 AS INT) AS [Draft]
,CAST(2 AS INT) AS [New]
,CAST(3 AS INT) AS [Open]
,CAST(4 AS INT) AS [Delivered]

Basic Usage

Here's an example query that looks for Orders that are in a status that should still allow them to be edited:

-- Use Inner Join to select 1 to n values
SELECT
  O.OrderNumber,
  O.CustomerNumber
FROM
dbo.Order O
  INNER JOIN dbo.cvw_OrderStatusID OS_AllowEdit ON O.OrderStatusID 
  IN (OS_AllowEdit.[Draft], OS_AllowEdit.[New], OS_AllowEdit.[Open])

-- Use Cross Apply to pass a fixed value to a TVF for example:
SELECT orders.* FROM dbo.cvw_OrderTypeID OrderTypes
CROSS APPLY dbo.fn_GetOrders(OrderTypes.[Express], @this ,@that ,@theother) orders
ORDER BY orders.DateSold

Small improvement on the idea - [Match Exists]

To make it more evident or easy to test when you are checking against multiple values for a match, add a field called [Match Exists] to your Constant Views of type Bit Default 1 (true).

When you use your SQL Constant Views, you can use an INNER JOIN where data must match, or a LEFT OUTER JOIN where data may or may not match X different values, structure your SQL like this:

SELECT
  IIF(OS_OrderDelivered.[Match Exists] = 1, p.DeliveredDate, NULL)  AS OrderDeliveryDate,
  ISNULL(OS_AllowEdit.[Match Exists], 0) AS AllowEditing
FROM
dbo.Order O
  LEFT OUTER JOIN dbo.cvw_OrderStatusID OS_OrderDelivered ON O.OrderStatusID = OS_OrderDelivered.[Delivered]
  LEFT OUTER JOIN dbo.cvw_OrderStatusID OS_AllowEdit ON O.OrderStatusID 
  IN (OS_AllowEdit.[Draft], OS_AllowEdit.[New], OS_AllowEdit.[Open])

Giving credit where it is due:

For the full treatment, read these blogs, as they went deeper into performance considerations.

https://learn.microsoft.com/en-us/archive/blogs/sql_server_appendix_z/sql-server-variables-parameters-or-literals-or-constants

https://www.red-gate.com/hub/product-learning/sql-prompt/misuse-scalar-user-defined-function-constant-pe017