A New (and Hopefully Better) Approach to Constants

Constant Struggle

While Microsoft SQL Server continues to evolve and improve, one piece of functionality still missing is that of constants. They are not always needed, but quite often it is handy to use the same constants in the database programming as are being used in the application code; and if constants are being used in the application code to avoid the hard-coding of values, then it certainly doesn't help if they are being hard-coded in the database code.

Several people have suggested various different means of simulating constants. One decent approach is to create a Table to hold the constants, and User-Defined Function to retrieve the constant values. This approach can be implemented by placing the User-Defined Function in each place that you would want the constant. The only real problem with this approach is that you have a lot of disk access as SQL Server might have to repetitively get the same value over and over again. The solution is then to declare variables to store the needed values so the User-Defined Function is only called once per each constant name. This solution works just fine (and I have used it myself when using SQL Server 2000), but the more constants you have the more variables you have to declare and it could (and sometimes does) get a bit messy and/or annoying managing variables that really aren't part of the logic you are coding. Might there not be a better way?

A New Hope

Why yes, there is a better way. Thanks for asking :). While the solution about to be mentioned only works with SQL Server 2005 and beyond, hopefully that is a reality for most of us already, or it will be soon enough since SQL Server 2000 won't be supported forever and we aren't too far away from SQL Server 2008.

The proposed solution relies upon the .Net CLR that is built into SQL Server as of SQL Server 2005 and the ability to create User-Defined Types with it (and hence why this will unfortunately not work with SQL Server 2000). We will still be creating a regular T-SQL User-Defined Function to retrieve the constant values, but whether or not you get those values from a table is up to you (as I suppose it also was in the typical approach). I will show both methods here and you can choose which is best for you.

First, in order to implement this solution, you will need to get (and install) a copy of SQL# (SQLsharp) by downloading it from the SQL# website at: http://www.SQLsharp.com/ (the free version is all you need for this). SQL# is a .Net CLR-based library of over 65 functions plus some User-Defined Aggregates and User-Defined Types. For this solution we will be using the Type_HashTable User-Defined Type which allows you to create a variable of name / value pairs (known in .Net languages as a HashTable). Please note that while the "value" datatype returned by Type_HashTable is an NVARCHAR(4000), NVARCHAR will implicitly convert to any numeric datatype, or you can explicitly do a CAST or CONVERT if you prefer. But this does give the flexibility of doing both string and numeric constants.

Before we get into the real guts of getting the constants we first need to deal with the overall structure of a typical set of constants. In most cases constants are divided into groups or categories or some logical division of meanings. So, let's start by assuming that the theoretical application we are dealing with tracks address information for customers. Two pieces of the address information we are storing are: AddressType and Region. AddressType can be: Home, Work, or Shipping. Region can be: Eastern, Central, Mountain, and Pacific.

So now that we have the two groupings of AddressType and Region, how do we really want to distinguish them? There are two main choices: 1) Create a field to store, and filter on, the category (e.g. Category = "Region", Constant = "Eastern"); and 2) Use dot-notation with the constant name itself (e.g. no Category, Constant = "Region.Eastern"). Now, relational-theory would have us choose option #1 without a second thought (and some people would go so far as to put Category in its own table and put a CategoryId in the Constants table--assuming of course that we are creating a table for this--but that would be getting farther away from our goal which has little to do with normalization).

The main goal is to simplify our lives as database programmers (while not doing anything insane) and to that end I advocate the use of the dot-notation within a single Constant field. While this does not have a truly clean separation of Category and Constant, it does give us the ability to use a set of constants of any size with only a single variable in the T-SQL code (and after all, this data is a programmatic device for database programming and is not data that the application needs to manage or query). For me that is a huge plus, but if you do want to go the route of using a separate field to indicate the category then you will have only one variable per category, which is still better than doing it by the standard approach which requires one variable per constant used.

We will start out by looking at how the end-result works so you can see the two ways of dealing with Categories to help you determine which way is best for you. Please note that from this point forward I will refer to Categories as "Enum"s which is short for "Enumeration"s and which is the .Net structure for containing sets of numeric constants.

/* Using dot-notation (single field contains
    enum.constant) so we only have one variable */
DECLARE @Constants dbo.Type_HashTable
SET @Constants = dbo.GetConstants('')
SELECTaddr.Line1, addr.Line2, addr.City, addr.State,
addr.Zip
FROMAddresses addr
WHEREaddr.AddressType =
@Constants.GetValue('Address.Work')
ANDaddr.Region =
@Constants.GetValue('Region.Central')
--- OR ---
/* Using different fields for enum and constant so we
    have one variable per enum */
DECLARE @AddressConstants dbo.Type_HashTable
DECLARE @RegionConstants dbo.Type_HashTable
SET @AddressConstants = dbo.GetConstants('Address')
SET @RegionConstants = dbo.GetConstants('Region')
SELECTaddr.Line1, addr.Line2, addr.City, addr.State,
addr.Zip
FROMAddresses addr
WHEREaddr.AddressType =
@AddressConstants.GetValue('Work')
ANDaddr.Region =
@RegionConstants.GetValue('Central')

The beauty of this solution is that there is either only one variable (@Constants in the top example) or at most one variable per enum (@AddressConstants and @RegionConstants in the bottom example). So the number of variables is kept to a minimum and the disk access is also kept to a minimum, especially in the top example with only one variable and hence only one call to dbo.GetConstants.

