Microsoft SQL Server collects statistical information about indexes and column data stored in the database. These statistics are used by the SQL Server query optimizer to choose the most efficient plan for retrieving or updating data.
Creating a Table
IF OBJECT_ID('Tb_Sample1','U') IS NULL
CREATE TABLE Tb_Sample1
(
Id INT IDENTITY(1,1),
Column1 VARCHAR(10),
Column2 DATETIME,
Column3 VARCHAR(10)
)
GO
Inserting sample data(10 Records) for testing
INSERT Tb_Sample1(Column1,Column2,Column3) VALUES('Column1',CURRENT_TIMESTAMP,'Column3')
GO 10
Creating Non-Clustered Index on ID
CREATE INDEX IND_ID ON Tb_Sample1(ID)
GO
Creating Non-Clustered Index on Column1
CREATE INDEX IND_Column1 ON Tb_Sample1(Column1)
GO
Creating Non-Clustered Index on Column2
CREATE INDEX IND_Column2 ON Tb_Sample1(Column2)
GO
Creating Non-Clustered Index on Column3
CREATE INDEX IND_Column3 ON Tb_Sample1(Column3)
GO
Fetching the Stratistics information for the Table
SP_AUTOSTATS 'Tb_Sample1'
GO
(OR)
SELECT Name [Index_Name],
CASE no_recompute WHEN 1 THEN 'OFF' ELSE 'ON' END AUTOSTATS,
STATS_DATE([OBJECT_ID],2) [Last Updated]
FROM sys.stats WHERE [Object_Id] = OBJECT_ID('Tb_Sample1')
GO
Fetching the Stratistics information for the particular index on a Table
SP_AUTOSTATS 'Tb_Sample1',NULL,'IND_Column2'
GO
Setting "Auto Update Statistics" OFF for a particular index on a Table
SP_AUTOSTATS 'Tb_Sample1','OFF','IND_Column2'
GO
Setting "Auto Update Statistics" OFF for all the indexes on a Table
SP_AUTOSTATS 'Tb_Sample1','OFF'
GO
Update statistics for a Particular Index on a Table
UPDATE STATISTICS Tb_Sample1 IND_Column2
GO
Update statistics on a particular Table
UPDATE STATISTICS Tb_Sample1
GO
Update statistics on all the User-defined and Internal tables
EXEC SP_UPDATESTATS
GO
No comments:
Post a Comment