Skip to content

creating-and-updating-statistics.md

Latest commit

 

History

History
125 lines (99 loc) · 5.97 KB

creating-and-updating-statistics.md

File metadata and controls

125 lines (99 loc) · 5.97 KB
title Creating and Updating Statistics
ms.custom seo-dt-2019
ms.date 08/06/2017
ms.prod sql
ms.prod_service database-engine
ms.reviewer
ms.technology
ms.topic reference
helpviewer_keywords
statistical information [SMO]
ms.assetid 47a0a172-a969-4deb-bca9-dd04401a0fe1
author markingmyname
ms.author maghan
monikerRange =azuresqldb-current||=azure-sqldw-latest||>=sql-server-2016||=sqlallproducts-allversions||>=sql-server-linux-2017||=azuresqldb-mi-current

Creating and Updating Statistics

[!INCLUDEappliesto-ss-asdb-asdw-xxx-md] In SMO, statistical information about processing queries in the database can be collected by using the xref:Microsoft.SqlServer.Management.Smo.Statistic object.

It is possible to create statistics for any column by using the xref:Microsoft.SqlServer.Management.Smo.Statistic and xref:Microsoft.SqlServer.Management.Smo.StatisticColumn object. The xref:Microsoft.SqlServer.Management.Smo.Statistic.Update%2A method can be run to update the statistics in the xref:Microsoft.SqlServer.Management.Smo.Statistic object. The results can be viewed in the Query Optimizer.

Example

To use any code example that is provided, you will have to choose the programming environment, the programming template, and the programming language in which to create your application. For more information, see Create a Visual C# SMO Project in Visual Studio .NET.

Creating and Update Statistics in Visual Basic

This code example creates a new table on an existing database for which the xref:Microsoft.SqlServer.Management.Smo.Statistic object and the xref:Microsoft.SqlServer.Management.Smo.StatisticColumn object are created.

'Connect to the local, default instance of SQL Server.
Dim srv As Server
srv = New Server
'Reference the AdventureWorks2012 database.
Dim db As Database
db = srv.Databases("AdventureWorks2012")
'Reference the CreditCard table.
Dim tb As Table
tb = db.Tables("CreditCard", "Sales")
'Define a Statistic object by supplying the parent table and name arguments in the constructor.
Dim stat As Statistic
stat = New Statistic(tb, "Test_Statistics")
'Define a StatisticColumn object variable for the CardType column and add to the Statistic object variable.
Dim statcol As StatisticColumn
statcol = New StatisticColumn(stat, "CardType")
stat.StatisticColumns.Add(statcol)
'Create the statistic counter on the instance of SQL Server.
stat.Create()

Creating and Update Statistics in Visual C#

This code example creates a new table on an existing database for which the xref:Microsoft.SqlServer.Management.Smo.Statistic object and the xref:Microsoft.SqlServer.Management.Smo.StatisticColumn object are created.

{  
            //Connect to the local, default instance of SQL Server.  
            Server srv = new Server();  
            //Reference the AdventureWorks2012 database.   
            Database db = default(Database);  
            db = srv.Databases["AdventureWorks2012"];  
            //Reference the CreditCard table.   
  
           Table tb = db.Tables["CreditCard", "Sales"];  
            //Define a Statistic object by supplying the parent table and name   
            //arguments in the constructor.   
            Statistic stat = default(Statistic);  
            stat = new Statistic(tb, "Test_Statistics");  
            //Define a StatisticColumn object variable for the CardType column   
            //and add to the Statistic object variable.   
            StatisticColumn statcol = default(StatisticColumn);  
            statcol = new StatisticColumn(stat, "CardType");  
            stat.StatisticColumns.Add(statcol);  
            //Create the statistic counter on the instance of SQL Server.   
            stat.Create();  
        }

Creating and Update Statistics in PowerShell

This code example creates a new table on an existing database for which the xref:Microsoft.SqlServer.Management.Smo.Statistic object and the xref:Microsoft.SqlServer.Management.Smo.StatisticColumn object are created.

# Example of implementing a full text search on the default instance.  
# Set the path context to the local, default instance of SQL Server and database tables  
  
CD \sql\localhost\default\databases  
$db = get-item AdventureWorks2012  
  
CD AdventureWorks2012\tables  
  
#Get a reference to the table  
$tb = get-item Production.ProductCategory  
  
# Define a FullTextCatalog object variable by specifying the parent database and name arguments in the constructor.  
  
$ftc = New-Object -TypeName Microsoft.SqlServer.Management.SMO.FullTextCatalog -argumentlist $db, "Test_Catalog2"  
$ftc.IsDefault = $true  
  
# Create the Full Text Search catalog on the instance of SQL Server.  
$ftc.Create()  
  
# Define a FullTextIndex object variable by supplying the parent table argument in the constructor.  
$fti = New-Object -TypeName Microsoft.SqlServer.Management.SMO.FullTextIndex -argumentlist $tb  
  
#  Define a FullTextIndexColumn object variable by supplying the parent index   
#  and column name arguments in the constructor.  
  
$ftic = New-Object -TypeName Microsoft.SqlServer.Management.SMO.FullTextIndexColumn -argumentlist $fti, "Name"  
  
# Add the indexed column to the index.  
$fti.IndexedColumns.Add($ftic)  
  
# Set change tracking  
$fti.ChangeTracking = [Microsoft.SqlServer.Management.SMO.ChangeTracking]::Automatic  
  
# Specify the unique index on the table that is required by the Full Text Search index.  
$fti.UniqueIndexName = "AK_ProductCategory_Name"  
  
# Specify the catalog associated with the index.  
$fti.CatalogName = "Test_Catalog2"  
  
# Create the Full Text Search Index  
$fti.Create()