Using TABLESAMPLE in SQL Server

SQL Server has several ways for you to limit the number of records returned from a query.  For example, the AdventureWorks [sales].[salesorderdetail] table has 121,317 rows loaded in sequential order starting with SalesOrderDetailID 1.  By Using a top 1000 clause I can limit the results to the first 1000 rows loaded into the table.  If I want to get a more random sample of rows to inspect I can leverage the TABLESAMPLE keyword in my statement.

select *

from [Sales] .[SalesOrderDetail]

– 121,317 Rows Returned

select top 1000 *

from [Sales] .[SalesOrderDetail]

– 1,000 Rows Returned

– First 7 Rows still in the order the table was loaded

Instead of selecting the first 1000 rows we can use the TABLESAMPLE keyword to get a random sample returned from the query.  Adding TABLESAMPLE (1000 ROWS) tells SQL Server to find random records and return approximately this number of rows.  Using this example, it returned 1,183 rows.

select *

from [Sales] .[SalesOrderDetail]

TABLESAMPLE (1000 ROWS)

– 1,183 Rows Returned

– First 7 Rows

You can also limit the rows returned by the percentage of records as shown here:

select *

from [Sales] .[SalesOrderDetail]

TABLESAMPLE (10 Percent)

– 11,819 Rows Returned

– First 7 Rows

Using the TABLESAMPLE keyword is a great way to quickly look at a set of data for profiling.  This can be very helpful when beginning work on a business intelligence or data warehousing project.

For complete information on TABLESAMPLE see MSDN: http://technet.microsoft.com/en-us/library/ms189108%28v=sql.105%29.aspx

The post SQL Server TABLESAMPLE appeared first on Derek E Wilson - Blog.