SQL Server equivalent to MySQL enum data type?

Question

Does SQL Server 2008 have a a data-type like MySQL's enum?

Found this interesting approach when I wanted to implement enums in SQL Server. The approach mentioned below in the link is quite compelling, considering all your database enum needs could be satisfied with 2 central tables. [SQL SERVER – Enumerations in Relational Database – Best Practice](http://blog.sqlauthority.com/2010/03/22/sql-server-enumerations-in-relational-database-best-practice/) — user_v, Aug 27 '13 at 08:07
@user_v This is a variation of the anti-pattern known as "one true (lookup) table". The proper approach is to have separate table for each enum type and use foreign keys (if you need lookup at all, which may not be the case for "pure" enums). — Branko Dimitrijevic, Sep 04 '14 at 13:22
@user_v The comments on the linked page provide good backup for using individual tables for each "enum", rather than what this answer specifies — nikodaemus, Apr 13 '15 at 20:14
https://stackoverflow.com/questions/3419847/what-is-a-lookup-table Seems to be the solution for this, as suggested by some answers here. — carloswm85, Oct 25 '22 at 17:10

score 207 · Accepted Answer · answered Sep 16 '09 at 17:19

207

It doesn't. There's a vague equivalent:

mycol VARCHAR(10) NOT NULL CHECK (mycol IN('Useful', 'Useless', 'Unknown'))

answered Sep 16 '09 at 17:19

chaos

122,029
33
303
309

23

Why not use a table that defines valid values, and then use a foreign key constraint instead? – Elaskanator Dec 05 '18 at 17:04
2

@Elaskanator I'd say this most precisely answers the direct OP, while the best solution for it is probably the FK+external table usage. – userfuser Dec 21 '18 at 09:12
1

Thank you @Elaskanator for reminding me of the obvious... normalize the data and enum's stop existing. – Andrew Apr 30 '19 at 18:36
10

@Elaskanator Often enum values are defined in application code first (e.g. C# `enum`), whereas if they used a table FK reference then those supposedly static enum entries could be modified at runtime which would be undesirable (and SQL Server doesn't support the concept of immutable tables), finally if you have lots of enums with only a few values then you'll end-up adding lots of tables to your database. Not to mention extra IO reads due to FK constraint-checking when inserting/deleting data, whereas a `CHECK CONSTRAINT` is much faster and doesn't cause database object spam. – Dai Sep 18 '20 at 07:23
2

I agree with @dai. I used to use code-tables, but I see them as overkill. I like the CHECK functionality, and varchar. It allows you to use a static Enum in C# without a bunch of fancy workarounds. Performance-wise, sqlserver is perfectly capable of indexing strings almost as well as ints or smallints. The performance gain is minimal compared to the additional coding complexity. Time is money. – James O'Brien Oct 26 '20 at 21:14
@JamesO'Brien That is a naïve assumption to make without understanding the particular use case. It probably wont make the world of difference with a small table but all other things being equal int's consume a lot less space. This helps to keep your index size leaner and reduce page IO's. On a very big table this can make a drastic difference. Sorting is also a lot more expensive in terms of CPU resources, and collation can have an impact. If working with large amounts of data it is important to benchmark before making these kinds of assumptions. – Jamie Pearcey Mar 09 '21 at 10:53
@JamiePearcey Well yes - it depends on your particular use case. I more meant that this is another option. I ended up going back to code tables for enums on my current project. – James O'Brien Mar 10 '21 at 18:29
@Elaskanator - why do I want to incur the overhead of locking that table every time I want to use something that is, for all intents-and-purposes, a constant? How difficult would it be to implement an Enum in the language? – Quark Soup Jul 11 '21 at 22:02
@Quarkly "How difficult would it be to implement an Enum in the language?" is not a reasonable question. – Elaskanator Jul 13 '21 at 20:21
@Elaskanator - Only unreasonable people think in those terms. For those who can reason, it's a static list of constants. No table locking, no additional indices to navigate and the free version of SQL already has it. – Quark Soup Jul 14 '21 at 23:38
@Elaskanator That would work for a single-choice `ENUM` but not for a multiple-choice `SET`. – Danny Beckett Nov 12 '21 at 14:30
Using text values with limitation by CHECK has several disadvantages: uses more memory, has no user-defined order, is defined by column (has to be defined redundantly in several places unless defined as a domain). – Holger Jakobs Jun 19 '23 at 12:49

score 105 · Answer 2 · answered Jun 21 '12 at 12:53

105

The best solution I've found in this is to create a lookup table with the possible values as a primary key, and create a foreign key to the lookup table.

answered Jun 21 '12 at 12:53

user1431422

1,059
1
7
2

17

A better solution from a maintainability perspective than the check constraint shown above. – HLGEM Jun 21 '12 at 13:59
23

This is a better solution than Enums - in MySQL as well. – ypercubeᵀᴹ Jun 28 '12 at 09:29
5

@ypercube Why is it better for MySQL as well? – BenR Nov 17 '14 at 18:52
6

@BenRecord There are several issues with MySQL's enums: [8 Reasons Why MySQL's ENUM Data Type Is Evil](http://komlenic.com/244/8-reasons-why-mysqls-enum-data-type-is-evil/). I don't agree 100% that it's evil but you have to be extra careful when using them. – ypercubeᵀᴹ Nov 17 '14 at 19:20
2

@BenR also if I recall correctly, in non-strict mode with MySQL, an invalid enum can be inserted as a NULL. Whatever the condition, a previous team of mine had issues with a MySQL enum not getting inserted and *not failing* when the value wasn't specified in the list of values. A foreign key constraint on a lookup table would cause a failure. I agree the lookup table is better for MySQL. – Joseph Yaduvanshi Jul 13 '15 at 15:58
1

In addition, the table can be made read-only, if you don't want the lookup table's values to ever be added-to/altered/deleted. See: https://stackoverflow.com/questions/2529839/how-to-make-a-table-read-only-in-sql-server – Daniel Neel Jan 07 '16 at 22:59
@ypercubeᵀᴹ - Same question as above. Why do I want to incur the cost of locking an extra table every time I want to use a constant? – Quark Soup Jul 11 '21 at 22:04
@Quarkly what do you mean with "locking an extra table"? There is nothing wrong with **having** one more table in your design. As of why it's better than enums, take for example what would happen if you want to change the data, from 3 possible values to 4. With enums, you have to ALTER the column definition of the table that has the enum or the CHECK constraint (possibly locking the entire - maybe huge - table while doing so). With the extra table and a FK, you only have to insert 1 row. No DDL, no locking. – ypercubeᵀᴹ Jul 12 '21 at 07:00
@ypercubeᵀᴹ - No one here is talking about **One** more table in a design. Including lookup tables for enums multiplies the number of tables locked for a given operation geometrically. If every table has two 'enum' values as attributes and you have eight tables in a query (before the enum tables are included), then you're looking at 24 table locks instead of eight. Glad you're small database isn't impacted but the overhead is measurable in my customers' databases. – Quark Soup Jul 15 '21 at 15:09
@Quackly I know exactly what I said and I did mean one more table (for every enum). Your previous comment has "locking", this one has "locked" and I really cannot fathom why you think that a query that access 24 (or even 240 tables) needs to lock them. I f you mean to do lookup (in those tables), yeah, it might need to. – ypercubeᵀᴹ Jul 15 '21 at 15:19
Another thing is even though you may have reference/lookup tables instead of enum columns that does not mean that your queries need to use them. I don't have to join my `country` table when I can equivalently do for example `WHERE actual_table_we_need.country_code = 'FR'` – ypercubeᵀᴹ Jul 15 '21 at 15:21
So with either an enum column or a reference table, your query will have `WHERE actual_table_we_need.country_code = 'FR'`. The only difference is that on the design with reference tables (what is suggested in this answer), you'll have an extra table (for every enum), eg. `country` with a PK of `(country_code)` and an FK from the `actual_table_we_need (country_code) REFERENCES country (country_code)` – ypercubeᵀᴹ Jul 15 '21 at 15:35
This would work for a single-choice `ENUM` but not for a multiple-choice `SET`. – Danny Beckett Nov 12 '21 at 14:30
A lookup table is nice if the allowed values change regularly. Otherwise, an enum is the way to go as it is far better regarding performance. – Holger Jakobs Jun 19 '23 at 12:50

score 8 · Answer 3 · answered Aug 01 '12 at 12:36

8

IMHO Lookup tables is the way to go, with referential integrity. But only if you avoid "Evil Magic Numbers" by following an example such as this one: Generate enum from a database lookup table using T4

Have Fun!

answered Aug 01 '12 at 12:36

Jony Adamit

3,178
35
44

You don't need T4 to do this... – Mark Schultheiss Mar 22 '22 at 19:27
1

Haha @MarkSchultheiss you DID notice this was 10 years ago right?? – Jony Adamit Mar 23 '22 at 11:17
Yes, just adding a comment for that - not suggesting this was a bad answer or even still is in some places. Sometimes technology changes, imagine that! – Mark Schultheiss Mar 23 '22 at 11:56

score 5 · Answer 4 · edited Feb 23 '12 at 22:28

5

CREATE FUNCTION ActionState_Preassigned()
RETURNS tinyint
AS
BEGIN
    RETURN 0
END

GO

CREATE FUNCTION ActionState_Unassigned()
RETURNS tinyint
AS
BEGIN
    RETURN 1
END

-- etc...

Where performance matters, still use the hard values.

edited Feb 23 '12 at 22:28

Taryn

242,637
56
362
405

answered Feb 23 '12 at 06:43

Dimitrios Staikos

323
4
8

carloswm85 · Answer 5 · 2022-10-25T17:38:17.880

Probably the best solution for this is a simple look-up table (What is a lookup table?). Nevertheless you can implement something like this:

Solution

SQL Server Management Studio (SSMS)

User.Role is the Foreign Key here, and Role.Type is the Primary Key it refers. And in that table, you'll have the following values:

The type of which must match in both tables. In this case that type is: nvarchar(15)
If you try to add a value at User.Role different than those available at the Role table, you'll get an error.

SQL Code

CREATE TABLE [dbo].[User](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [Username] [nvarchar](50) NOT NULL,
    [Email] [nvarchar](75) NOT NULL,
    [Password] [nvarchar](25) NOT NULL,
    [Role] [nvarchar](15) NOT NULL,
 CONSTRAINT [PK_User] PRIMARY KEY CLUSTERED 
(
    [Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[User]  WITH CHECK ADD  CONSTRAINT [FK_User_Role] FOREIGN KEY([Role])
REFERENCES [dbo].[Role] ([Type])
GO

ALTER TABLE [dbo].[User] CHECK CONSTRAINT [FK_User_Role]
GO

CREATE TABLE [dbo].[Role](
    [Type] [nvarchar](15) NOT NULL,
 CONSTRAINT [PK_Role] PRIMARY KEY CLUSTERED 
(
    [Type] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO

Solution 2

You can implement a lookup table like this one: Create enum in SQL Server

score 0 · Answer 6 · answered Dec 29 '21 at 11:39

0

You can try something like

ALTER TABLE dbo.yourTable
ADD CONSTRAINT yourColumn CHECK(yourColumn IN('XL','L','M','S','XS'))

answered Dec 29 '21 at 11:39

Jonan87

31
2
6

SQL Server equivalent to MySQL enum data type?

6 Answers6

Solution

SQL Server Management Studio (SSMS)

SQL Code

Solution 2

Linked