Creating Database and Tables - Beginning C# 5.0 Databases - page 34

Figure 3-8. The create table statement and Column properties
Adding an IDENTITY Column in a Table
Sometimes developers and database programmers need to create a new column in an existing table. SQL
Server provides an ALTER statement to modify/alter the objects that are already created.
In this case, the table you created is missing a column that can help uniquely identify each person,
because a name could be common with two people; for example, both might be the same age and
gender and without an SSN. In such a scenario, having a column that can uniquely identify each row is a
valuable addition, and it's also how all the real-world databases are designed.
The best way to have each person identified with a distinct value or ID is to use SQL Server's
IDENTITY property. IDENTITY is an auto-incremented value that is inserted by SQL Server
automatically for each inserted row to a table. That is, you are not responsible for specifying the value of
the IDENTITY column.
This IDENTITY property requires an Integer type column to be associated with it. The IDENTITY
property is used for those columns that need automatically generated unique system values. This
property can be used to generate sequential numbers. The syntax for this is IDENTITY (Seed,Increment) .
Seed is the starting or initial value for the IDENTITY column. Increment is the step value used to generate
the next value for column.
For example, int ColumnName IDENTITY(1,1) means the first row will have the value 1, the second
row will have 2, and so on. The same IDENTITY property definition can be written as int ColumnName
IDENTITY . Hence, IDENTITY and IDENTITY(1,1) are the same.