SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Enums in SQL Server


Enums in SQL Server

Author
Message
TheSQLGuru
TheSQLGuru
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16836 Visits: 8600
IIRC, this is one of the worst things you can do with a UDF. It essentially equates to RBAR (Row By Agonizing Row) processing under the covers - as in a lookup for each row streaming through the resultset.

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
James Rochez
James Rochez
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1559 Visits: 824
Comments are not necessarily free. They can be costly at maintenance time. There are many occasions when the code is changed and the comments are not...especially when the changes are not done by the original developer. An enum forces the code to use a value from a limited set and still allows for good readability.

An enum does cost more at compile time but is translated to an integer value at runtime. I would think that if an enum was used in a stored procedure that is compiled once, the performance penalty of translating a character string to an integer value at compile time would be negligable.

An enumeration also acts as another integrity test through type checking. This, IMHO, is more valuable than the small performance gains from using any type of integer.



Yelena Varshal
Yelena Varshal
SSCertifiable
SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)

Group: General Forum Members
Points: 5546 Visits: 597

Isn't it called Dictionary Objects?

http://www.microsoft.com/technet/scriptcenter/scripts/misc/diction/default.mspx?mfr=true

Dictionary Objects




Regards,
Yelena Varshal

TheSQLGuru
TheSQLGuru
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16836 Visits: 8600
1) >>Comments are not necessarily free. They can be costly at maintenance time. There are many occasions when the code is changed and the comments are not...especially when the changes are not done by the original developer. An enum forces the code to use a value from a limited set and still allows for good readability.

2) >>An enum does cost more at compile time but is translated to an integer value at runtime. I would think that if an enum was used in a stored procedure that is compiled once, the performance penalty of translating a character string to an integer value at compile time would be negligable.

3) >>An enumeration also acts as another integrity test through type checking. This, IMHO, is more valuable than the small performance gains from using any type of integer.

**********************
for Item 1 above, comments are most useful when they are made by OTHER THAN the original developer. In any case, if developers don't adhere to good commenting then they need to be upgraded.

for Item 2 above, which particular method of enumeration are you espousing here. There have been several given in this thread and on the original web posting.

for Item 3 above, your comment is invalid in so much as you can type the wrong value in just like you can type the wrong value in when you do the lookup to get it.

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Ian Ornstein
Ian Ornstein
SSC Rookie
SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)

Group: General Forum Members
Points: 48 Visits: 74
Please fix the print style sheet so the text is not truncated.
cs_troyk
cs_troyk
SSCommitted
SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)

Group: General Forum Members
Points: 1815 Visits: 970

Although an admirable effort, the author misses the mark.

I would urge readers to, instead, push the folks at Microsoft to implement proper support for relational domains. This is the superior and theoretically sound solution.

TroyK





Michael Valentine Jones
Michael Valentine Jones
SSCertifiable
SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)

Group: General Forum Members
Points: 7488 Visits: 11793

I don’t see any point to the idea of ENUMS; basically, it solves a problem that doesn’t exist.

There is nothing wrong with any of the techniques in the code below, other than the author not liking to write it.

I will say that you should never use a hard coded ID value; that defeats the whole purpose of a surrogate key by hard-coding the meaning of a particular ID.

--Method 1

select

Order.OrderId,

Order.OrderDate,

Order.PaymentTypeId

from

Order

join

PaymentType

on Order.PaymentTypeId = PaymentType.PaymentTypeId

where

PaymentTypeDescription = 'Invoice'

or

--Method 2

select

Order.OrderId,

Order.OrderDate,

Order.PaymentTypeId

from

Order

where

Order.PaymentTypeId in (

select

PaymentType.PaymentTypeId

from

PaymentType

where

PaymentTypeDescription = 'Invoice'

)

or

--Method 3

declare @PaymentTypeId int

select

@PaymentTypeId = PaymentType.PaymentTypeId

from

PaymentType

where

PaymentTypeDescription = 'Invoice'

select

Order.OrderId,

Order.OrderDate,

Order.PaymentTypeId

from

Order

where

Order.PaymentTypeId = @PaymentTypeId


Merrill Aldrich
Merrill Aldrich
SSC Veteran
SSC Veteran (246 reputation)SSC Veteran (246 reputation)SSC Veteran (246 reputation)SSC Veteran (246 reputation)SSC Veteran (246 reputation)SSC Veteran (246 reputation)SSC Veteran (246 reputation)SSC Veteran (246 reputation)

Group: General Forum Members
Points: 246 Visits: 137
This, like many articles, seems written by a person who hasn't read much of the existing thought on the topic, and is therefore blindly bumping into old issues that have already been covered in much more detail elsewhere. Read Celko, read Date, read about all the reasons for the surrogate key optimization, then come back and write something if there's more to say than has been said already.

FWIW: surrogate keys are both evil, and necessary for scalability. Unless/until the database vendors come up with an internal engine optimization that avoids this problem (the issue of the width of keys / the cost to compare them / the cost of On Update Cascade), an issue which seems still to be a blind spot for them, we will have surrogate keys with all the extra joins, logic problems and headaches they create.
TheSQLGuru
TheSQLGuru
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16836 Visits: 8600
It is obviously physically impossible to process many if not most 'natural keys' as efficiently as surrogate ones, therefore your pining about this 'evil' is to no avail. At least you ACKNOWLEDGE the need for surrogate keys - something Celko in his ivory tower still doesn't do. Espousing a 23 character key when a tinyint will suffice as a surrogate is simply ridiculous.

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Tom Wilson
Tom Wilson
SSC Journeyman
SSC Journeyman (94 reputation)SSC Journeyman (94 reputation)SSC Journeyman (94 reputation)SSC Journeyman (94 reputation)SSC Journeyman (94 reputation)SSC Journeyman (94 reputation)SSC Journeyman (94 reputation)SSC Journeyman (94 reputation)

Group: General Forum Members
Points: 94 Visits: 162

In the SQL 2005 world, I've wondered (but not implemented) code using the SQLCMD metacommand :setvar and :r. One could use these like #define and #include in C++.

File1.sql
#setvar enumMale M

#setvar enumFemale F

File2.sql:

:r file1.sql

select Name from Person where Sex = '$(enumMale)'

The above is contrived and simplistic, but you get the idea - rather than going for enum, just go for symbolic substitution.


Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search