SQL SERVER – Who needs ETL Version Control?

While making some changes (read: mistakes) to my ETL business logic the other day, it occurred to me much too late that those unfortunate changes had replaced the once properly working logic with now very flawed logic.  The good news was that I remembered what the working logic was supposed to be.  The bad news, I had to re-create it.  Had I had the working logic already checked-in under version control, I could have saved myself the two hours of wasted time and effort.  In an ETL team development setting, these types of issues could easily multiply and significantly impede developer progress and productivity.

Version control makes it possible to have multiple people developing together and working on same project.  If I am working on something, a fellow ETL developer will not be able to check it out and make changes to it until I am finished with my changes and check those back in.   This addresses the common situation where one developer’s project artifact and code changes clobber that of another developer by accident.

Another reason to have version control is that it gives the developer confidence to try different modifications to the logic without having to worry about a negative impact to the previously developed logic.   Sure, I could always save off my work and create a copy to try things out on.  However, in my experience, I would wind up with various different copies all over the place and it would then become difficult to keep track of what’s what.  Version control allows me to capture and document relevant information about whatever I am checking in.

Finally, version control maintains a history of changes that have been made to the application logic.  In some environments, this information and traceability can prove extremely valuable if not necessary.   Ever wonder when a bug was introduced into the application logic?

Click to Enlarge

The expressor Studio desktop ETL tool allows developers to seamlessly check-in and -out project artifacts to expressor’s Repository, which supports version control and is available as part of their Standard Edition product offering.

Many data integration solutions do not have built-in version control.   Most offer some way to interface to version control systems, but expressor has it built-in with their Standard Edition product offering.   I requested a trial license to play with it and was pleasantly surprised at expressor’s seamless version control integration.  There was no need to do any separate 3rd party download, install, and configuration.  With expressor, check-in and check-out was almost transparent.  As someone who has developed ETL solutions, I really appreciated the clean and simple approach expressor has taken.   I have no doubt this would help make any ETL project go smoother and faster.

You can request a free 30-day trial of the expressor Standard Edition, or download their free expressor Studio community ETL tool at their site.

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

About these ads

SQL SERVER – Tips from the SQL Joes 2 Pros Development Series – Aggregates with the Over Clause – Day 10 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 2.
Every day one winner from United States will get Joes 2 Pros Volume 2.

Aggregates with the Over Clause

You have likely heard the business term “Market Share”. If your company is the biggest and has sold 15 million units in an industry that has sold a total of 50 million units then your company’s market share is 30% (15/50 = .30). Market share represents your number divide by the sum of all other numbers. In JProCo the biggest grant (Ben@Moretechnology.com) is $41,000 and the total of all grants is $193,700. Therefore the Ben grant is 21.6% of the whole set of grants for the company.

The two simple queries in the figure below show all the Grant table records and the sum of the grant amounts.

If we want to show the total amount next to every record of the table – or just one record of the table – SQL Server gives us the same error. It does not find the supporting aggregated language needed to support the SUM( ) aggregate function.

Adding the OVER( ) clause allows us to see the total amount next to each grant. We see 193,700 next to each record in the result set.

The sum of all 10 grants is $193,700. Recall the largest single grant (007) is $41,000. Doing the quick math in our head, we recognize $41,000 is around 1/5 of ~$200,000 and guesstimate that Grant 007 is just over 20% of the total.

Thanks to the OVER clause, there’s no need to guess. We can get the precise percentage. To accomplish this, we will add an expression that does the same math we did in our head. We want the new column to divide each grant amount by $193,700 (the total of all the grants).

By listing the total amount of all grants next to each individual grant, we automatically get a nice reference for how each individual grant compares to the total of all JProCo grants. The new column is added and confirms our prediction that Grant 007 represents just over 21% of all grants.

Notice that the figures in our new column appear as ratios. Percentages are 100 times the size of a ratio. Example:  the ratio 0.2116 represents a percentage of 21.16%. Multiplying a ratio by 100 will show the percentage. To finish, give the column a descriptive title, PercentOfTotal.

