SQL Server AUTO_INCREMENT error

database sql-server-2008 primary-key auto-increment heidisql
Anubhav Chatterjee picture Anubhav Chatterjee · Feb 23, 2015 · Viewed 11.3k times · Source
  • 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

Marcell picture Marcell · Oct 1, 2015

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.

Related questions

database: primary key, Clustered or NonClustered

I am creating a database in SQL server 2008, CREATE TABLE Users ( U_Id INT NOT NULL FirstName VARCHAR(50) NOT NULL, LastName VARCHAR(50) NOT NULL, Email VARCHAR(200) Password VARCHAR(50) ) I want to make U_Id the primary key. I would like …

Read More
Reset identity seed after deleting records in SQL Server

I have inserted records into a SQL Server database table. The table had a primary key defined and the auto increment identity seed is set to “Yes”. This is done primarily because in SQL Azure, each table has to have …

Read More
how to schedule a job for sql query to run daily?

I need to know how to make a SQL query run daily using a SQL Server Agent job, with minimum required configuration settings.

Read More