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 3.5 (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.

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

See Also

Reference

ALTER TABLE (SQL Server Compact)
CREATE TABLE (SQL Server Compact)

Help and Information

Getting Assistance (SQL Server Compact 3.5 Service Pack 1)