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

QuestionsCategory: DatabasesWhat's the difference between AUTO_INCREMENT and IDENTITY in MySQL and SQL Server?
Julie W. asked 6 months ago

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

1 Answers
christian Staff answered 6 months 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.

Your Answer

5 + 2 =