In today post we examined the basic over clause with an empty set of Parenthesis. The over clause actually have many variations which we will see in tomorrow’s post.

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 SQLQueriesChapter5.0Setup.sql script from Volume 2.

Question 10

You want to show all fields of the Employee table. You want an additional field called StartDate that shows the first HireDate for all Employees. Which query should you use?

  1. SELECT *, Min(HireDate) as StartDate FROM Employee
  2. SELECT *, Max(HireDate) as StartDate FROM Employee
  3. SELECT *, Min(HireDate) OVER() as StartDate FROM Employee
  4. SELECT *, Max(HireDate) OVER() as StartDate FROM Employee

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 2.
Winner from India will get Joes 2 Pros Volume 2.
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)

SQL SERVER – Use INSERT INTO … SELECT instead of Cursor

This blog post is written in response to the post showing some of the worst practices of past. Well, just like last month’s theme, everybody learns by doing it one step at a time. In my case, I started my career as a network engineer and had no database knowledge during that time. I can still remember my old code which became quite a laughingstock when it was sent for a code review. This story is indeed interesting, so instead of writing shortly, I am going to write today in detail.

It happened about 8 years ago when I was working as Network Engineer in United States. I was responsible for a large data center and we had more than 40 servers under my watch. In our SQL Server database we had one table for the inventory of all the servers. On that table we used to keep a lot of details of the server. To keep it simple, I will say that we have only two columns in that table: ServerID and ServerName. I was asked to write a script which was intended to retrieve all the data from this table and insert them into the new table that was created by the database administrator. I thought I could handle SQL Server script well enough because I learned SQL a bit when I was younger. I ended up writing a script where I wrote cursor to SELECT single row from the table and insert into new table. Well, this went very fine. The script was taking a while to perform its tasks; nevertheless, it worked. I used this script for a short while.

Here is the script that I wrote. I am using sample and simple database for example. Please note that this is not a good practice.

-- Create Table and Populate with Sample Data
CREATE TABLE ServerTable (ServerID INT, ServerName VARCHAR(100))
INSERT INTO ServerTable (ServerID, ServerName)
SELECT 1, 'First Server'
UNION ALL
SELECT 2, 'Second Server'
UNION ALL
SELECT 3, 'Third Server'
-- Creating New Table
CREATE TABLE NewServerTable (ServerID INT, ServerName VARCHAR(100))
-- Insert Logic
DECLARE @Flag INT
SELECT
@Flag = COUNT(*) FROM ServerTable
WHILE(@Flag > 0)
BEGIN
INSERT INTO
NewServerTable (ServerID, ServerName)
SELECT ServerID, ServerName
FROM ServerTable
WHERE ServerID = @Flag
SET @Flag = @Flag - 1
END
SELECT
ServerID, ServerName
FROM NewServerTable
-- Clean up
DROP TABLE ServerTable
DROP TABLE NewServerTable

This script worked very well till we had changed in company policy. We then had a newly- appointed CTO (which I was about to become 3 years after this incidence), who introduced the requirement of Code Reviewing of all both old codes and new ongoing codes. When my code went to a code review, my Team Leader really laughed at my code. So he sent me new piece of the code which was much shorter and more efficient. The code which he proposed was as follows. I honestly say this was much better than my code.

-- Create Table and Populate with Sample Data
CREATE TABLE ServerTable (ServerID INT, ServerName VARCHAR(100))
INSERT INTO ServerTable (ServerID, ServerName)
SELECT 1, 'First Server'
UNION ALL
SELECT 2, 'Second Server'
UNION ALL
SELECT 3, 'Third Server'
-- Creating New Table
CREATE TABLE NewServerTable (ServerID INT, ServerName VARCHAR(100))
-- Insert Logic
INSERT INTO
NewServerTable (ServerID, ServerName)
SELECT ServerID, ServerName
FROM ServerTable
SELECT ServerID, ServerName
FROM NewServerTable
-- Clean up
DROP TABLE ServerTable
DROP TABLE NewServerTable

