Migration from MySQL to SQL Server

How can I change the MySQL code to SQL Server's like below?
I could not find out to change appropriate UNSIGNED keyword and ENUM
keyword in SQL Server.

CREATE TABLE myTable (
id INT UNSIGNED NOT NULL,
name CHAR(20),
role ENUM('ADMIN','USER','GUEST','UNKNOWN') DEFAULT 'ADMIN'
PRIMARY KEY(id)
);

hi,

you can use "standard" signed integers in SQL Server syntax to host integer values in the range -2,147,483,648 to 2,147,483,647 as SQL Server does not provide unsigned integers... you can "scale up" to bigint if you required larger range in the integer domain..
http://msdn2.microsoft.com/en-us/library/ms187745.aspx

SQL Server does not provide an "Enum" feature, but you can define a CHECK CONSTRAINT for that...
usually you should consider a referential integrity based referenced table in the design if the "range" can grow as a direct CHECK requires modelling refinement and rewriting if you, say, want to add sometime later another "role" valid entry...

so... you can "translate" as

SET NOCOUNT ON; USE tempdb; GO CREATE TABLE myTable ( Id int NOT NULL PRIMARY KEY, Name char(20) NULL, -- but I bet varchar(20) should be better -- as not all names will be 20 chars long Role varchar(7) NOT NULL DEFAULT 'ADMIN' CONSTRAINT chk_Valid$Entries$for$Roles -- use this check constraint if you require CASE SENSITIVE validation of the role -- CHECK ( Role IN ('ADMIN' COLLATE Latin1_General_CS_AS , 'USER' COLLATE Latin1_General_CS_AS , 'GUEST' COLLATE Latin1_General_CS_AS , 'UNKNOWN' COLLATE Latin1_General_CS_AS ) ) -- use this check constraint if you do not require CASE SENSITIVE validation of the role CHECK ( Role IN ('ADMIN', 'USER', 'GUEST', 'UNKNOWN') ) ); GO INSERT INTO myTable VALUES ( 1 , 'Andrea', DEFAULT ); -- default role GO INSERT INTO myTable VALUES ( 2 , 'Andrea2', 'USER' ); -- valid role GO INSERT INTO myTable VALUES ( 3 , 'Andrea3', 'other' ); -- NOT valid role GO INSERT INTO myTable VALUES ( 4 , 'Andrea3', 'User' ); -- NOT valid role IF case sensite -- collation check has been set GO SELECT * FROM myTable GO DROP TABLE myTable; --<-- Msg 547, Level 16, State 0, Line 1 The INSERT statement conflicted with the CHECK constraint "chk_Valid$Entries$for$Roles". The conflict occurred in database "tempdb", table "dbo.myTable", column 'Role'. The statement has been terminated. Id Name Role -- -- - 1 Andrea ADMIN 2 Andrea2 USER 4 Andrea3 User 

I provided 2 different check constraints... the active one is NOT case sensitive, that's to say users can enter both 'ADMIN' and 'admin' (or whatever conbination of cases), where the commented one performs a case sensitive check and the 4th insert will fail accordingly...

thanks to Lorenzo Benaglia, friend and SQL Server MVP fellow for helping in the "migration"...

regards