SQL SERVER – Tips from the SQL Joes 2 Pros Development Series – Overridding Identity Fields – Tricks and Tips of Identity Fields – Day 9 of 35

pinaldave

5.00/5 (1 vote)

9 Aug 2011CPOL5 min read

15.4K

SQL SERVER – Tips from the SQL Joes 2 Pros Development Series

Answer a simple quiz at the end of the blog post and -
Every day, one winner from India will get Joes 2 Pros Volume 2.
Every day, one winner from the United States will get Joes 2 Pros Volume 2.

Identity Fields

For students new to the database world, it helps to begin thinking about ID fields in the context of larger organizations with lots of activity. A customer service department has a constant flow of activity and many representatives are entering data in the system simultaneously. The same is true for large billing departments. These are examples where an identity field helps to ensure the entities you care about get tracked properly. A CustomerID value that is automatically generated with each new record makes sure each new customer gets a unique number – even if you have many reps all entering data at the same time.

The CustomerID field is one we wouldn’t want accidentally duplicated, altered, or deleted. Similarly, the billing department would not want to have mistaken entries in the InvoiceID field. A missing InvoiceID could indicate a serious error (e.g., a customer wouldn’t get billed and JProCo wouldn’t get paid for that order) or even fraud. A duplicate InvoiceID value might result in a customer’s payment being applied to the wrong customer.

Identity fields help prevent these unwanted scenarios of ambiguity. For larger tables, like JProCo’s CurrentProducts, having ProductID as an identity field ensures each ProductID value will be unique and sequential. It also saves JProCo’s product managers from having to track down which ProductID to use each time they quickly need to add new products.

One hint about the identity property is to count the number of times we’ve used the words “large” and “active” in this section. For large tables where new records get added daily, the identity property saves time and helps enforce data integrity. But with smaller tables where records don’t often change, using the identity property to create your ID field is unnecessary and its automatic incrementing can make extra work for you.

Tables where records are frequently deleted also make poor candidates for the identity property. In our next example, we will see that an identity field’s ability to auto-increment and keep track of the next expected value can require extra maintenance tasks when fields are deleted. The example we're going to use is the CurrentProducts table which has 480 records (see figure below).

Click on Images to see it in original size.

There are exception cases when you will need to alter a value in the identity field. When training a new database user, you might temporarily allocate them a few empty invoice records to practice on. Later the practice records will be deleted, but you’ll want to make sure your next invoice numbers appears in proper sequence. The ProductID field is auto populated each time a record is created since it has an identity property set to count by 1.

Let’s step through an example and pretend we don’t already know that ProductID field is an identity field, so we can read the error message SQL Server generates when you attempt to enter an ID into the identity field. Note: There is a reason this example is inserting by position and not by name and we will get to that later.

Notice we tried to enter 481 instead of letting SQL pick the next value? This results in an error message.

When you remove the 481 value (ProductID) from the code, then the insert statement works correctly.

Check to see you have ProductID 481 inserted at the “Yoga Mtn Getaway 5 Days”. Once verified, delete all the yoga products, in order to simulate the accidental deletion scenario.

Overriding Identity Fields

If you run the insert statement again, you won’t get ProductID 481. You will get 482. There is no 481 and there won’t be unless you take charge and put it there.

Our next goal it to re-insert this Yoga trip with a value of 481. To do our next step, we need to temporarily set the IDENTITY_INSERT property to ON. We successfully ran the first command setting IDENTITY_INSERT to ON for the CurrentProducts table. We next attempted to run the INSERT statement for the three yoga records.

Click on Images to see it in original size.

The error message prompts us to include a column list whenever we manually insert records 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.

SQL Server will allow you to utilize IDENTITY_INSERT with just one table at a time. After you’ve completed the needed work, it’s very important to reset the IDENTITY_INSERT back to OFF. Just to check it worked, we ran a SELECT statement and confirm the yoga records show in the table and can see the last record has a ProductID of 481.

Note: If you want to setup the sample JProCo database on your system, you can watch this video. For this post, you will want to run the SQLQueriesChapter3.0Setup.sql script from Volume 2.

Question 9

You need to explicitly insert a value into an identity field for the SalesInvoice table. What two things must you do in order for your insert statement to successfully execute? (Choose two.)

Turn the IDENTITY_INSERT to ON for the SalesInvoice table
Turn the IDENTITY_INSERT to OFF for the SalesInvoice table
Insert your values by position
Insert your values by name

Please post your answer in the comment section to win Joes 2 Pros books.

Rules

Please leave your answer in the comment section below with the correct option, explanation and your country of residence.
Every day, one winner will be announced from the United States.
Every day, one winner will be announced from India.
A valid answer must contain country of residence of the person who is answering.
Please check my Facebook page for winners name and correct answer.
Winner from the United States will get Joes 2 Pros Volume 2.
Winner from India will get Joes 2 Pros Volume 2.
The contest is open till my next blog post shows up at http://blog.sqlauthority.com which is next day GTM+2.5.