If you’ve noticed, the new insert code is just reduced to two lines only. Today you might think it is a very simple code, but for me it was a very new thing during those times. Now comes the best part of this whole story.

My team leader, who was very supportive and extremely positive person, asked me to demonstrate this whole incidence on our next team meeting. Honestly, I did not feel bad about accepting my mistake and learning new things. During the team meeting, I first accepted the fact that I was wrong with my code and thanked the Team Leader for giving me the opportunity to improve and develop myself. Because of his encouragement to accept my own mistake and improve from that incident, today I have reached this place.

Please get rid of cursors and use INSERT INTO…SELECT or SELECT INTO logic.

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

SQL SERVER – The SQL Hands-On Guide for Beginners – Book Available for SQL Server Certification

We recently give away 7 physical books of Joes 2 Pros eBook Volume 1. The response to following questions was overwhelming and was excellent. The book is available to purchase now in India and USA. This is great news as I often get request that where one can learn SQL Server, how to prepare for SQL Server Certifications. This book with its innovative visual approach lets you have firm hands-on experience as a SQL Server 2008 Developer. It is highly interactive with sections that challenge the student to play “Bug Catcher” in code, and do other interesting quiz games.

United States:

India:

Interesting Feature of Book1:

  • Chapter on writing Efficient Queries
  • Videos for each chapter
  • Query Strategies and Maintaining Tables
  • Chapter on DML, DDL, TCL and DCL
  • 100+ Quiz Questions
  • 50 Hands-on skill labs
  • “Bug Catcher” Code Game

Less than 30 seconds Video

Checkout following questions which are based on Volume 1.

Questions

Q 1) SQL SERVER – Tips from the SQL Joes 2 Pros Development Series – Wildcard Basics Recap – Day 1 of 35

Q 2) SQL SERVER – Tips from the SQL Joes 2 Pros Development Series – Wildcard – Querying Special Characters – Day 2 of 35

Q 3) SQL SERVER – Tips from the SQL Joes 2 Pros Development Series – Finding Apostrophes in String and Text – Day 3 of 35

Q 4) SQL SERVER – Tips from the SQL Joes 2 Pros Development Series – Efficient Query Writing Strategy – Day 4 of 35

Q 5) SQL SERVER – Tips from the SQL Joes 2 Pros Development Series – Finding un-matching Records – Day 5 of 35

Q 6) SQL SERVER – Tips from the SQL Joes 2 Pros Development Series – Row Constructors – Day 6 of 35

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

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

SQL SERVER – Tips from the SQL Joes 2 Pros Development Series – Overriding Identity Fields – Tricks and Tips of Identity Fields – Day 9 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 2.
Every day one winner from United States will get Joes 2 Pros Volume 2.

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.

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.

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.

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

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.

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.

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.

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 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 United States will get Joes 2 Pros Volume 2.
Winner from India will get Joes 2 Pros Volume 2.
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)

SQL SERVER – Tips from the SQL Joes 2 Pros Development Series – Many to Many Relationships – Day 8 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 2.
Every day one winner from United States will get Joes 2 Pros Volume 2.

Many to Many relationships

If anyone has done some shopping on the internet you are familiar with the term “Shopping Cart” or “Shopping basket”. After you have selected a product you want to buy the storefront will gladly let you keep on shopping until there are many items in you shopping cart. On my last trip to Amazon.com I put 3 things in my cart before checking out. One item was a HDMI cable box converter for my flat screen TV at home. I took a closer look at this products rating and reviews before buying. Below this product it said this items was in the top 100 selling items in home electronics. This means this items has sold to more people than just me and I was not the first to buy this product.

Based on the story above what is the relationship to a product (like the HDMI cable box) and a customer who may want to buy that item? As a customer I can buy many products at once in my shopping cart. Does this mean there is a one to many relationship between customers (like me) and products (like the HDMI cable box). Yes there is but that is only a half truth. A product like the HDMI cable box can be purchased by many different customers. Therefore there is a Many to Many relationship between Customers and products. This product can be bought by many customers and many customer can buy this product.

