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 Koenig Training Solution and Joes 2 Pros.

The books are available here Amazon | Flipkart | Indiaplaza

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 (http://blog.sqlauthority.com)

Filed under: Joes 2 Pros, PostADay, SQL, SQL Authority, SQL Puzzle, SQL Query, SQL Server, SQL Tips and Tricks, T SQL, Technology