To Table, or Not To Table?

Now that we have seen the end-result of how this works, the next question to answer is how to best store and manage the constants. Putting the values in a table is definitely a more dynamic means of storing the information since it requires no update to the code of the User-Defined Function (and is arguably the proper use of the database). However, it also requires disk access every time a piece of code (Procedure, Function, Trigger, or View) needs to get the constant value on top of reading the definition of the function to get the constant value which it has to do in either case. But I guess if the Constants table is selected from enough (and in most cases it would be) then the results will be cached anyway so it won't be any slower than just putting the values into the User-Defined Function. Either way, you can see for yourself below how the function will look in either case as the GetConstants function below has both methods shown in it.

IF EXISTS (
SELECT1
FROMsys.objects so
WHEREso.object_id =
     OBJECT_ID(N'[dbo].[GetConstants]')
ANDso.type IN
     (N'FN', N'IF', N'TF', N'FS', N'FT')
)
DROP FUNCTION [dbo].[GetConstants]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION GetConstants (
@EnumVARCHAR(100)
)
RETURNS NVARCHAR(4000)
AS
BEGIN
  DECLARE @Constants SQL#.Type_HashTable
  SET @Constants = '' /* it must be initialized before
calling .AddData() */

  /* Use the @Enum input parameter to separate constants
      into groups.  The ownside is that you will have to
      have one variable per group when retrieving. */
  IF (@Enum = 'Address')
  BEGIN
    SET @Constants = @Constants.AddData('Home=1')
    SET @Constants = @Constants.AddData('Work=2')
    SET @Constants = @Constants.AddData('Shipping=3')
  END
  IF (@Enum = 'Region')
  BEGIN
    SET @Constants = @Constants.AddData('Eastern=1')
    SET @Constants = @Constants.AddData('Central=2')
    SET @Constants = @Constants.AddData('Mountain=3')
    SET @Constants = @Constants.AddData('Pacific=4')
  END
  IF (@Enum = 'CC')
  BEGIN
    SET @Constants = @Constants.AddData('MasterCard=1')
    SET @Constants = @Constants.AddData('Visa=2')
    SET @Constants = @Constants.AddData('Amex=3')
    SET @Constants = @Constants.AddData('Discover=4')
  END
  /* put all constants into the same group using a
      period (.) to separate */
  IF (@Enum = '')
  BEGIN
    SET @Constants = @Constants.AddData('Address.Home=1')
    SET @Constants = @Constants.AddData('Address.Work=2')
    SET @Constants = @Constants.AddData('Address.Shipping=3')
    SET @Constants = @Constants.AddData('Region.Eastern=1')
    SET @Constants = @Constants.AddData('Region.Central=2')
    SET @Constants = @Constants.AddData('Region.Mountain=3')
    SET @Constants = @Constants.AddData('Region.Pacific=4')
    SET @Constants = @Constants.AddData('CC.MasterCard=1')
    SET @Constants = @Constants.AddData('CC.Visa=2')
    SET @Constants = @Constants.AddData('CC.Amex=3')
    SET @Constants = @Constants.AddData('CC.Discover=4')
  END

  /* you can even load the data from a table; a table is
      a little more dynamic but requires more disk
      access but doesn't require updating this function
      each time you want to make a change */
  SELECT  @Constants =
    @Constants.AddData(const.[Key] + '=' + const.[Value])
  FROM  Constants const
  WHERE   Enum = @Enum /* this is not needed if all in
                           one group using periods (.) */

  /* do not return the native datatype of Type_HashTable
      since it will impede updating SQL# in the future */
  RETURN @Constants.ToString()
END
GO

Now that you have seen the options for putting the data in the function itself or in a table (the more typical choice), if you decide to put the Constants data in a table then choose from one of these two options.

If you are going to put the Enum (or category) in the Key field using the dot-notation (e.g. 'Region.Eastern'), then use the following SQL:

IF EXISTS (
SELECT1
FROMsys.objects so
WHEREso.object_id =
OBJECT_ID(N'[dbo].[Constants]')
ANDso.type IN (N'U')
)
DROP TABLE [dbo].[Constants]
GO
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Constants] (
[Key] [NVARCHAR](50) NOT NULL,
[Value] [NVARCHAR](50) NULL
) ON [PRIMARY]
GO
ALTER TABLE dbo.Constants ADD CONSTRAINT PK_Constants
PRIMARY KEY CLUSTERED ([Key])
GO

Or, if you want to keep the Enum's as a separate field, then use this SQL:

IF EXISTS (
SELECT1
FROMsys.objects so
WHEREso.object_id =
OBJECT_ID(N'[dbo].[Constants]')
ANDso.type IN (N'U')
)
DROP TABLE [dbo].[Constants]
GO
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Constants] (
[Enum] [VARCHAR](50) NOT NULL,
[Key] [NVARCHAR](50) NOT NULL,
[Value] [NVARCHAR](50) NULL
) ON [PRIMARY]
GO
ALTER TABLE dbo.Constants ADD CONSTRAINT PK_Constants
PRIMARY KEY CLUSTERED ([Enum], [Key])
GO

Conclusion

Hopefully this method provides a more workable solution to the Constant problem. Of course, a lot more can be done with the Type_HashTable User-Defined Type (as well as the other Types, Procs, and Functions in the SQL# library), but this is a good, practical start to show how the .Net CLR integration starting in SQL Server 2005 can help make our lives easier.