syntaxhelp.com Your online cheat sheet!

Creating statistics using FULLSCAN option:

--Creating Statistics by scanning all rows in the table (or indexed
--view). By default this option is on when a database is created.
--FULLSCAN forces a complete read of every row of data.

CREATE STATISTICS City_PostalCode
ON AdventureWorks.Person.Address (City, PostalCode)
WITH FULLSCAN ;

Creating statistics using FULLSCAN option with NORECOMPUTE:

--Creating Statistics by scanning all rows in the table (or indexed
--view) with AUTO_UPDATE_STATISTICS option off.

CREATE STATISTICS City_PostalCode
ON AdventureWorks.Person.Address (City, PostalCode)
WITH FULLSCAN , NORECOMPUTE ;

Creating statistics using SAMPLE option:

--Specifies the approximate percentage or number of rows in the table (or indexed
--view) for the query optimizer to use when it creates statistics.
--SAMPLE 100 PERCENT and FULLSCAN both give same results.

--PERCENT
CREATE STATISTICS City_PostalCode
ON AdventureWorks.Person.Address (City, PostalCode)
WITH SAMPLE 10 PERCENT ;

--ROWS
CREATE STATISTICS City_PostalCode
ON AdventureWorks.Person.Address (City, PostalCode)
WITH SAMPLE 1000 ROWS ;

Creating statistics using SAMPLE option with NORECOMPUTE:

--Specifies the approximate percentage or number of rows in the table (or indexed
--view)for the query optimizer to use when it creates statistics and
--disables automatic recomputing of statistics.

--PERCENT
CREATE STATISTICS City_PostalCode
ON AdventureWorks.Person.Address (City, PostalCode)
WITH SAMPLE 10 PERCENT , NORECOMPUTE ;

--ROWS
CREATE STATISTICS City_PostalCode
ON AdventureWorks.Person.Address (City, PostalCode)
WITH SAMPLE 750 ROWS , NORECOMPUTE ;

Creating statistics using STATS_STREAM option:

--To generate hexadecimal value for STATS_STREAM refer this link:
--http://decipherinfosys.wordpress.com/2008/12/02/scripting-statistics-and-histograms-in-sql-server/
--Generally STATS_STREAM is more used in UPDATE_STATISTICS. Future compatibility is not guaranteed.

CREATE STATISTICS City_PostalCode
ON AdventureWorks.Person.Address (City, PostalCode)
WITH STATS_STREAM = 0x010000000200000000000000000000001C3F5786 (very long hex code) B0600C0016C060085046D0600050A71060002 ;

Creating statistics using filter predicate (MSSQL 2008):

--Filter predicate uses simple comparison logic. Computed column, a UDT
--column, a spatial data type column, or a hierarchyID data type column
--cannot be referenced.

CREATE STATISTICS City_PostalCode
ON AdventureWorks.Person.Address (City, PostalCode)
WHERE ModifiedDate > '2000-01-01' AND ModifiedDate < '2006-02-20'
AND city <> 'Bothell' ;

Creating statistics using filter predicate with PERCENT and NORECOMPUTE option (MSSQL 2008):

--Filter predicate uses simple comparison logic.
--The Database Engine samples 20 percent of the data and then selects the rows with ModifiedDate greater than 2001-01-20. 

CREATE STATISTICS City_PostalCode
ON AdventureWorks.Person.Address (City, PostalCode)
WHERE ModifiedDate > '2001-01-20'
WITH SAMPLE 20 PERCENT , NORECOMPUTE ;