Incorrect syntax near 'unsigned' Microsoft SQL Server-CodePudding

Im having an error with this query, I'm using a the latest SQL server and management studio

See the query below

CREATE TABLE messages_server (
Id int unsigned NOT NULL AUTO_INCREMENT,
SentTime datetime,
MessageRead BIT,
Content varchar(8000),
MessageCategory varchar(255),
MessageUser varchar(255),
PRIMARY KEY (Id)
)

CodePudding user response：

Microsoft SQL Server does not support unsigned integer types, it also doesn't support the keyword AUTO_INCREMENT. It looks like you have generated a script from another DB engine and tried to run it on SQL Server. The corrected script below should run.

CREATE TABLE messages_server (
Id int NOT NULL IDENTITY,
SentTime datetime,
MessageRead BIT,
Content varchar(8000),
MessageCategory varchar(255),
MessageUser varchar(255),
PRIMARY KEY (Id)
)

CodePudding user response：

As mentioned in the comments, firstly SQL Server doesn't support choice of signing of data types; all numerical data types are signed data types with the exception of tinyint which is an unsigned 1-byte integer. If you want to ensure that a column is always a positive value use a CHECK CONSTRAINT.

T-SQL also doesn't use AUTO_INCREMENT it uses IDENTITY.

As such, you likely want something like this. Note I also name your primary key, as naming your CONSTRAINTs is important:

CREATE TABLE dbo.messages_server (Id int NOT NULL IDENTITY(1, 1),
                                  SentTime datetime,
                                  MessageRead bit, --Should this be NULLable?
                                  Content varchar(8000),
                                  MessageCategory varchar(255),
                                  MessageUser varchar(255),
                                  CONSTRAINT PK_messages_server PRIMARY KEY (Id),
                                  CONSTRAINT chk_id_signed CHECK (Id >= 0));