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.

Solution 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.

Solution 2

In SQL Server, it's called an IDENTITY:

CREATE TABLE dbo.YourTAble
(
   ID INT NOT NULL IDENTITY(1,1),
    .............
)

The official MSDN documentation on CREATE TABLE contains all the possible options - that should always be your first place to look for answers like this ....

Solution 3

If you can use the SSMS (SQL Server Management Studio) as the GUI for database management tasks, you can change the ID column as an auto increment column by setting its Identity Specification attributes as follows

Comments

[ainasiart]

Add all your columns, don't hit on Save button, instead, go to the tab "Create Code", copy all code and paste in a query tab, add IDENTITY(1,1) to the column ID, then execute the code