SET datatype set in SQL Server

Question

While creating a table I have to use the datatype SET, but it looks like there is no datatype SET in SQL Server. I was looking on the Microsoft's website and those are the datatypes that it supports: http://msdn.microsoft.com/en-us/library/ms187752.aspx

Which one should I use to replace the SET?

I have used SET in MySQL database like this:

CREATE TABLE IF NOT EXISTS `configurations` (
`index` int(11) NOT NULL AUTO_INCREMENT,
`user_id` int(11) NOT NULL,
`configDuration` int(5) NOT NULL,
`configDurationPerspective` set('list_this_day','list_remaining') NOT NULL,
PRIMARY KEY (`index`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1;

And then when I insert data into the table it looks like this:

INSERT INTO 'configurations' (index, user_id, configDuration, configDurationPerspective) VALUES (1, 1, 2, 'list_this_day');

Never mind the quotes. Something messed up while pasting the code.

Now I want to do the same thing, but in SQL Server.

What would you like to do with the SET? If you answer we may be able to propose an alternative way of doing what you'd like to do... — mortb, Nov 05 '12 at 12:20
Ah, here is the definition: http://dev.mysql.com/doc/refman/5.0/en/set.html. — mortb, Nov 05 '12 at 12:26
I have edited my post. Yes, exactly, this is the definition of the SET. But I guess this is for MySQL. I couldn't find something like this for MS SQL. — Apostrofix, Nov 05 '12 at 12:33
I can only select one of them when I try to insert something in that table. There was a question asking me that, but its gone now(may be it was deleted). The version is 2008. — Apostrofix, Nov 05 '12 at 12:51
A `BIGINT` contains 64-bits, but the bitwise operators only support 32-bit `INT` values. I would create a lookup table that contains name/value pairs for the bits. That allows you to output set values as a comma delimited list by looking up the names. Checking for a value by name is fairly straightforward. Adding elements to a set is a nuisance, especially if you want to handle comma delimited lists as input. A trigger can verify that only defined bits are set in the "set" column. — HABO, Nov 05 '12 at 14:07

score 2 · Accepted Answer · edited Aug 10 '16 at 15:55

You'd either have to use separate bit fields (one column with bit datatype per value) or you'd pack the values into a column with a integer datatype. If you'd use integer you'd have to use t-sql bitwise operators to read and write the values.

If you use bitwise operators you'll only get one column The create table statement should look like this:

CREATE TABLE configurations(
[index] int NOT NULL IDENTITY (1,1) PRIMARY KEY,
user_id int NOT NULL,
configDuration int  NOT NULL,
configDurationPerspective int NOT NULL,
)

And then you'd have to insert values that are possible to bitmask like 1,2,4,8,16,32 into configDurationPerspective

INSERT INTO 'configurations' (index, user_id, configDuration, configDurationPerspective) VALUES (1, 1, 2, 'list_this_day');

would translate to

INSERT INTO 'configurations' (index, user_id, configDuration, configDurationPerspective) VALUES (1, 1, 2, 1);

And

INSERT INTO 'configurations' (index, user_id, configDuration, configDurationPerspective) VALUES (1, 1, 2, 'list_remaining');

would translate to

INSERT INTO 'configurations' (index, user_id, configDuration, configDurationPerspective) VALUES (1, 1, 2, 2);

and selecting could look like:

select  [index], configDuration,
case when configDurationPerspective & 1 > 0 then 'list_this_day' else '' end
 + case when configDurationPerspective & 2 > 0 then 'list_remaining' else '' end as configDurationPerspective
 from configurations

I've edited my post and written some suggestions for how to pack the values into an int and how to select them. If you put the values into an .NET-enum in your code after you select them there is no need for bitmasking, you'd just cast the db int value to the enum type. — mortb, Nov 05 '12 at 13:54

score 2 · Answer 2 · edited May 23 '17 at 10:27

The list of basic types in MS SQL Server does not support the same. But what we have are constraints and user types. In this question you can see how MySQL enum is solved

SQL Server equivalent to MySQL enum data type?

And you can also observe user types (I've seen that they were used for the similar purpose)

http://msdn.microsoft.com/en-us/library/ms175007.aspx

But as the most typical solution to this issue, we were (on our projects) using some "CodeList/StaticList" table and referencing it by Primary key (int, shortint, tinyint)

SET datatype set in SQL Server

2 Answers2