TSQL: Enumations and constants

Bruce sent me a link the other day to an article T4 template for generating SQL view from C# enumeration which I found interesting from a modelling constants/enumerations in SQL viewpoint.

The example used was modelling an enumeration of ContactType which has valid items of Individual and Organisation.

The article used a view to model this, as per
CREATE VIEW enumContactType
AS
  SELECT
    0 AS Individual,
    1 AS Organization

and then using it within a SELECT as

SELECT *
FROM Contact
WHERE Type = (SELECT Organization FROM enumContactType)
(Note: in the original article Oleg used a schema called enum, but I'm just ignoring this at the moment and have thus changed the name from enum.ContactType to enumContactType)

An alternative

In my previous company, we used Scalar-Valued Functions to mimic constants, and I guess this could be extended to enumerations. I thought I'd re-create the above example and give it a try to see how it looks and compares.

So, to model the enumeration ContactType, I've created two functions as follows:
CREATE FUNCTION enumContactTypeIndividual()
RETURNS INT
AS
BEGIN
  RETURN 0
END
GO

CREATE FUNCTION enumContactTypeOrganisation()
RETURNS INT
AS
BEGIN
  RETURN 1
END
GO

And then to reproduce the SELECT query I wrote:
SELECT *
FROM Contact
WHERE Type = dbo.enumContactTypeOrganisation()

The resulting data matches that used in the VIEW model and provides an alternative. I'm sure that a template could be written to produce those functions as an output as per the end part of Oleg's article.

Performance and timings

I was interested in the relative performance of these two methods, so armed with my timing code from last week I checked them out. I amended the SELECT to bring back the COUNT(*) FROM Contact into a local integer variable, and ran it 1000000 times.

The results are as follows:

showing that the view method is more performant.

I then changed the function to make use of SCHEMABINDING. The new functions look like:
CREATE FUNCTION enumContactTypeIndividual()
RETURNS INT
WITH SCHEMABINDING
AS
BEGIN
  RETURN 0
END
GO

CREATE FUNCTION enumContactTypeOrganisation()
RETURNS INT
WITH SCHEMABINDING
AS
BEGIN
  RETURN 1
END
GO

And the timings change to be:

which do reduce the time taken for the UDF but still means that the view is faster.

For interests sake I then ran a comparison timing against the code using the literal as:
SELECT *
FROM Contact
WHERE Type = 1
which resulted in

showing it is faster, but not by much, than the view.

Summary

So, what has this shown?

• Using a view is quite efficient and effective for modelling enumerations
• Using a UDF is an alternative, but is slower
• Schema binding makes UDF usage quicker
• The difference between using a VIEW and using the hard-coded literal isn't a lot in performance terms