SQL Tips by Namwar Rizvi

June 2, 2007

Sample Your Data with TABLESAMPLE feature

Filed under: Information,New Features,Performance,Query,SQL Server 2005,tips,TSQL — namwar @ 11:11 PM

Data sampling is a technique to get the idea of the data without going through the whole list of rows. It is used mostly in data warehousing or in trend finding application when you have huge amount of data and finding an average value will take quite a long time. In this scenario, we implement techniques to randomly selects some rows based on a probability factor and calculate average from these selected values. Although, there is no guarantee that this average is 100% accurate but intelligently chosen random rows will give nearly 70-80% correct value which is most of the time sufficient.
SQL Server 2005 introduced a new feature called TABLESAMPLE which provides you the power of data sampling. The algorithm on which it decides which row needs to be returned is currently only one which is SYSTEM but in future I am quite hopeful that Microsoft will surely come up with other algorithms.
For example, you need to quickly get an idea that what is the average subtotal of orders, following example demonstrates this:

Select
(Select Avg(SubTotal) from Sales.SalesOrderHeader TableSample(50)) as SampledAverage,
(Select Avg(SubTotal) from Sales.SalesOrderHeader) ActualAverage

I got the following result on my machine:
SampledAverage, ActualAverage
4049.9467 , 4046.9467
(1 row(s) affected)

In above example, we instructed SQL Server 2005 to calculate the average on 50% percent sample data of Sales.SalesOrderHeader table which contains 31465 rows. By reviewing the result you can see that we got nearly 95% correct result which is a real performance improvement because we just used half number of rows to achieve nearly 95% correctness.
Please note that running same TABLESAMPLE query again and again may return different results because of the probabilistic nature of this algorithm.
For further details please read visit

TableSample description at TechNet

Advertisements

Leave a Comment »

No comments yet.

RSS feed for comments on this post. TrackBack URI

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

Create a free website or blog at WordPress.com.

%d bloggers like this: