Export (0) Print
Expand All
Expand Minimize

Creates an identity column in a table. This property is used with the CREATE TABLE and ALTER TABLE statements.



IDENTITY [ (seed,increment) ]

seed

The value that is used for the first row loaded into the table.

increment

The incremental value that is added to the identity value of the previous row that was loaded.

You must specify both the seed and increment, or neither. If neither is specified, the default is (1,1).


In Microsoft SQL Server Compact 3.5, the IDENTITY property can be created only on a column of data type integer or bigint. A table can have only one IDENTITY column.

Description

The following examples demonstrate how to create a table in which the first column is an IDENTITY column, and how to insert and delete values from the table.

Code

-- Create the Tool table.
CREATE TABLE Tool(
   ID INT IDENTITY NOT NULL PRIMARY KEY, 
   Name VARCHAR(40) NOT NULL
)
-- Insert values into the Tool table.
INSERT INTO Tool(Name) VALUES ('Screwdriver')
INSERT INTO Tool(Name) VALUES ('Hammer')
INSERT INTO Tool(Name) VALUES ('Saw')
INSERT INTO Tool(Name) VALUES ('Shovel')
-- Create a gap in the identity values.
DELETE Tool 
WHERE Name = 'Saw'
-- Select the records and check results.
SELECT * 
FROM Tool
-- Insert an explicit ID value of 3.
-- Query returns an error.
INSERT INTO Tool (ID, Name) 
VALUES (3, 'Garden shovel')

-- SET IDENTITY_INSERT to ON.
SET IDENTITY_INSERT Tool ON

-- Insert an explicit ID value of 3.
INSERT INTO Tool (ID, Name) 
VALUES (3, 'Garden shovel')

-- Select the records and check results.
SELECT * 
FROM Tool

-- Drop Tool table.
DROP TABLE Tool
Show:
© 2014 Microsoft