SQL SERVER – Tips from the SQL Joes 2 Pros Development Series – Dirty Records and Table Hints – Day 7 of 35

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

Dirty Records Recap

Most SQL people know what a “Dirty Record” is. You might also call that an “Intermediate record”. In case this is new to you here is a very quick explanation. The simplest way to describe the steps of a transaction is to use an example of updating an existing record into a table. When the insert runs, SQL Server gets the data from storage, such as a hard drive, and loads it into memory and your CPU. The data in memory is changed and then saved to the storage device. Finally, a message is sent confirming the rows that were affected.

For a very short period of time the update takes the data and puts it into memory (an intermediate state), not a permanent state. For every data change to a table there is a brief moment where the change is made in the intermediate state, but is not committed. During this time, any other DML statement needing that data waits until the lock is released. This is a safety feature so that SQL Server evaluates only official data.

Basic Table Hints

Some transactions take time and then rollback. In other words, the changes never become official data and in essence never took place. The example below shows a transaction that takes 15 seconds and then rolls back:

BEGIN TRAN
UPDATE
dbo.Employee
SET HireDate = '1/1/1992'
WHERE EmployeeID = 1
WAITFOR DELAY '00:00:15'
ROLLBACK TRAN

If employee #1 was really hired in 1989 and you run the above code, you have incorrect data for fifteen seconds in the intermediate state. During those fifteen seconds, if a 401K vesting program ran an evaluation on all employees hired before 1990, the process would wrongfully overlook employee #1. The safety catch is the 401K process would wait until this transaction is done in order to get the official data.

A great deal of data can be changed in the intermediate state, but never get committed. Locking prevents other processes from making decisions on dirty data. The result is that only committed data is used to isolate transactions. The drawback is that some processes that could run instantly now have to wait. Locking lowers the level of concurrency, which is the ability to allow software to run many processes at the same time.

A Select query can now pull out any record being changed in the intermediate state. You could say that a query will not show any dirty records because all transactions are isolated from each other. By changing one record in your CurrentProducts table, a query looking for records from that table will have to wait. In the figure below the update statement was executed first and puts data into an intermediate state. The query on the right waits for the transaction to be committed.

If you look more closely you can see that the update statement has been running for one minute, 38 seconds and the query on the right was started six seconds later. The committed RetailPrice is $75 and the RetailPrice in the intermediate state of that value is $999. This will be turn two and a half minutes. Upon completion, the price never changed and the $999 value was thrown away (never persisted). The query on the right waits until the data becomes official. This is a safety feature that has a downside in that the select query takes much longer to run.

If you don’t want the query on the right to wait, you have some choices. If you are fine with the query on the right accessing dirty data, you can issue a locking hint for the query. The following code tells the query to run without waiting:

SELECT *
FROM CurrentProducts (READUNCOMMITTED)

In the code example would run without waiting. The result is the query would show the RetailPrice value of $999.00.

In the figure below we see ProductID 1 has a value in the RetailPrice field of $999.00 in the result set. The query did not wait and ran with the newest value it found in memory. In the end the $999.00 was never a committed value in the database. Any evaluation of data running this query could give you a false report. In other words you run the same query three minutes later and get a different result. This is despite the fact that the table may not have really changed at all in that time.

The READUNCOMMITTED table hint allows the query to show uncommitted (dirty) data. The advantage is the query runs much faster. This is a common solution for evaluation queries that don’t need to be exact. A common example is just looking to see how many records are in a table as in the query below:

SELECT COUNT(*)
FROM CurrentProducts (READUNCOMMITTED)

You know this result changes over time and is used for general trends. In this case, SQL Server allows the query to run without waiting. The following statements are equivalent and show a shorter way of accomplishing the same result:

SELECT COUNT(*) FROM CurrentProducts (READUNCOMMITTED)
SELECT COUNT(*) FROM CurrentProducts (NOLOCK)

Readuncommited and nolock are just two of many Table hints used by SQL server to affect how a queries isolcation level can be affected.

Note: If you want to setup the sample JProCo database on your system you can watch this video.

Question 7

Q 7) What happens when you try to query records in the intermediate state?

  1. Your query waits until the data is no longer in the intermediate state.
  2. Your query runs normally.
  3. Your query splits the results into permanent and intermediate results.

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 United States will get Joes 2 Pros Volume 1.
Winner from India will get Joes 2 Pros Volume 1.
The contest is open till next blog post shows up at http://blog.sqlauthority.com which is next day GTM+2.5.

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

About these ads

