SQL SERVER – Identity Fields Review – SQL Queries 2012 Joes 2 Pros Volume 2 – The SQL Query Techniques Tutorial for SQL Server 2012

This chapter is abstract from the Beginning SQL 2012 – Joes 2 Pros Volume 2

Book On Amazon | Book On Flipkart

Kit on Amazon | Kit on Flipkart

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 from 300 pages to 600 pages. This book is designed to make the query question and query skills in the professional work seem must easier.

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.

All supporting files are available with a free download from the www.Joes2Pros.com web site. This example is from the SQL 2012 series Volume 2 in the file SQLQueries2012Vol2Chapter3.2Setup.sql. If you need help setting up then look in the “Free Videos” section on Joes2Pros under “Getting Started” called “How to install your labs”

Identity Fields Review

The CurrentProducts 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')

Messages
Msg 8101, Level 16, State 1, Line 1
An explicit value for the identity column in table ‘CurrentProducts’ can only be specified when a column list is used and IDENTITY_INSERT is ON.

0 rows

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')

Messages
(1 row(s) affected)

0 rows

 

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')

Messages
Msg 8101, Level 16, State 1, Line 2
An explicit value for the identity column in table ‘CurrentProducts’ can only be specified when a column list is used and IDENTITY_INSERT is ON.

0 rows

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

Messages
Command(s) completed successfully.

0 rows

Book On Amazon | Book On Flipkart

Kit on Amazon | Kit on Flipkart

Click to Download Scripts

Reference: Pinal Dave (http://blog.SQLAuthority.com)

About these ads

3 thoughts on “SQL SERVER – Identity Fields Review – SQL Queries 2012 Joes 2 Pros Volume 2 – The SQL Query Techniques Tutorial for SQL Server 2012

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s