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