updating statistics using STATS_STREAM or WITH FULLSCAN?

the syntax for creating statistics is:

-- Syntax for SQL Server and Azure SQL Database  

UPDATE STATISTICS table_or_indexed_view_name   
    [   
        {   
            { index_or_statistics__name }  
          | ( { index_or_statistics_name } [ ,...n ] )   
                }  
    ]   
    [    WITH   
        [  
            FULLSCAN   
              [ [ , ] PERSIST_SAMPLE_PERCENT = { ON | OFF } ]    
            | SAMPLE number { PERCENT | ROWS }   
              [ [ , ] PERSIST_SAMPLE_PERCENT = { ON | OFF } ]    
            | RESAMPLE   
              [ ON PARTITIONS ( {  |  } [, ...n] ) ]  
            |  [ ,...n ]  
        ]   
        [ [ , ] [ ALL | COLUMNS | INDEX ]   
        [ [ , ] NORECOMPUTE ]   
        [ [ , ] INCREMENTAL = { ON | OFF } ] 
        [ [ , ] MAXDOP = max_degree_of_parallelism ] 
    ] ;  

 ::=  
    [ STATS_STREAM = stats_stream ]  
    [ ROWCOUNT = numeric_constant ]  
    [ PAGECOUNT = numeric_contant ]

and here there is an excellent script by Martin Smith that scripts the stats with STATS_STREAM

when I run that script in one of my test databases I get the following:

```
DECLARE @Schema sysname,
@Table sysname,
@StatsName sysname,
@IncludeStatsStream BIT,
@StatsStream VARCHAR(MAX),
@TwoPartQuotedName NVARCHAR(500);

select @Schema = 'dbo',
@Table = 'tbl_Application_Medical',
@StatsName = '_WA_Sys_00000002_7039AB57',
@IncludeStatsStream = 1;

select @TwoPartQuotedName = QUOTENAME(@Schema) + '.' + QUOTENAME(@Table);

IF @IncludeStatsStream = 1 AND @@MICROSOFTVERSION/ POWER(2,24) > 9
BEGIN
DECLARE @StatsResults TABLE
(
StatsStream VARBINARY(MAX),
Rows BIGINT,
DataPages BIGINT
);

INSERT INTO @StatsResults
EXEC sys.sp_executesql
N'DBCC SHOW_STATISTICS(@TwoPartQuotedName, @StatsName) WITH STATS_STREAM;',

Using commands with STATS_STREAM you are able to create exact copy of some statistics. It really useful for troubleshooting or for testing purposes when you want to repeat the query optimizer behavior on another table or even in another database.

A great article on the option: Custom Built Statistics

Using a command 'WITH FULLSCAN' you just update query optimization statistics by scanning all rows on a table.

From BOL:

FULLSCAN

Compute statistics by scanning all rows in the table or
indexed view. FULLSCAN and SAMPLE 100 PERCENT have the same results.
FULLSCAN cannot be used with the SAMPLE option.

StackExchange Database Administrators Q#235517, answer score: 4