SQL SERVER – Identity Fields Review – The SQL Query Techniques Tutorial for SQL Server 2012

In this blog post we are going to learn about Identity Fields Review.

Why buy this Book: The beta of this book actually existed for a year and was tested and used in my classroom. Its purpose back then was to help the students do the steps individually that led to the skills where they could all pass the Microsoft test. It worked and then many went out for their SQL interview. They did good enough to get the job, but they told me about one or two questions they were not able to answer. A year of collecting this data and turning them into lessons doubled the size of this book of 300 pages to 600 pages. This book is designed to make the query question and query skills in the professional work must seem easier. Let us learn about Identity Fields Review.

What will I learn after reading this book: Query Aggregations, TOP, Ranked, Tiles queries, Set operators, CTEs, recursion, basic and correlated subqueries, Using MERGE and OUTPUT.

Identity Fields Review

The Current Products table has 480 records and the next records will be ProductID 481. ProductID is the first field and it is an identity field. SQL Server generates when you attempt to enter a value into the identity field.

INSERT INTO CurrentProducts VALUES
(481, 'Yoga Mtn Getaway 5 Days', 875, '9/1/2009', 0, 'Medium-Stay')

Remove the 481 value from the insert statement and then insert the record with the following statement.

INSERT INTO CurrentProducts VALUES
('Yoga Mtn Getaway 5 Days', 875, '9/1/2009', 0, 'Medium-Stay')

Product 481 has been added.

SELECT * FROM CurrentProducts

Now let’s delete the yoga product, in order to simulate the deletion scenario.

DELETE FROM CurrentProducts
WHERE ProductName LIKE '%yoga%'

Next, attempt to reinsert the yoga product again we just deleted. Note the identity property of the ProductID field keeps track of all records you insert and delete. It knows most recent inserted record was 481. This next statement will get a ProductID of 482.

INSERT INTO CurrentProducts VALUES
('Yoga Mtn Getaway 5 Days',875.00,'9/1/2009',0,
'Medium-Stay')
SELECT * FROM CurrentProducts

Overriding Identity Fields

We don’t want this gap between ProductID values from 480 to 482. We want the yoga product to retain its original ProductID values of 481. The deleted records have been re-inserted causing SQL Server to jump ahead to the next unused ProductID values.

DELETE FROM CurrentProducts
WHERE ProductName LIKE '%yoga%'

To accomplish this goal, we must temporarily halt the automatic identity and insert our record. Then we can manually assign the correct ProductID values. Delete the Yoga record again so we can try another type of insertion. Our next step is to temporarily set the IDENTITY_INSERT property to ON. Now we will attempt to run the INSERT statement for the yoga record.

SET IDENTITY_INSERT CurrentProducts ON
INSERT INTO CurrentProducts VALUES
(481,'Yoga Mtn Getaway 5 Days',875.00,'9/1/2009',0,
'Medium-Stay')

The error message prompts us to include a column list whenever we manually insert record(s) to a table with an identity field. In other words, when manually inserting records, we have to pass the values by name and not position. It is required to only add data in specified columns. In other words find the names of all the fields in the table and list them in parenthesis after the INSERT INTO keywords but before the VALUES keyword.

After you’ve added the column list, enclose the list in parentheses and move the keyword VALUES to follow the column list. Now reattempt to insert the yoga record. After SQL Server gives you confirmation (“1 row(s) affected”).

SET IDENTITY_INSERT CurrentProducts ON
INSERT INTO CurrentProducts (
ProductID, ProductName, RetailPrice, OriginationDate, ToBeDeleted, Category) VALUES
(481,'Yoga Mtn Getaway 5 Days',875.00,'9/1/2009',0,
'Medium-Stay')

Run a SELECT statement and confirm the yoga records show in the table.

SELECT * FROM CurrentProducts

SQL Server will allow you to utilize IDENTITY_INSERT with just one table at a time in your database. After you’ve completed the needed work, it’s very important to reset the IDENTITY_INSERT back to OFF. A best practice recommendation is to write the OFF statement as a reminder at the same time you write the ON statement.

SET IDENTITY_INSERT CurrentProducts OFF

Reference: Pinal Dave (https://blog.sqlauthority.com)