How do I implement enum data type?

Hi.

I'm coming from an environment where I can have a column which can have enum columns.

I don't think MSSQL has this column type.

How best can I implement this?

Regards,

Richard Quadling.
LVL 40
Richard QuadlingSenior Software DeveloperAsked:
Who is Participating?
 
ShogunWadeConnect With a Mentor Commented:
Sounds to me like a standard lookup table with 3 values.
0
 
ShogunWadeCommented:
An enum in a virtual type its not real.  

Most enums are implemented as integer datatypes.

The SQL equivelant is a Table  
eg:  CREATE TABLE (MyLookupID int,MyLookupDescription varchar(20))
0
 
HilaireCommented:
Could you be more precise about what you want to achieve ?

User-defined datatypes in SQL Server do not allow (for the moment) to build complex datatypes.
Yet there are workarounds, like table variables, check constraints, ... depending on what you need to do.

0
Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

 
nexusSamCommented:
You can create an IDENTITY column which self-increments giving you an enumerated list
0
 
Richard QuadlingSenior Software DeveloperAuthor Commented:
I have a column which needs to be ...

SQL, Total or Percent (1,2,3).

This setting then dictates which table to link to get additional information.

e.g.

If the row is an SQL row, then we look at a table which contains elements dealing with the creation of an SQL statement from a database.
If the row is a Percent row, then we look at a table which contains elements dealing with calculation percentages.
If the row is a Total row, then we look at a table which contains elements dealing with totals.

Initially, only these three types are defined, but others will come. One that I know of is Regress.

The table I am looking at is a table which describes "elements". Each element gets its data from somewhere. Each element has a name, desciption, output label, rules of comparison, colour logic.

Then there is a table for each specific type of element. There may be multiple records in one of the additional tables.

I think a constraint is good enough.

If I use an integer type I can also order them (I want SQL first, then totals then percentages. The Regress type may come in at all levels, RegressSQL, RegressTotals, RegressPercent - I do not know yet).

Richard.
0
 
Richard QuadlingSenior Software DeveloperAuthor Commented:
IDENTITY (or autoinc as I know it) is not the right way. I want to control the values being inserted, not have then assigned as I insert.

Richard.
0
 
ShogunWadeCommented:
given that there are only 3 possible values you may aswell use a tinyint
0
 
Anthony PerkinsCommented:
If there are only 3 possible values you can use a computed column.
0
 
Richard QuadlingSenior Software DeveloperAuthor Commented:
Oh. More on the computed column please?
0
 
Anthony PerkinsCommented:
On second thoughts, seeing that you have more than one attribute, namely: "name, desciption, output label, rules of comparison, colour logic" ShogunWade's solution is best.
0
 
Richard QuadlingSenior Software DeveloperAuthor Commented:
OK.

I now realise that the type (SQL, Percent, Total, etc) have other properties which had previously been hardcoded. This column is now a lookup with an evaluation order (i.e. you can't work out percentages or totals on data without having first got data from the DB!).

So. Look up table it is!

Thanks for this.

Richard.
0
All Courses

From novice to tech pro — start learning today.