To create a table with an IDENTITY column for auto-incrementing primary keys in SQL Server, you can use the following syntax:
CREATE TABLE table_name (
column1 data_type [NOT NULL] PRIMARY KEY,
column2 data_type,
...
identity_column data_type IDENTITY(start_value, increment_value),
...
);
Explanation:
- table_name: The name of your table.
- column1: Other column names and their respective data types. The primary key column should be defined as the first column where you place PRIMARY KEY.
- identity_column: This is the column that you want to auto-increment. Replace data_type with the appropriate type (e.g., INT, BIGINT, etc.).
- IDENTITY(start_value, increment_value): This specifies that the column is an identity column. start_value is the value that will be assigned to the first row, and increment_value specifies how much to increase the value for each subsequent row.
- You can add as many other columns as needed.
Example:
Here’s an example of creating a table called Employees with an IDENTITY column for the primary key:
CREATE TABLE Employees (
EmployeeID INT IDENTITY(1,1) PRIMARY KEY,
FirstName NVARCHAR(50),
LastName NVARCHAR(50),
HireDate DATE
);
In this example:
- EmployeeID is defined as an INT IDENTITY column that starts at 1 and increments by 1 for each new row inserted.
- FirstName, LastName, and HireDate are other columns in the table.
Notes:
- The IDENTITY property can only be applied to columns of types INT, BIGINT, SMALLINT, TINYINT, and DECIMAL.
- You cannot insert a value into an IDENTITY column unless you set SET IDENTITY_INSERT table_name ON; before the insert statement. However, this is not common practice unless you're performing bulk inserts or restoring data.
This setup ensures that each record in the Employees table will have a unique identifier without needing to manage it manually.