SQL server 2008: what is the best defined type in SQL database table for Enum type?

Question

We have general questions regarding SQL database table type definition for Enum type:
1) Should we use enum value (0, 1, 2, ...) or enum strings (shape1, shape2, ...) in Enum type column?
2) if the first answer is enum value, should we use smallint type or tinyint?
thx!

Answer 1

Unfortunately there is no ENUM in MSSQL. Use Rule or a Check Constraint instead.


---
Paul Svirin
StarWind Software developer ( http://www.starwindsoftware.com )

Answer 2

Paul,
thanks for replying.

We know SQL server does not support enum type yet.
Our question is what is the best practise in general to define enum type in SQL table.
Should we use enum value or enum string?

If using enum value, actually enum value can go from small integer range to large integer range depending on how you define
enum value

enum ShapeEnum
{
    Shape1 = 0,
    Shape2 = 1,
    ...
    Shape5= 1000
}

if we define Shape5 value as 1000, then use tinyint (8 bits) is not enough to represent it.

Answer 3

Hi,

I think it is a good option using key-value pair to store enum type data. If tingint is not enough to store number, can you use int/bigint, if the value is not stand for a number, we can use char/varchar to store the value.

Hope this helps.

---

ChunSong Feng
Microsoft Online Community Support

Please remember to mark the replies as answers if they help and unmark them if they provide no help.
Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.