SQL AUTO INCREMENT Field
Auto-increment permits a unique number to be generated when a
new record is inserted into a table.
AUTO INCREMENT
a Field
Very frequently we would like the value of the primary key field
to be created automatically every time a new record is inserted.
We would like to create an auto-increment field in a table.
Syntax for SQL
Server
The following SQL statement defines the "EmpID" column to
be an auto-increment primary key field in the "Employee" table:
CREATE TABLE
Employee
(
EmpID
Bigint IDENTITY(1,1) PRIMARY KEY,
LastName
Nvarchar (200) NOT NULL,
FirstName
Nvarchar (200),
Address Nvarchar (500),
CityCode
Nvarchar(3)
)
The SQL Server uses the IDENTITY
keyword to perform an auto-increment feature.
In the example above, the starting value for IDENTITY is 1, and it
will increment by 1 for each new record.
Tip: To specify that the "EmpID"
column should start at value 200 and increment by 1, change it to IDENTITY(200,1).
To insert a new record into the " Employee " table, we will NOT have to specify
a value for the "EmpID" column a unique value will be added
automatically.
