--1.
-----Create tool table.
CREATE TABLE dbo.Tool(
ID INT IDENTITY NOT NULL PRIMARY KEY,
Name VARCHAR(40) NOT NULL
)
GO
--2.
-----Inserting values into products table.
INSERT INTO dbo.Tool(Name) VALUES ('Screwdriver')
INSERT INTO dbo.Tool(Name) VALUES ('Hammer')
INSERT INTO dbo.Tool(Name) VALUES ('Saw')
INSERT INTO dbo.Tool(Name) VALUES ('Shovel')
GO
--3.
SELECT * FROM dbo.Tool
---Results Messages:
ID Name
----------- ----------------------------------------
1 Screwdriver
2 Hammer
3 Saw
4 Shovel
(4 row(s) affected)
--4.
-----Create a gap in the identity values.
DELETE dbo.Tool
WHERE Name = 'Saw'
GO
--5.
---Results Messages:
ID Name
----------- ----------------------------------------
1 Screwdriver
2 Hammer
4 Shovel
(3 row(s) affected)
--6.
----- Try to insert an explicit ID value of 3; should return a warning.
INSERT INTO dbo.Tool (ID, Name) VALUES (3, 'Garden shovel')
GO
--7.
---Results Messages:
Msg 544, Level 16, State 1, Line 1
Cannot insert explicit value for identity column in table 'Tool'
when IDENTITY_INSERT is set to OFF.
--8.
-----SET IDENTITY_INSERT to ON.
SET IDENTITY_INSERT dbo.Tool ON
GO
--9.
-----Try to insert an explicit ID value of 3.
INSERT INTO dbo.Tool (ID, Name) VALUES (3, 'Garden shovel')
GO
--10.
SELECT * FROM dbo.Tool
GO
---Results Messages:
ID Name
----------- ----------------------------------------
1 Screwdriver
2 Hammer
3 Garden shovel
4 Shovel
(4 row(s) affected)
--11.
-----Drop products table.
DROP TABLE dbo.Tool
GO
Source:
http://msdn.microsoft.com/en-us/library/ms188059.aspx