SqlServer create table with MySql like auto_increment primary key

Question

I want to make a table in SqlServer that will add, on insert, a auto incremented primary key. This should be an autoincremented id similar to MySql auto_increment functionality. (Below)

create table foo  
(  
user_id int not null auto_increment,  
name varchar(50)  
)

Is there a way of doing this with out creating an insert trigger?

Answer 1

Just set the field as an identity field.

Answer 2

Like this

create table foo  
(  
user_id int not null identity,  
name varchar(50)  
)

Answer 3

declare the field to be identity

Answer 4

As others have said, just set the Identity option.

Answer 5

As advised above, use an IDENTITY field.

CREATE TABLE foo
(
user_id int IDENTITY(1,1) NOT NULL,
name varchar(50)
)

Answer 6

OP requested an auto incremented primary key. The IDENTITY keyword does not, by itself, make a column be the primary key.

CREATE TABLE user
(
  TheKey int IDENTITY(1,1) PRIMARY KEY,
  Name varchar(50)
)

Answer 7

As others have mentioned: add the IDENTITY attribute to the column, and make it a primary key.

There are, however, differences between MSSQL's IDENTITY and MySQL's AUTO_INCREMENT:

• MySQL requires that a unique constraint (often in the form of a primary key) be defined for the AUTO_INCREMENT column.
  MSSQL doesn't have such a requirement.
• MySQL lets you manually insert values into an AUTO_INCREMENT column.
  MSSQL prevents you from manually inserting a value into an IDENTITY column; if needed, you can override this by issuing a "SET IDENTITY_INSERT tablename ON" command before the insert.
• MySQL allows you to update values in an AUTO_INCREMENT column.
  MSSQL refuses to update values in an IDENTITY column.

Answer 8

They have answered your question but I want to add one bit of advice for someone new to using identity columns. There are times when you have to return the value of the identity just inserted so that you can insert into a related table. Many sources will tell you to use @@identity to get this value. Under no circumstances should you ever use @@identity if you want to mantain data integrity. It will give the identity created in a trigger if one of them is added to insert to another table. Since you cannot guarantee the value of @@identity will always be correct, it is best to never use @@identity. Use scope_identity() to get this value instead. I know this is slightly off topic, but it is important to your understanding of how to use identity with SQL Server. And trust me, you did not want to be fixing a problem of the related records having the wrong identity value fed to them. This is something that can quietly go wrong for months before it is dicovered and is almost impossible to fix the data afterward.