SQL SERVER – Tips from the Development Series – Overriding Identity Fields – Day 9 of 35

In this blog post we are going to discuss about Overriding Identity Fields.

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 were going to use is the CurrentProducts table which has 480 records (see figure below).

Click on Images to see it in in original size.

SQL SERVER - Tips from the Development Series - Overriding Identity Fields - Day 9 of 35 j2p_9_1

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.

SQL SERVER - Tips from the Development Series - Overriding Identity Fields - Day 9 of 35 j2p_9_2

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.

SQL SERVER - Tips from the Development Series - Overriding Identity Fields - Day 9 of 35 j2p_9_3

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

SQL SERVER - Tips from the Development Series - Overriding Identity Fields - Day 9 of 35 j2p_9_4

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.

SQL SERVER - Tips from the Development Series - Overriding Identity Fields - Day 9 of 35 j2p_9_5

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.

SQL SERVER - Tips from the Development Series - Overriding Identity Fields - Day 9 of 35 j2p_9_6

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 in original size.

SQL SERVER - Tips from the Development Series - Overriding Identity Fields - Day 9 of 35 j2p_9_7

In 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 - Tips from the Development Series - Overriding Identity Fields - Day 9 of 35 j2p_9_8

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)

  1. Turn the IDENTITY_INSERT to ON for the SalesInvoice table
  2. Turn the IDENTITY_INSERT to OFF for the SalesInvoice table
  3. Insert your values by position
  4. Insert your values by name

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

Rules:

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

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

Joes 2 Pros, SQL Identity, SQL Scripts, SQL Server
Previous Post
SQL SERVER – Tips from the SQL Joes 2 Pros Development Series – Many to Many Relationships – Day 8 of 35
Next Post
SQL SERVER – The SQL Hands-On Guide for Beginners – Book Available for SQL Server Certification

Related Posts

