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 VIDEOS
 
 
SQL E/BOOKS   WORLD, USA & SQL NEWS   FORMAT
SCRIPTS SQL 2005 SQL 2008 ARTICLES
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
SQL 2016 DESIGN & PROGRAMMING
 
 
 
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