DATETIME SELECT SELECT INTO DATE PAD STRING DYNAMIC SQL CURSOR MONEY FORMAT PERCENT STORED PROCEDURE SQL SERVER AGENT JOB OPTIMIZATION WHILE LOOP OVER PARTITION BY UPDATE
SITE SEARCH SQLUSA.com HEADLINES NEWS
SQL E/BOOKS   SQL 2014 PROGRAMMING   DOWNLOADS
SCRIPTS SQL 2005 SQL 2008 ARTICLES
SQL JOBS TWITTER FORMAT VIDEOS
How to use TABLESAMPLE with aggregate functions?

Execute the following SQL Server T-SQL scripts in Management Studio Query Editor to demonstrate the use of the TABLESAMPLE clause.

-- SQL Server tablesample clause - SQL random sample

-- SQL tablesample with aggregate functions

USE AdventureWorks;

SELECT

      SampleSize              = count(*),

      AvgOrderQuantity        = AVG(1.0*OrderQty),

      TotalOrderQuantity      = SUM(1.0*OrderQty),

      StdDevOrderQuantity     = STDEV(1.0*OrderQty)

FROM Sales.SalesOrderDetail

TABLESAMPLE SYSTEM (2 PERCENT)

GO

 

/ 

/* Results - first execution

 

SampleSize  AvgOrderQuantity  TotalOrderQuantity      StdDevOrderQuantity

2209        2.117700          4678.0                  2.41498547697288

*/

 

 

/* Results - second run

 

SampleSize  AvgOrderQuantity  TotalOrderQuantity      StdDevOrderQuantity

2467        1.980948          4887.0                  2.03578136114617

*/

 

 

 

-- SQL tablesample on select query

SELECT

            SalesOrderID

FROM Sales.SalesOrderHeader

TABLESAMPLE SYSTEM (7 PERCENT)

GO

-- (663 row(s) affected)

-- (3315 row(s) affected)

/* Partial results

 

SalesOrderID

60808

58753

72039

53750

64501

60227

*/

 

-- Alternative to tablesample on select (no aggregate)

-- SQL newid random function

SELECT TOP (7) PERCENT

            SalesOrderID

FROM Sales.SalesOrderHeader

ORDER BY NEWID()

GO

-- (2203 row(s) affected) - ALWAYS!

/* Results vary with subsequent execution

 

SalesOrderID

49880

56299

69957

56079

55601

65216

*/

Exam Prep 70-461
Exam 70-461
DATETIME SELECT SELECT INTO DATE PAD STRING DYNAMIC SQL CURSOR MONEY FORMAT PERCENT STORED PROCEDURE SQL SERVER AGENT JOB OPTIMIZATION WHILE LOOP OVER PARTITION BY UPDATE