Enums in SQL Server

  • Simon Sabin

    SSCrazy Eights

    Points: 8142

    Comments posted here are about the content posted at http://www.sqlservercentral.com/columnists/ssabin/3174.asp


    Simon Sabin
    SQL Server MVP

    http://sqlblogcasts.com/blogs/simons

  • David Tittle

    SSC Enthusiast

    Points: 105

    Your idea is an excellent one and should be added. However, it is also one that you can accomplish right now using Sql.CLR and .Net (either VB or C#).

    I regularly use my C# enums exposed in a CLR class as PK/ID columms for tables.

    Good day,

    David

  • Jeswanth Jaishanker

    SSCommitted

    Points: 1869

    Hi,

    i feel instead of bringing one more join to sql its better to declare some variables inside the stored procedure and use it instead of bringing the enum concept forcibly in sql server.

    not sure how many will agree to this. because i feel we should not sacrifice performance to readability.

    Jeswanth

    --------------------------------

  • jonespm

    Ten Centuries

    Points: 1393

    Yep - we have been down this path and have actually achieved it using real .Net enums (as used in our app) within SQL Serevr 2005.

    We use an excellent, i.e. this is the best thing since sliced bread and cold beer!, product called StrataFrame and the use of enums within SQL Server 2005 was put to their (again excellent) support team and they came back with the following:

    http://forum.strataframe.net/Topic10664-14-1.aspx

    It took a little bit of messing around but, in the end, it works like a charm.

    Cheers, Peter

     

  • TheSQLGuru

    SSC Guru

    Points: 134017

    I will go on record as strongly disagreeing with the stuff espoused by the Enums article (and even more strongly with using CLR to implement them). Comments cost nothing to implement. Using a (var)CHAR ANYTHING is going to be less efficient (significantly as the length increases) than using a tiny/small/int as a surrogate key. I have a client that uses varchar(20)s for a lot of CLUSTERED PKs and their performance is horrid. Indexes are very inefficient too.

    Best,
    Kevin G. Boles
    SQL Server Consultant
    SQL MVP 2007-2012
    TheSQLGuru on googles mail service

  • Vic Kirkpatrick-173212

    SSCommitted

    Points: 1801

    Just use a comment next to the int based column. It's not really too hard.

  • Bill Wehnert

    Ten Centuries

    Points: 1057

    This is what I posted on the MS forum for SQL Server in response to this column:

     

    I think enums would be a great feature to add to SQL. I would think they would work best like a custom datatype.

    Something like this:

    exec sp_addenum 'Visa', 0, 'PaymentType'

    exce sp_addenum 'MasterCard', 1, 'PaymentType'

    exec sp_addenum 'Amex', 5, 'PaymentType'

    Then you would use it in a table as:

    create table Orders (

    OrderID bigint not null identity(1,1),

    CustomerID bigint not null,

    PaymentMethod PaymentType not null

    )

    The underlying datatype should be a bigint - allowable to be compared to other integer values so that they could be passed in from other sources.

    Then in queries like suggested:

    select * from Orders where PaymentMethod in (PaymentType.Visa, PaymentType.MasterCard)

    The question would be is something like this allowed?

    select * from Orders where PaymentMethod = Shipping.UPS

    I think it should be prohibited. Either a constant (PaymentMethod = 1) or a valid enum type (PaymentMethod = PaymentType.Visa) or a variable (PaymentMethod = @PaymentType) should be accepted.

    This would hopefully at least prevent people from writing the PaymentMethod = Shipping.UPS code.

    Ad maiorem Dei gloriam

  • TheSQLGuru

    SSC Guru

    Points: 134017

    Again a bad example from a performance perspective.

    1) Using a bigint vice tinyint in this case requires 7 extra bytes of storage and 7 extra bytes to chew through on row accessed. And yes, those 7 bytes DO make a difference!

    2) Prohibiting improper enum usage would require under-the-covers system validation, also slowing performance for every query that references an enum. Once again a developer 'convenience' equals reduced throughput. Consider also what happens if you change an enum or change which enum a table references.

    Microsoft has for many years gotten a bad name in the server business (especially database) for sub-optimal performance. A lot of that came because they made things REALLY, REALLY EASY for developers. RAD this, point-and-click program that. Like parameter refresh, etc. But easy for developers quite often turns out to be a performance DOG when any reasonable amount of data is thrown at the application.

    I guess I shouldn't complain too vigorously however, since I make a comfortable living fixing other people's bad designs, bad data structures and bad code!! :-))

    Best,
    Kevin G. Boles
    SQL Server Consultant
    SQL MVP 2007-2012
    TheSQLGuru on googles mail service

  • Henrik Staun Poulsen

    SSCertifiable

    Points: 6410

    I have a small "Attribute-Value" table, and a UDF to go with it.

    Then I can do SELECT * FROM Orders where PaymentType = dbo.fn_SystemInfo('VISA'). Unfortunately this results in a convertion to/from varchar which may not be efficient. But it is easy on the eye to read the code.

    Your suggestion would help a lot.

    Best regards

    Henrik Staun Poulsen

  • Jeswanth Jaishanker

    SSCommitted

    Points: 1869

    the stress should be on performance and related topics than readability. for readability use comments in code. something like

    SELECT fld1,fld2 FROM Orders where PaymentType = 1 -- VISA

    Regards

    Jeswanth

    --------------------------------

  • TheSQLGuru

    SSC Guru

    Points: 134017

    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

    SSCrazy

    Points: 2029

    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

    SSC-Dedicated

    Points: 34251

    Isn't it called Dictionary Objects?

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

    Dictionary Objects

    Regards,Yelena Varsha

  • TheSQLGuru

    SSC Guru

    Points: 134017

    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

    Old Hand

    Points: 350

    Please fix the print style sheet so the text is not truncated.

Viewing 15 posts - 1 through 15 (of 20 total)

You must be logged in to reply to this topic. Login to reply