Another many to many relationship is between books and Authors. I have written 5 books so we know there is at least a one to many relationship between Authors and Books. Now look at my 4th book (SQL Programming Joes 2 Pros Volume 4 – ISBN-13: 978-1451579482). This book has two authors (Rick A. Morelan and Pinal Dave). Since an Author can write many books and a book can be written by many authors there is a many to many relationship between authors and books.

We need a new process to map this complex relationship. Sometimes tables exist for the sole purpose of allowing indirect relationships between tables. What do you call tables the handle relationships for other tables? Common terms for these type of tables are mapping tables, bridge tables, or junction tables.

Many to Many relationships with invoicing systems

As discussed earlier a product can appear on many invoices and an invoice can have many products. The relationship between products and invoices is known as a many-to-many relationship as seen in the figure below.

If you were to ask yourself “What Products are on Invoice 5631?” or “Go-Duck was ordered on how many invoices?” you could resolve this query? The Figure below shows us how to resolve both mappings. We can see the Go-Duck toy was order twice (on 7/15 and on 9/22). We can also see the shopping cart 5631 has two items in it (Toy Car and Furchee).

Now we’ll take a look at many-to-many relationships between sales invoices and products in the JProCo sample database.

Here is an example of JProCo’s sales invoices mapping to a bridge table (SalesInvoiceDetail) in order to map over to the CurrentProducts table. The CurrentProducts table gives us all the detail of the current products that have been ordered.

With SalesInvoice 5 It looks like many products were ordered on that one invoice (Products 9, 11, 12, and 16). To see what those products are, we would look over to the CurrentProducts table. We see Product 9 is an Underwater Tour 3 Days East Coast. Product 11 is an Underwater Tour 1 Week East Coast, and so forth. So a SalesInvoice can have many products, and products can be ordered on multiple sales invoices.

The SalesInvoiceDetail table is the mapping table that is handling the many to many relationship between the SalesInvoice table and the CurrentProducts table. This 3 table join between these tables would look like the query you see below.

SELECT si.CustomerID, si.InvoiceID, si.OrderDate,
sd.Quantity, cp.ProductName, cp.RetailPrice
FROM dbo.SalesInvoiceDetail AS sd
INNER JOIN dbo.SalesInvoice AS si
ON sd.InvoiceID = si.InvoiceID
INNER JOIN CurrentProducts AS cp
ON cp.ProductID = sd.ProductID

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 8

Q.8) You have tables named dbo.SalesInvoice and dbo.SalesInvoiceDetail. CustomerID is located in the SalesInvoice table and InvoiceID is located in both tables. You have been told to show the discount amounts from the SalesInvoiceDetail table that correspond to the sales of a specific CustomerID of 490. Which T-SQL statement should you use?

  1. SELECT sd.CustomerID, si.DiscountAmt, cp.ProductName
    FROM dbo.SalesInvoiceDetail sd
    INNER JOIN dbo.SalesInvoice si
    ON sd.InvoiceID= si.InvoiceID
    INNER JOIN CurrentProducts AS cp
    ON cp.ProductID = sd.ProductID
    WHERE si.CustomerID= 490
  2. SELECT sd.CustomerID, si.DiscountAmt, cp.ProductName
    FROM dbo.SalesInvoiceDetail sd
    INNER JOIN CurrentProducts AS cp
    ON cp.ProductID = sd.ProductID
    WHERE si.CustomerID= 490
  3. SELECT sd.CustomerID, si.DiscountAmt, cp.ProductName
    FROM dbo.SalesInvoiceDetail sd
    WHERE EXISTS (dbo.SalesInvoice si
    ON sd.InvoiceID= si.InvoiceID
    INNER JOIN CurrentProducts AS cp
    ON cp.ProductID = sd.ProductID
    WHERE si.CustomerID= 490)

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 2.
Winner from India will get Joes 2 Pros Volume 2.
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)

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)