Skip to content

delete-statistics.md

Latest commit

 

History

History
94 lines (64 loc) · 3.74 KB

delete-statistics.md

File metadata and controls

94 lines (64 loc) · 3.74 KB
title Delete Statistics | Microsoft Docs
ms.custom
ms.date 03/14/2017
ms.prod sql
ms.reviewer
ms.technology performance
ms.topic conceptual
helpviewer_keywords
statistics [SQL Server], deleting
deleting statistics
ms.assetid eccce0aa-591e-4a1d-bd10-373b022f8749
author julieMSFT
ms.author jrasnick
monikerRange >=aps-pdw-2016||=azuresqldb-current||=azure-sqldw-latest||>=sql-server-2016||=sqlallproducts-allversions||>=sql-server-linux-2017||=azuresqldb-mi-current

Delete Statistics

[!INCLUDEappliesto-ss-asdb-asdw-pdw-md] You can delete (drop) statistics from tables and views in [!INCLUDEssCurrent] by using [!INCLUDEssManStudioFull] or [!INCLUDEtsql]

In This Topic

Before You Begin

Limitations and Restrictions

  • Be careful when you drop statistics. Doing so may affect the execution plan chosen by the query optimizer.

  • Statistics on indexes cannot be dropped by using DROP STATISTICS. Statistics remain as long as the index exists.

Security

Permissions

Requires ALTER permission on the table or view.

Using SQL Server Management Studio

To drop statistics from a table or view

  1. In Object Explorer, click the plus sign to expand the database in which you want to delete a statistic.

  2. Click the plus sign to expand the Tables folder.

  3. Click the plus sign to expand the table in which you want to delete a statistic.

  4. Click the plus sign to expand the Statistics folder.

  5. Right-click the statistics object that you want to delete and select Delete.

  6. In the Delete Object dialog box, ensure that the correct statistic has been selected and click OK.

Using Transact-SQL

To drop statistics from a table or view

  1. In Object Explorer, connect to an instance of [!INCLUDEssDE].

  2. On the Standard bar, click New Query.

  3. Copy and paste the following example into the query window and click Execute.

    USE AdventureWorks2012;  
GO  
-- First, create two statistics named VendorCredit and CustomerTotal  
-- The first statistic uses a random 50% sample of information provided from the Name and CreditRating columns in the Purchasing.Vendor table.  
CREATE STATISTICS VendorCredit  
    ON Purchasing.Vendor (Name, CreditRating)  
    WITH SAMPLE 50 PERCENT  
-- The second statistic uses all of the information from the CustomerID and TotalDue columns in the Sales.SalesOrderHeader table  
CREATE STATISTICS CustomerTotal  
    ON Sales.SalesOrderHeader (CustomerID, TotalDue)  
    WITH FULLSCAN;  
GO  
-- This next statement drops both of the statistics created above. Note that the naming convention is [table_name].[statistics_name].  
DROP STATISTICS Purchasing.Vendor.VendorCredit, Sales.SalesOrderHeader.CustomerTotal;  
GO

For more information, see DROP STATISTICS (Transact-SQL).