Convert MySQL AUTO_INCREMENT to SQL Server

MySQL provides AUTO_INCREMENT option to generate sequence of unique integer numbers for a column automatically. Each table can have only one AUTO_INCREMENT column and it must be a part of primary key. AUTO_INCREMENT column cannot have a default value. Unlike other database management systems, MySQL does not allow to specify increment step, it is always 1. Start value can be specified in CREATE TABLE statement as it is illustrated below:

CREATE TABLE cities(
   id INT AUTO_INCREMENT PRIMARY KEY, 
   name VARCHAR(100)
) AUTO_INCREMENT = 10; 

In order to generate AUTO_INCREMENT value just omit the corresponding column in INSERT query:

INSERT INTO cities(name) VALUES ('London');

MySQL function LAST_INSERT_ID() returns the last value being successfully inserted in the current session:

SELECT LAST_INSERT_ID(); -- returns: 10

SQL Server provides IDENTITY(start, step) option for column type with similar capabilities to MySQL AUTO_INCREMENT. This is how the table from example above may look in MS SQL:

CREATE TABLE cities(
   id INT IDENTITY(10, 1) PRIMARY KEY, 
   name VARCHAR(100)
)
GO

Have questions? Contact us