Enum in Sql server

Question

Hi all I have heard about the concept "Use Enum in Sql server" but dont know what it is.

I searched and didnt get any satisfactory answer.So can anyone please explain me what it is. How to use it and does it exists. If any good link is available please share with me.

Thanks in advance

---

Want to add MVP with my name.

Answer 1

Hope the below links helps you

http://stackoverflow.com/questions/1434298/sql-server-equivalent-to-mysql-enum-data-type

http://www.vbforums.com/showthread.php?634199-RESOLVED-Alternative-for-ENUM-in-SQL-Server

http://forums.asp.net/t/1161357.aspx/1

---

Please mark as helpful and propose as answer if you find this as correct!!! Thanks, Rakesh.

Answer 2

In sql server there is no datatype like mySQL enum. However you can achieve the same using CHECK constraint.

In MYSQL:

CREATE TABLE sizes (
    name ENUM('small', 'medium', 'large')
);

In SQL SERVER

CREATE TABLE sizes
(
    name varchar(10) NOT NULL CHECK (name IN('small', 'medium', 'large'))
)



I hope this helps.

Thanks.

---

Best Regards, Venkat

Answer 3

concept "Use Enum in Sql server" but dont know what it is.

Hello,

In RDBMS I don't know any kind of enum(erations) concept and I can't imagine any scenario where to use.

Maybe primary key/foreign key constraints are meant instead, as Venkat already mentioned, to ensure that data can contain only predefined values.

---

Olaf Helper
* cogito ergo sum * errare humanum est * quote erat demonstrandum *
Wenn ich denke, ist das ein Fehler und das beweise ich täglich
Blog Xing

Answer 4

What is the scenario where you want to implement the ENUM?

I don't think you can have ENUM type in TSQL, however an alternate solution would be to use a check constraint, but that is no the perfect enum implementation

Regards
Satheesh

Answer 5

Hi Naomi, Can you please show me with syntax how I can implement this in code? I mean how can I access value from 'sizes ' and comparing it.  I have a requirement like I need to pass a string in a function and based on a condition on this string I have to return a value from function. But I don't want to hard code the string value. So I want to pass ENUM like value in function and want to compare it in function.

Thanks in advance.

Answer 6

Is there any way of displaying data from this CHECK constraint in an windows application and data binding it to a, let's say, drop down list?

If it is possible can you please give an example or describe a process?

Best regards and good answer (since I used it in some other case, but I am not able to think of a way to represent this in application)

Answer 7

It is possible... I am not sure that is recomended...

Simple enums uses only one option in its value definition. Lets use MartialStatus, by example - a person can only fits in one option between 'married', 'widowed' or 'single'. In this scenario, enum values can be just an incremented value.

Here, an database check constraint with only allowed values handles the restriction to use only the enumeration values.



But in some scenarios, more than one option can be used, such as the buttons shown in System.Windows.Forms.MessageBoxButtons. The enumeration uses Yes, No or Cancel (and other values). Here, any option can be used to display just one button. Or they can be combinated to use two or three of them. If you browse the enum definition, you'll find that YesNoCancel is equal to sum the values of Yes, No and Cancel. In this implementation, enumeration values that specify option must be an power of 2 number. The numbers that is not in this rule, targets any combination of another values. 

In this last example, a check constraint becomes more complex, but I think that is sufficient uses a between 0 (minimal value) and maximum values.



In any approach, when the enumeration is recompiled, its equivalent check constraint must be recompiled, too. This is my alert about this practice can (or not) be considered recommended.