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('') SELECT addr.Line1, addr.Line2, addr.City, addr.State, addr.Zip FROM Addresses addr WHERE addr.AddressType = @Constants.GetValue('Address.Work') AND addr.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') SELECT addr.Line1, addr.Line2, addr.City, addr.State, addr.Zip FROM Addresses addr WHERE addr.AddressType = @AddressConstants.GetValue('Work') AND addr.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 ( SELECT 1 FROM sys.objects so WHERE so.object_id = OBJECT_ID(N'[dbo].[GetConstants]') AND so.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 ( @Enum VARCHAR(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 ( SELECT 1 FROM sys.objects so WHERE so.object_id = OBJECT_ID(N'[dbo].[Constants]') AND so.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 ( SELECT 1 FROM sys.objects so WHERE so.object_id = OBJECT_ID(N'[dbo].[Constants]') AND so.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
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.
Copyright © September, 2007 by Solomon Rutzky