IDENTITY Property (SQL Server Compact)

SQL Server 2012

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

Copy 

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.

Note Note

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

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.

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

Copy 
-- 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: