How To Update Statistics On A Table In SQL Server

As a senior developer working with SQL servers, when I was analyzed in terms of performance perspective, I learned that keeping the statistics up to date is crucial for optimal query performance. In this article, I will walk you through the best approaches to updating statistics on a table in an SQL server.

Approach-1 Using UPDATE STATISTICS statement

We can execute the SQL query below to update the statistics in an SQL table.

Syntax

UPDATE STATISTICS table name;

Example

I executed the query below to update the STATISTICS of the Orders table.

UPDATE STATISTICS Orders;

The query was executed successfully. Check out the screenshot below for your reference.

You can use the WITH FULLSCAN option for a more thorough update

UPDATE STATISTICS Orders WITH FULLSCAN;

After executing the above query, I got the expected output, as shown in the screenshot below

If you are working with a huge table, you can use the WITH SAMPLE PERCENT option for better performance, as shown below.

UPDATE STATISTICS Orders WITH SAMPLE 50 PERCENT;

We can also use the SQL query below to update a table’s specific column.

UPDATE STATISTICS table_name (column1, column2);

UPDATE STATISTICS Product (ProductID);

Check out How To Enable CDC On A Table In SQL Server

Approach-2: Using sp_updatestats Stored Procedure

We can also use the sp_updatestats Stored Procedure to update the statistics for all the tables in a database, as shown below.

EXEC sp_updatestats;

After executing the above query, I got the expected output, as shown below

Check out How To Create A Table Variable In SQL Server

Conclusion

Updating statistics on a table in SQL Server is a very important maintenance task that can help you, as a developer, boost query performance. You can utilize the above quick approaches to do so this.

You may also like following the articles below.

• How To Enable CDC On A Table In SQL Server
• How to create index on temp table in SQL Server
• How To Create A Table Variable In SQL Server