August 2011 we ran a contest where every day we give away one book for an entire month. The contest had extreme success. Lots of people participated and lots of give away. I have received lots of questions if we are doing something similar this month. Absolutely, instead of running a contest a month long we are doing something more interesting. We are giving away USD 198 worth gift every day for this week. We are giving away Joes 2 Pros 5 Volumes (BOOK) SQL 2008 Development Certification Training Kit every day. One copy in India and One in USA. Total 2 of the giveaway (worth USD 198). All the gifts are sponsored from the Joes 2 Pros.
The books are available here Amazon | Flipkart
How to Win:
- Read the Question
- Read the Hints
- Answer the Quiz in Contact Form in following format
- Question
- Answer
- Name of the country (The contest is open for USA and India residents only)
- 2 Winners will be randomly selected announced on August 20th.
Question of the Day:
Which of the following queries will return dirty data?
a) SELECT * FROM Table1 (READUNCOMMITED)
b) SELECT * FROM Table1 (NOLOCK)
c) SELECT * FROM Table1 (DIRTYREAD)
d) SELECT * FROM Table1 (MYLOCK)
Query Hints:
BIG HINT POST
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.
For every data change to a table there is a brief moment where the change is made in this intermediate state, but is not committed. During this time, any other DML statement (SELECT, INSERT, DELETE, UPDATE) needing that data must wait until the lock is released. This is a safety feature put in place so that SQL Server evaluates only official data.
Additional Hints:
I have previously discussed various concepts from SQL Server Joes 2 Pros Volume 1.
- SQL Joes 2 Pros Development Series – Dirty Records and Table Hints
- SQL Joes 2 Pros Development Series – Row Constructors
- SQL Joes 2 Pros Development Series – Finding un-matching Records
- SQL Joes 2 Pros Development Series – Efficient Query Writing Strategy
- SQL Joes 2 Pros Development Series – Finding Apostrophes in String and Text
- SQL Joes 2 Pros Development Series – Wildcard – Querying Special Characters
- SQL Joes 2 Pros Development Series – Wildcard Basics Recap
Next Step:
Answer the Quiz in Contact Form in following format
- Question
- Answer
- Name of the country (The contest is open for USA and India)
Bonus Winner
Leave a comment with your favorite article from the “additional hints” section and you may be eligible for surprise gift. There is no country restriction for this Bonus Contest. Do mention why you liked it any particular blog post and I will announce the winner of the same along with the main contest.
Reference: Pinal Dave (https://blog.sqlauthority.com)
21 Comments. Leave new
Option B is the correct answer. Because if u don’t put a lock on the records and at the same time if multiple users are concurrently accessing / updating the data then it will lead to the dirty reads
OPTION D
I dont think so
Hi Pinal,
I am interested to this contest. One of the winner in August 2011 contest.[Day 26]
In Addition Hints Section from SQL Server Joes 2 Pros Volume 1, i had check with all the links and my favorite link as below :
SQL SERVER – Tips from the SQL Joes 2 Pros Development Series – Finding Apostrophes in String and Text – Day 3 of 35
Reason : Most of the technical guys are just struck to face this difficulties in their working scenarios. Its simple thing but most of them feel difficult at the time of facing first time in their working environment.
Hi Pinal Dave,
SQL SERVER – Tips from the SQL Joes 2 Pros Development Series – Row Constructors – Day 6 of 35
I have seen this post. Its very nice to add more record in a single query. And also I have used in my project also.
Hi sir,
This is very interesting event and i would like “SQL Joes 2 Pros Development Series – Dirty Records and Table Hints” because as stated in that blog post your answer for the question is pretty much sure and lots of people given the correct answer but as i read the hole comments i’m very much comfortable with the question and your answer…!!!!
Thanks
Ganesh N
option A, because a dirty read occurs when a transaction is allowed to read data from a row that has been modified by another running transaction and not yet committed.
Hi Pinal,
I liked Querying Special Characters which was a bit tricky one and very nicely explained too.
Hi Pinal,
I liked “SQL Joes 2 Pros Development Series – Wildcard – Querying Special Characters” which was a bit tricky one and very nicely explained too.
Option B is the right answere becoz in sql server we use “with ()” for giving query hint in query.In above example its also giving query hint to read dirty records with nolock
Dear Sir,
OPtion B is the correct answer.
Thanks,
Fazal Vahora
Hi Sir,
SQL Joes 2 Pros Development Series – Wildcard – Querying Special Characters
and
SQL Joes 2 Pros Development Series – Dirty Records and Table Hints
are my favourite links that is in my book marks.. :)
Just loved the way the concept is explained
Regards,
Anish Shenoy,
Quadwave,
Bangalore
Hi Pinal,
I liked the additional hint – SQL Joes 2 Pros Development Series – Dirty Records and Table Hints as in this blog it is mentioned very clear for dirty records.
Best Regards,
S Kumar
Ans:
B) nolock means every data will retrives whether its commited or not.
Country: india
Question :
Which of the following queries will return dirty data?
b) SELECT * FROM Table1 (NOLOCK)
Favorite Article:
SQL Joes 2 Pros Development Series – Wildcard – Querying Special Characters
question: Which of the following queries will return dirty data?
Answer is B: Select * from Table1 (NOLOCK) will return dirty data.
Country: USA
Thank You
Uday
Uncommitted transactions are refered to as dirty data. SQL Server manages
Which of the following queries will return dirty data?
Ans : a And b
USA
From MSDN NOLOCK Is equivalent to READUNCOMMITTED
https://docs.microsoft.com/en-us/sql/t-sql/queries/hints-transact-sql-table?view=sql-server-2017
Which of the following queries will return dirty data?
Ans : b
India
Option B is the correct answer.This is Related to ACID Property
Both A and B options are right answers.not just B .