92 thoughts on “SQL SERVER – Tips from the SQL Joes 2 Pros Development Series – Dirty Records and Table Hints – Day 7 of 35

  1. Q 7) What happens when you try to query records in the intermediate state?
    correct answer –
    1. Your query waits until the data is no longer in the intermediate state.

    Shekhar Gurav.
    country : India

    Like

  2. Q 7) What happens when you try to query records in the intermediate state?

    A. 1.Your query waits until the data is no longer in the intermediate state.

    Gordon Kane
    Allen TX
    USA

    Like

  3. Q 7) What happens when you try to query records in the intermediate state?

    1) Your query waits until the data is no longer in the intermediate state.

    Leo Pius
    USA

    Like

  4. The answer is very much clear in your own blog post. if we are mentioning READUNCOMMITTED or NOLOCK table hint, it will show are dirty records too otherwise, it will wait till lock release records so the answer should be 1st in your choice list, which is:

    “Your query waits until the data is no longer in the intermediate state.”

    Like

  5. The answer is very much clear in your own blog post. if we are mentioning READUNCOMMITTED or NOLOCK table hint, it will show are dirty records too otherwise, it will wait till lock release records so the answer should be 1st in your choice list, which is:

    “Your query waits until the data is no longer in the intermediate state.”

    Ritesh (India)

    Like

  6. If there are no hints on this query, then the correct answer will be 1 – waits till data are no longer in the intermediate state.

    I am from USA>

    Like

  7. the correct answers is option : 1 ie.

    “Your query waits until the data is no longer in the intermediate state.”

    because we did not specify “nolock or readuncommitted”
    so the query will wait until data is commited

    India

    Like

  8. Answer is # 1.

    “Your query waits until the data is no longer in the intermediate state.”

    If the CHECKPOINT option has been given, then an Intermediate Data is committed and it is available for the Query.

    Gopalakrishnan Arthanarisamy
    Bangalore, India

    Like

  9. Pingback: SQL SERVER – Win a Book a Day – Contest Rules – Day 0 of 35 Journey to SQLAuthority

  10. Hi,

    Q 7) What happens when you try to query records in the intermediate state?

    1) Your query waits until the data is no longer in the intermediate state.

    when one query is running on that time table getting lock so all data of that table are in intermediate state. so up-till locked wont get release other query need to waits until data are in intermediate state.

    I am from India..

    Like

  11. Q 7) What happens when you try to query records in the intermediate state?

    Correct Ans:
    1) Your query waits until the data is no longer in the intermediate state.

    Jatin

    INDIA

    Like

  12. What happens when you try to query records in the intermediate state?

    1) Your query waits until the data is no longer in the intermediate state.

    Above answer is the reason to query intermediate state.

    Chennai, Tamilnadu, India

    Like

  13. Q 7) What happens when you try to query records in the intermediate state?

    Option 1 Correct:- Your query waits until the data is no longer in the intermediate state.(By Default SQL Server locks the object in Transaction so any other transaction on same object have to wait until the 1st transaction completes)

    Thank you,
    Yeou Sunn from Bhilai, Chhattisgarh, India.

    Like

  14. if we are mentioning READUNCOMMITTED or NOLOCK table hint, it will show are dirty records too otherwise, it will wait till lock release records so the answer should be 1st in your choice list, therefore the correct is
    1.Your query waits until the data is no longer in the intermediate state

    I’m from INDIA

    Like

  15. Actually the answer depends on the default isolation level. If the default isolation level is READ COMMITTED the answer is 1.

    If the default isolation level is READ COMMITTED SNAPSHOT (as in SQL Azure) the answer is 2

    Roji P Thomas

    Like

  16. The right answer is No. 2, the query runs normally.

    This is because according to the question as is written, NOLOCK or READUNCOMMITTED have not been used, so the query doesn’t have to wait until the data is no longer in the intermediate state.

    Now, if NOLOCK or READUNCOMMITTED are implicitly used due to the subject, then right answer is No. 1.

    Rene Castro
    El Salvador

    Like

  17. when you try to query records in the intermediate state?
    In this condition correct answer is:
    “Your query waits until the data is no longer in the intermediate state.”
    Option No 1.

    Thanks,
    Rajneesh Verma
    INDIA

    Like

  18. Option 1

    1.Your query waits until the data is no longer in the intermediate state.

    Explaination :
    Read committed (Database Engine default Isolation level) which prevents the dirty read.

    Thanks
    Shree

    Bangalore India

    Like

  19. The Correct answer for the above question is Option 1

    “Your query waits until the data is no longer in the intermediate state.”

    Since we did not specify NOLOCK or READUNCOMMITTED the query waits on the initial transaction so it will wait to get the data when it is in normal state comes out the intermediate state.

    DILIP KUMAR JENA
    Country : INDIA

    Like

  20. The answer is #1. Your query waits until the data is no longer in the intermediate state.

    The query is not using the READUNCOMMITTED table hint, so it will not run while the data is in the intermediate state.

    I’m from Michigan, in the USA

    Like

  21. Q 7) What happens when you try to query records in the intermediate state?

    Option 1. Your query waits until the data is no longer in the intermediate state
    is correct answer.

    From,
    Malay Shah
    City: Ahmedabad
    Country:India

    Like

  22. If we have not specify (NoLock) or (ReadUncommitted) then the answer is
    answers is :

    1 )Your query waits until the data is no longer in the intermediate state.

    The query is not using the READUNCOMMITTED table hint, so it will not run while the data is in the intermediate and waits until the data becomes official

    Location: India

    Like

  23. 1. “Your query waits until the data is no longer in the intermediate state.”

    As SQL as default isolation of Read committed to prevent Intermediate state

    -Annie
    Bangalore
    India

    Like

  24. The correct answer is option 1

    Your query waits until the data is no longer in the intermediate state.

    Unless a query hint such as (NOLOCK) or (READUNCOMMITTED) the query will wait for any lock to be released on the data being retrieved.

    Country of Residence: USA

    Like

  25. Option 1 is the correct answer

    !.Your query waits until the data is no longer in the intermediate state.

    Rahul Singh rathore

    Country: india

    Like

  26. Hi Pinal,

    Challenge:
    Q 7) What happens when you try to query records in the intermediate state?

    Correct Answer:
    The first choice is the correct answer:
    1.Your query waits until the data is no longer in the intermediate state.

    Explanation:
    If your query didn’t have table hints (nolock, or readuncommitted) the query would wait until the data is no longer in an intermediate, dirty state.

    Country:
    United States

    Thanks for the knowledge!

    Regards,

    Bill Pepping

    Like

  27. Hi Sir,

    The answer is option 1 : “The query waits until the data is no longer in the intermediate state.”

    If the query transactions is with “ISOLATION” property then it will not see the data in an intermediate state.

    Anish Shenoy
    INDIA,Bangalore,Karnataka

    Like

  28. Pingback: SQL SERVER – The SQL Hands-On Guide for Beginners – Book Available for SQL Server Certification Journey to SQLAuthority

  29. Q 7) SQL SERVER – Tips from the SQL Joes 2 Pros Development Series – Dirty Records and Table Hints – Day 7 of 35

    Since the query may be waiting for data to leave the intermediate state and the results will not contain intermediate results both (2) and (3) are incorrect. Because SQL needs to ensure data integrity your query waits until the data is no longer in the intermediate state so (1) is correct.

    Winner from USA: Bill Pepping

    Winner from India: Nakul Vachhrajani

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

    Like

  30. If NOLOCK or READUNCOMITED is NOT specified then Query 1 is true, as Query has to wait until that transaction is get commited.

    If NOLOCK OR READUNCOMITED is specified, then query runs normally, as we specifically defined that, it is ok if SQL SERVER Returns DIRTY DATA.

    Country: INDIA

    Like

  31. Hi Pinal Sir,

    The correct answer for this question is option no. 1:

    ” Your query waits until the data is no longer in the intermediate state. “

    Like

  32. Answer is #1:
    Your query waits until the data is no longer in the intermediate state.

    But if you specify the READUNCOMMITTED clearly in the query, then the dirty (intermediate state) data will be reached.

    Thanks,
    Wayne

    Like

  33. Hi Pinaldave,

    I actually had a question it… when tried to do the same my select query was not waiting for 15sec, its immediatley returning the uncommitted data without the READUNCOMITED keyword, pls let me know where i was wrong…

    Thanks
    Padma

    Like

  34. Your query waits until the data is no longer in the intermediate state.

    Since we did not specify NOLOCK or READUNCOMMITTED the query waits on the initial transaction

    India

    Like

  35. Pingback: SQL SERVER – Query Hint – Contest Win Joes 2 Pros Combo (USD 198) – Day 1 of 5 « SQL Server Journey with SQL Authority

  36. Pingback: SQL SERVER – Weekly Series – Memory Lane – #041 | Journey to SQL Authority with Pinal Dave

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