Despite your numerous explanations about the
value of identity columns and the usefulness of their arbitrary
values, some of your coworkers insist upon sequential PKs within a
given table. Then, when invoice numbers are missing, they panic,
fearing litigation, fraud, or worse.
To appreciate the problem, create a table with
an identity column and populate it with a few rows:
— Create a test table.
CREATE TABLE TestIdentityGaps
(
ID int IDENTITY
PRIMARY KEY,
Description
varchar(20)
)
GO
— Insert some values. The word INTO is optional:
INSERT [INTO] TestIdentityGaps (Description) VALUES (‘One’)
INSERT [INTO] TestIdentityGaps (Description) VALUES (‘Two’)
INSERT [INTO] TestIdentityGaps (Description) VALUES
(‘Three’)
INSERT [INTO] TestIdentityGaps (Description) VALUES (‘Four’)
INSERT [INTO] TestIdentityGaps (Description) VALUES (‘Five’)
INSERT [INTO] TestIdentityGaps (Description) VALUES (‘Six’)
GO
Now, delete a couple of rows:
DELETE TestIdentityGaps
WHERE Description IN(‘Two’, ‘Five’)
Since we wrote the code, we know that values
‘Two’ and ‘Five’ are missing. We want to insert two rows to fill
these gaps. Two simple INSERT statements won’t fill the bill;
rather, they’ll create PKs at the end of the sequence.
INSERT [INTO] TestIdentityGaps (Description) VALUES
(‘Two Point One’)
INSERT [INTO] TestIdentityGaps (Description) VALUES (‘Five Point
One’)
GO
SELECT * FROM TestIdentityGaps
You also can’t explicitly set the value of an
identity column:
— Try inserting an explicit ID value of 2. Returns
a warning.
INSERT INTO TestIdentityGaps (id, Description) VALUES(2, ‘Two
Point One’)
GO
As a way to work around this problem, SQL
Server 2000 provides the setting IDENTITY_INSERT. To force the
insertion of a row with a specific value, issue the command and
then follow it with your specific inserts:
SET IDENTITY_INSERT TestIdentityGaps ON
INSERT INTO TestIdentityGaps (id, Description) VALUES(2, ‘Two
Point One’)
INSERT INTO TestIdentityGaps (id, Description) VALUES(5, ‘Five
Point One’)
GO
SELECT * FROM TestIdentityGaps
Now you can see that the new rows have been
inserted using the specified primary keys.
Note: The IDENTITY_INSERT
setting applies to one table within a database at any given moment.
If you need to plug gaps in more than one table, you must
explicitly identify each table with a specific command.
You can insert specific values into a table
with an Identity column, but, to do so, you must first set the
IDENTITY_INSERT value to ON. If you don’t, you’ll receive an error
message. Even if you set the IDENTITY_INSERT value to ON and then
attempt to insert an existing value, you’ll receive an error
message.
TechRepublic’s free SQL Server newsletter, delivered each Tuesday, contains hands-on tips that will help you become more adept with this powerful relational database management system. Automatically subscribe today!