Solved

Enums in Sql-Server

Posted on 2004-11-01
659 Views
Last Modified: 2012-05-05
Hi,
   Can we have enums in sql-server? If not, is there any equivalent of enum in sql server.
I'm working on tsql and need to use enum in a particualar scenario in a stored procedure.
Thanks in Advance
0
Question by:answer_me
    4 Comments
     
    LVL 8

    Expert Comment

    by:sigmacon
    SQL server doesn't support enums directly - at least not like MySQL (that's the only place where I know about enums ...), but you can create a column as text and put a check constraint on it:

    CREATE TABLE SomeTable (
        SomeEnumColumn varchar(10) not null
            constraint CkSomeEnumColumn check (
                SomeEnumColumn in ('Value1', 'Value2', 'Value3')
            )
    )

    Hope that helps. If not, please post more specifics about what you are trying to do in the stored proc.
    0
     
    LVL 10

    Author Comment

    by:answer_me
    This is one of feasible approach i already know but i don't want to go this way. coz there is an overhead of maintaing a table and insertions for this.
    0
     
    LVL 8

    Accepted Solution

    by:
    OK, compared to

    CREATE TABLE SomeTABLE (
        ...
        SomeEnumColumn ENUM('Value1', 'Value2', 'Value3') NOT NULL
        ...
    )

    What kind of table overhead are you talking about? I may misunderstand what you are referring to with ENUM? In my example above, the column is part of whatever table you already have and does not need to be created extra:

    CREATE TABLE YourTableDefinition (
        ...
        SomeEnumColumn varchar(10) not null
            constraint CkSomeEnumColumn check (
                SomeEnumColumn in ('Value1', 'Value2', 'Value3')
            ),
        ...
    )
    0
     
    LVL 11

    Assisted Solution

    by:rdrunner
    Hello...

    I would implement an enumeration like this.. I would create one "master enum" table which contains ALL enums for ALL fields in your Database.

    The master enaum table would look like this

    ID,Value,EnumCode,Valid
    1,1,1,true
    2,2,1,true

    As an aditional feature you could create an aditional table which could hold all laguages for the same enumeration. This would allow for a good localisation support.
    ID,EnumID,Text,Language
    1,1,"Male","EN"
    2,1,"männlich","DE"
    3,2,"Female","EN"
    3,2,"weiblich","DE"

    etc..

    You can also create a function that will validate if the current code is valid.


    0

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    What Should I Do With This Threat Intelligence?

    Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

    Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
    International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
    Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
    This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

    913 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now