110 Comments. Leave new

  • Aditya Bisoi (@AdityaBisoi07)
    August 9, 2011 11:47 pm

    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)

    1-Turn the IDENTITY_INSERT to ON for the SalesInvoice table

    4-Insert your values by name

    Chennai, INDIA

    Reply
  • Partha Dutta Gupta
    August 10, 2011 12:20 am

    If I need to explicitly insert a value into an identity field for the SalesInvoice table,
    i need to perform the following two steps:
    1.)Turn the IDENTITY_INSERT to ON for the SalesInvoice table
    2). Since when manually inserting records to a table with an identity field, we have to pass the values by name and not position thus my next step would be:
    Insert values by name

    Thus the answers would be:
    1. Turn the IDENTITY_INSERT to ON for the SalesInvoice table
    4. Insert your values by name.

    After the work is complete it is very important to turn the IDENTITY_INSERT off.

    My country for Residence is — India

    Reply
  • The 2 things you must do to explicitly insert a value into an identity field for the SalesInvoice table are:

    SET IDENTITY_INSERT SalesInvoice ON
    and
    Insert your values by name.

    This corresponds to answers 1 and 4.

    David
    USA

    Reply
  • dilipkumarjena
    August 10, 2011 1:34 am

    The Correct answers the above question is
    Option 1 and 4.

    1)Turn the IDENTITY_INSERT to ON for the SalesInvoice table
    4)Insert your values by name

    Explanation:

    When we want to explicitly insert a value into an identity field for the SalesInvoice table first we need to enable it or set it to on as described by the option 1.

    Then when it comes to inserting values we cannot insert values into the table with reference to the identity field because it automatically gets inserted.
    Hen the other option we are remain is the 4th option by name and when we do that we insert into the table easily.

    Hence 4th and 1 st option is correct.

    DILIP KUMAR JENA
    Country : INDIA

    Reply
  • Actually 3 steps.
    First, Option 1: Turn the IDENTITY_INSERT to ON for the SalesInvoice table. (Allows values to be explicitly inserted into the ID column.)

    Second, Option 4: Insert your values by name. (Required by SQL Server syntax. Use “Script Table as” feature to get list of column names. Use “SELECT” option not “INSERT” option.)

    Third, Option 2: Turn the IDENTITY_INSERT to OFF for the SalesInvoice table. (You are the guardian of the data. Be tidy!)

    Country: United States of America

    Reply
  • Venkataraman R (@venkataramanr)
    August 10, 2011 3:35 am

    Option 1 and Option 4. We need to enable identity insert on the table and also we need to insert values into the table with column names, for the identity insertion to Succeed.

    Venkataraman R
    USA

    Reply
  • The answer is:
    1 – Turn the IDENTITY_INSERT to ON for the SalesInvoice table
    4 – Insert your values by name

    Thanks!
    Jerry – USA

    Reply
  • A. Arul Prakash
    August 10, 2011 4:15 am

    The correct options are 1 and 4

    In order to insert any value where in table any identity column is defined first you need to set “IDENTITY_INSERT ON” then need to insert your desired value. After this you also need to set the “IDENTITY_INSERT TO OFF”.

    Reply
  • A. Arul Prakash
    August 10, 2011 4:15 am

    The correct options are 1 and 4

    In order to insert any value where in table any identity column is defined first you need to set “IDENTITY_INSERT ON” then need to insert your desired value. After this you also need to set the “IDENTITY_INSERT TO OFF”.

    Country : USA

    Reply
  • Turn the IDENTITY_INSERT to ON for the SalesInvoice table
    Insert your values by name

    Have to turn the IDENTITY_INSERT on as we want to explicitly insert the value into the identity column

    insert the values by name as sql server through an error if tried to insert by position

    USA

    Reply
  • Jungchan Hsieh
    August 10, 2011 7:54 am

    1.Turn the IDENTITY_INSERT to ON for the SalesInvoice table
    4.Insert your values by name

    Texas, USA

    Reply
  • The correct options are 1 and 4

    In order to insert any value where in table any identity column is defined first you need to set “IDENTITY_INSERT ON” then need to insert your desired value. After this you also need to set the “IDENTITY_INSERT TO OFF”.

    Amit Bansal
    INDIA
    SONIPAT

    Reply
  • Option 1, 4 are correct
    But After Successful insertion we need to set IDENTITY_INSERT OFF

    Reply
  • sachin kulshreshtha
    August 10, 2011 2:08 pm

    Answer is :1. Turn the IDENTITY_INSERT to ON for the SalesInvoice table

    4. Insert your values by name

    Country:India

    Reply
  • sikha rani mahapatro
    August 10, 2011 4:16 pm

    Answer: Option 1 & 4

    1) .Turn the IDENTITY_INSERT to ON for the SalesInvoice table

    4).Insert your values by name

    Country: India(sikha)

    Reply
  • the answer i option 1 and Option 4

    kaushik
    india

    Reply
  • Krishnakant Joshi
    August 10, 2011 7:01 pm

    The correct answers are

    1) Turn the IDENTITY_INSERT to ON for the SalesInvoice table

    4) Insert your values by name

    –As we have to explicitly insert value in the identity column the IDENTITY_COLUMN need to be set on and to overcomecerror ‘ An explicit value for the identity column in table SalesInvoice can only be specified when a column list is used and IDENTITY_INSERT is ON’ we need to insert the values by name

    Name: Krishnakant Joshi
    Country: INDIA

    Reply
  • Merrill Aldrich
    August 11, 2011 11:31 pm

    Nit pick: the correct term is not “Field,” it’s “Column.” A field is a box on a form where you provide user input. A column is part of a table in a database. Usually one populates the other :-)

    Otherwise, great idea for a post!

    Reply
  • The answers are :

    1.Turn the IDENTITY_INSERT to ON for the SalesInvoice table
    4.Insert your values by name

    Francisco,
    Miami

    Reply
  • Yesterday the RANDOM algorithm picked two winners from USA so
    today – we will have additional winner from India who will win
    Pluralsight FREE subscription.

    Pluralsight Subscription Winner

    Winner from India: Ritesh Shah

    I thank you all for participating here. The permanent record of this update is posted on facebook page.

    Reply

Leave a Reply