What are the different ways of handling 'Enumerations' in SQL server?

Question

We currently define a list of constants (mostly these correspond to enumerations we have defined in the business layer) at the top of a stored procedure like so:

DECLARE @COLOR_RED INT = 1
DECLARE @COLOR_GREEN INT = 2
DECLARE @COLOR_BLUE INT = 3

But these often get repeated for many stored procedures so there is a lot of duplication.

Another technique I use if the procedure needs just one or two constants is to pass them in as parameters to the stored procedure. (using the same convention of upper case for constant values). This way I'm sure the values in the business layer and data layer are consistent. This method is not nice for lots of values.

What are my other options?

I'm using SQL Server 2008, and C# if it makes any difference.

Update Because I'm using .Net is there any way that user defined (CLR) types can help?

Answer 1

I can suggest two different approaches:

1) Define an Enumeration table with a tinyint identity column as the primary key and the enum value as a unique index; e.g.

CREATE TABLE [dbo].[Market](
        [MarketId] [smallint] IDENTITY(1,1) NOT NULL,
        [MarketName] [varchar](32) COLLATE Latin1_General_CS_AS NOT NULL,
 CONSTRAINT [PK_Market] PRIMARY KEY CLUSTERED
(
        [MarketId] ASC
) ON [PRIMARY]
) ON [PRIMARY]

Then either:

• Have your application load the enumeration to primary key value mapping on start-up (assuming this will remain constant).
• Define a function to translate enumeration values to primary key values. This function can then be used by stored procs inserting data into other tables in order to determine the foreign key to the enumeration table.

2) As per (1) but define each primary key value to be a power of 2. This allows another table to reference multiple enumeration values directly without the need for an additional association table. For example, suppose you define a Colour enumeration table with values: {1, 'Red'}, {2, 'Blue'}, {4, 'Green'}. Another table could reference Red and Green values by including the foreign key 5 (i.e. the bit-wise OR of 1 and 4).

Answer 2

Scalar user define function? Not perfect, but functional...

CREATE FUNCTION dbo.ufnRGB (
    @Colour varchar(20)
)
RETURNS int
AS
BEGIN
    DECLARE @key int

    IF @Colour = 'BLue'
        SET @key = 1 
    ELSE IF @Colour = 'Red'
        SET @key = 2
    ELSE IF @Colour = 'Green'
        SET @key = 3 

    RETURN @KEy
END

Answer 3

I don't like the idea of defining what are effectively constants for stored procedures in multiple places - this seems like a maintenance nightmare and is easily susceptible to errors (typos etc). In fact, I can't really see many circumstances when you would need to do such a thing?

I would definitely keep all enumeration definitions in one place - in your C# classes. If that means having to pass them in to your procedures every time, so be it. At least that way they are only ever defined in one place.

To make this easier you could write some helper methods for calling your procedures that automatically pass the enum parameters in for you. So you call a helper method with just the procedure name and the "variable" parameters and then the helper method adds the rest of the enumeration parameters for you.

Answer 4

This might be controversial: my take is don't use enumerations in T-SQL. T-SQL isn't really designed in a way that makes enums useful, the way they are in other languages. To me, in T_SQL, they just add effort and complexity without the benefit seen elsewhere.

Answer 5

How about using a scalar function as a constant. A naming convention would make their usage close to enumerations:

CREATE FUNCTION COLOR_RED()  
RETURNS INT  
AS  
BEGIN  
    RETURN 2  
END