Jane's Technical Stuff
Archives
May 2002March 2003
May 2003
July 2003
August 2003
June 2005
July 2005
August 2005
December 2005
January 2006
May 2006
June 2006
July 2006
August 2006
September 2006
October 2006
November 2006
December 2006
January 2007
February 2007
March 2007
April 2007
May 2007
June 2007
July 2007
August 2007
September 2007
October 2007
November 2007
December 2007
January 2008
February 2008
March 2008
April 2008
May 2008
June 2008
July 2008
August 2008
September 2008
October 2008
Friday, July 18, 2008
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)
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.
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.
So, what has this shown?
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:
| Code | Description | TimeInMS |
| EnumView | Using the view | 13010 |
| EnumUDF | Using the UDF | 21450 |
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:
| Code | Description | TimeInMS |
| EnumView | Using the view | 13010 |
| EnumUDF | Using the UDF | 20280 |
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
| Code | Description | TimeInMS |
| EnumLiteral | Using the literal | 12043 |
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 perfomance terms
Labels: SQL, SQLServer2005
// posted by Jane @ 1:24 PM 
Comments:
