SQL Server 2008 R2. Incorrect syntax near 'AUTO_INCREMENT'

Why do i get the following error

Incorrect syntax near 'AUTO_INCREMENT'.

while trying to execute

CREATE TABLE Person
(
    P_Id int NOT NULL AUTO_INCREMENT,
    Name varchar开发者_Go百科(255),

    PRIMARY KEY (P_Id)
)

What is the correct syntax?

CREATE TABLE Person(
P_Id int NOT NULL IDENTITY(1,1) PRIMARY KEY,
Name varchar(255))

You should explicitly state whether NAME is NULL or NOT NULL so you are not dependant upon the current connection settings that happen to be in effect.

create table Person
(
    PersonId int identity(1,1) 
        constraint PK_Person primary key,
    Name varchar(255) not null
)

Some comments:

1. It is not needed to specify not null for identity column as identity column cannot be nullable. ANSI_NULL_DFLT_ON option does not affect 'nullability' of identity column.
2. On other hand it is important to specify 'not null / null' for Name column, as it will be affected by ANSI_NULL_DFLT_ON value.
3. It is always a good idea to explicitly specify names for constraints. Because if you don't, name constraint name will be generated. If you need to delete the constraint later, you will have to find out the auto-generated name.