sp_updatestats vs Update statistics

Is there any difference between updating the statistics of a table using sp_updatestats with out resample and updating stats of table using UPDATE STATISTICS without sample options(FULLSCAN,SAMPLE PERCENT,RESAMPLE)


exec sp_updatestats vs update statistics tablename

updating the tables using sp_updatestats with default value NO will update the statistics with default sampling rate.

similarly updating statistics of table using UPDATE STATISTICS without sample options(FULLSCAN,SAMPLE PERCENT,RESAMPLE) will also update the table statistics with default sampling.

So is there any difference between both methods? Am I missing anything here ?

Update :

I know that sp_updatestats runs on all the tables but using UPDATE STATISTICS we can update statistics of specific table.

The code for sp_updatestats is fairly straightforward, you can look at it by creating a copy of the mssqlsystemresource database. Just copy the mdf & ldf files somewhere and attach them as a database with another name.

As can be seen in BOL the statement takes 1 parameter, resample:

ALTER procedure [sys].[sp_updatestats]
    @resample char(8)='NO'
as

I don't think it's a good idea to post the entire procedure here, you could have a look yourself, but basically it's a simple procedure.

Inside the procedure, it loops over the tables in the database, does some basic checks, such as checking a table doesn't have it's clustered index disabled, checks if a table is hekathon (to determine if the list of statistics on a table should be read from sys.stats or sys.indexes), checks if it's a hekathon table again to add fullscan in case of a hekathon table and then executes a simple UPDATE STATISTICS.

Basically one of these three statements is executed on all statistics in your database

-- When resample is yes
UPDATE STATISTICS [sysname].[sysname] [sysname] WITH RESAMPLE
-- When resample is no
UPDATE STATISTICS [sysname].[sysname] [sysname] 
-- For a hekathon table
UPDATE STATISTICS [sysname].[sysname] [sysname] WITH FULLSCAN

Except if the statistics have autostats disabled it also adds a NORECOMPUTE clause

So there isn't a lot of difference between simply calling an UPDATE STATISTICS on each of your statistics if they don't have autostats disabled and aren't on memory optimized tables.

StackExchange Database Administrators Q#145982, answer score: 7