What's the difference between AUTO_INCREMENT and IDENTITY in MySQL and SQL Server?

Questions › Category: Databases › What's the difference between AUTO_INCREMENT and IDENTITY in MySQL and SQL Server?

Julie W. asked 2 years ago

Why does MySQL use AUTO_INCREMENT and SQL Server use IDENTITY? I thought SQL are ANSI standard? Could you explain?
Thank you.
Julie

People Also View

Leave a ReplyCancel reply

1 Answers

0 Vote Up Vote Down

Christian Staff answered 2 years ago

Hi Julie,
There are some standards but I don’t believe it’s for all databases. Some of these commands are system specific. In this case, the AUTO_INCREMENT is primarily used for MySQL/MariaDB database systems and IDENTITY is used in Microsoft SQL Server. Then in Oracle, you’d use to generate a SEQUENCE of unique numbers. There’s a standard for some databses. For example, the following command should be compatible with Oracle, PostgreSQL, DB2, Apache Derby. There could be more.

CREATE TABLE My_Table(
  mid INTEGER GENERATED ALWAYS AS IDENTITY,
  name varchar(20)
);

Of you want to start with a specfiic number and specific incremental:

CREATE TABLE My_Table(
  mid INTEGER GENERATED ALWAYS AS IDENTITY (start with 100 increment by 2 cycle),
  name varchar(20)
);

Hope this helps.