Create Enum in SQL Server

Create enum in SQL Server

It's better to properly normalize your model:

create table user_rank
(
   id integer primary key, -- no identity, so you can control the values
   rank varchar(20) not null unique
);

insert into user_rank (id, rank)
values
  (1, 'Fresh Meat'),
  (2, 'Intern'),
  (3, 'Janitor'),
  (4, 'Lieutenant'),
  (5, 'Supreme being');

CREATE TABLE [users]
(
   ID INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
   username varchar(255),
   password varchar(255),
   mail varchar (255),
   rank integer not null default 1, 
   constraint fk_user_rank foreign key (rank) references user_rank (id)
);

The dropdown on your web site can easily be populated by querying the user_rank table.

SQL Server equivalent to MySQL enum data type?

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

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

How to define ENUM in SQL Server 2005?

Use one or more scalar UDFs?

One per constant:

• dbo.CONST_Bicycle returns 1
• dbo.CONST_Car returns 2

One per enum:

• dbo.CONST_Types('Bicycle') returns 1
• dbo.CONST_Types('Car') returns 2

Or use a table with ID, Name per enum

Use a client side enum to match this (perhaps with validation against the table solution)

There is no quick or clean way to do this like there is in .net (as per your comment).

SQL create enum as column and group by date

You cannot nest aggregation functions, but you can simply use:

SELECT date(l.date) as date,
       sum(l.category = 'add') as add,
       sum(l.category = 'delete') as delete,
       sum(l.category = 'edit') as edit
FROM log l
WHERE l.date >= '2021-08-01' AND l.date < '2021-08-31'
GROUP BY l.date

SQL replace a string enum in a column of a result set with another string

You can do it with the CASE expression:

SELECT ...
,      CASE Column
         WHEN 'A1' THEN 'started'
         WHEN 'B2' THEN 'running'
         ... END AS Status
FROM Table

Read https://learn.microsoft.com/en-us/sql/t-sql/language-elements/case-transact-sql?view=sql-server-ver15

Simulate enums in TSQL?

You could take a look at the answer to this question. As far as I know, enum types are not part of SQL Server.

Anyhow, it's better to use an integral (INT, TINYINT, ...) type for your enums than a string type. Usually the enum types in your programming language of choice correspond better to integers than to strings.

In C#, by default every enum value corresponds to an integer, starting from 0. You can even cast between them, so this is valid code:

public enum MyEnum
{
    FirstEnumValue = 0,
    SecondEnumValue = 1
}

...

// Assuming you have opened a SqlDataReader.
MyEnum enumValue = (MyEnum) reader["account_category"];

And LINQtoSQL also supports this. If you have an enum-typed property and your database column is an integer type, conversion is automatic.

---

Related Topics