SQL Server AUTO_INCREMENT error

Question

• Interface: HeidiSQL
• Database: SQL Server

I'm trying to create table keeping ID (int) column as primary key as well as Auto_Increment.

But SQL Server is throwing Error 102:

Incorrect Syntax near 'Auto_increment'.

Please Help. Thanks.

Answer 1

The problem is that HeidiSQL will use the keyword AUTO_INCREMENT instead of IDENTITY. However AUTO_INCREMENT is not a valid specification for SQL Server CREATE TABLE statements. Here is how you do it:

CREATE TABLE "YourTableName"
(
   "ID" INT NOT NULL IDENTITY(1,1)
   PRIMARY KEY ("ID")
);

I didn't find a way to generate the CREATE TABLES with IDENTITY specification by using HeidiSQL. If someone does, please tell us.