Solved

How do I implement enum data type?

Posted on 2004-08-31
11
2,020 Views
Last Modified: 2006-11-17
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.
0
Comment
Question by:Richard Quadling
  • 4
  • 3
  • 2
  • +2
11 Comments
 
LVL 18

Expert Comment

by:ShogunWade
ID: 11942093
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
 
LVL 26

Expert Comment

by:Hilaire
ID: 11942433
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
 
LVL 2

Expert Comment

by:nexusSam
ID: 11942663
You can create an IDENTITY column which self-increments giving you an enumerated list
0
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
LVL 40

Author Comment

by:Richard Quadling
ID: 11942726
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
 
LVL 40

Author Comment

by:Richard Quadling
ID: 11942754
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
 
LVL 18

Accepted Solution

by:
ShogunWade earned 50 total points
ID: 11942755
Sounds to me like a standard lookup table with 3 values.
0
 
LVL 18

Expert Comment

by:ShogunWade
ID: 11942764
given that there are only 3 possible values you may aswell use a tinyint
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 11944026
If there are only 3 possible values you can use a computed column.
0
 
LVL 40

Author Comment

by:Richard Quadling
ID: 11944929
Oh. More on the computed column please?
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 11944982
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
 
LVL 40

Author Comment

by:Richard Quadling
ID: 11949646
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

Featured Post

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

830 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