Auto increment allows the database to assign a unique number or value to be assigned to a record when new data is inserted into the table. This is often used as a default primary key for lookup tables or where it is difficult to identify a natural key.
In SQL Server, we can assign a column of type Identity. Each table can have only one column that is of type Identity. We use the Identity option when the table is created.
If you are following along with the sample database I am using, you could add the table Fish into the mix. This table uses FishIdentifier as the primary key and also specifies this column be used as an Identity column.
CREATE TABLE Fish
FishIdentifier INT IDENTITY(1500, 1) ,
BirthDateOfFish DATETIME NULL ,
BreedOfFish VARCHAR(30) NULL ,
ColorOfFish VARCHAR(25) NOT NULL ,
MonthsWithOwner INT NULL ,
NameOfFish CHAR(40) NOT NULL ,
OwnerIdentifier INT NULL ,
TankSizeInGallons SMALLINT NULL ,
PRIMARY KEY NONCLUSTERED ( FishIdentifier )
You will notice the numbers after the IDENTITY property. The first number tells the database what the first assignment should be–this is called the seed. In the case of the Fish table, the first record inserted will have a FishIdentifier of 1500. The second number is called the increment. The increment tells the database how many to add to the see for the next number. In this example, we have one so the second row would be 1501. I can’t think of a good scenario to use a different increment, but you can.
In SQL Server, if you right click the table in SQL Server Management Studio (SSMS), you can click design. This gives you a way to edit the table and on the top you see the columns and the data types. Choose the column you think is the identity and the bottom panel with update with information. The Identify Specification indicates if the property has been set for that column or not. Normally, these are on primary key fields, so you can check those first.