Apostrofix Apostrofix - 3 months ago 15
SQL Question

SET datatype set in SQL Server

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.

Answer

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