SQL SERVER – Query Hint – Contest Win Joes 2 Pros Combo (USD 198) – Day 1 of 5

SQL SERVER - Query Hint - Contest Win Joes 2 Pros Combo (USD 198) - Day 1 of 5 combos

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.

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)

Joes 2 Pros
Previous Post
SQLAuthority News – Microsoft Whitepaper – AlwaysOn Solution Guide: Offloading Read-Only Workloads to Secondary Replicas
Next Post
SQL SERVER – Identity Fields – Contest Win Joes 2 Pros Combo (USD 198) – Day 2 of 5

Related Posts

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

    Reply
  • RASHMIKA VAGHELA
    August 6, 2012 12:01 pm

    OPTION D

    Reply
  • Kalyanasundaram.K
    August 6, 2012 12:37 pm

    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.

    Reply
  • Prasanna kumar.D
    August 6, 2012 3:03 pm

    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.

    Reply
  • Ganesh narim
    August 6, 2012 5:32 pm

    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

    Reply
  • gmbalajibelaji
    August 6, 2012 5:34 pm

    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.

    Reply
  • Hi Pinal,
    I liked Querying Special Characters which was a bit tricky one and very nicely explained too.

    Reply
  • infoindiaonline
    August 6, 2012 10:20 pm

    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.

    Reply
  • Database Tutor
    August 6, 2012 10:45 pm

    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

    Reply
  • Dear Sir,

    OPtion B is the correct answer.

    Thanks,
    Fazal Vahora

    Reply
  • P.Anish Shenoy
    August 10, 2012 12:30 pm

    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

    Reply
  • 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

    Reply
  • Ans:
    B) nolock means every data will retrives whether its commited or not.
    Country: india

    Reply
  • 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

    Reply
  • Uday Bhoopalam
    August 14, 2012 2:59 am

    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

    Reply
  • Uday Bhoopalam
    August 14, 2012 10:15 pm

    Uncommitted transactions are refered to as dirty data. SQL Server manages

    Reply
  • 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

    Reply
  • NagendRa Kambli
    August 17, 2012 11:29 am

    Which of the following queries will return dirty data?
    Ans : b
    India

    Reply
  • Option B is the correct answer.This is Related to ACID Property

    Reply
  • Both A and B options are right answers.not just B .

    Reply

Leave a Reply

Menu
Exit mobile version