Enums in SQL Server

  • 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

  • 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

     

     

     

     

  • 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.

  • 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

  • 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.

Viewing 5 posts - 16 through 20 (of 20 total)

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