IDENTITY Property (SQL Server Compact)
Creates an identity column in a table. This property is used with the CREATE TABLE and ALTER TABLE statements.
Syntax
IDENTITY [ (seed,increment) ]
Arguments
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.Note
You must specify both the seed and increment, or neither. If neither is specified, the default is (1,1).
Remarks
In Microsoft SQL Server Compact, the IDENTITY property can be created only on a column of data type integer or bigint. A table can have only one IDENTITY column.
Examples
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