Wednesday, October 24, 2012

Understanding Statistics


-- Statistics:
Two way to create statistics
1) Manual
2) Automatic

1) Manual : If you mention the index/Key in the table while creating or on existing table..
The statistics creates with the Index Name

2) Automatic :
When ever you are doing transactions based on the column which you never created an Index/ Key on it..
You can see the new field under statistics, which is like..
_WA_Sys_00000001_112G3A24

Where WA Stands Washington as the SQL product from there
Sys stands the SQL generates...
00000001 stands the column Number..and
Finally, 112G3A24 Hexdecimal number of the Object_ID(Object_Name/TableName)

 Help: Please click here to convert your hexadecimal into Numeric Number         
         
If you want to check this, Please try the below Example..

CREATE TABLE StatsTest (ID INT IDentity(1,1) Primary KEY
, Name varchar(10) )
INSERT INTO StatsTest VALUES ('Test1'),('Test2'),('Test3')

SELECT * FROM StatsTest


UPDATE StatsTest  SET Name = 'StatsTest'  WHERE ID =1
  CHECKPOINT
-- You dont see anything in the stats now

UPDATE StatsTest  SET Name = 'StatsTest1'  WHERE Name = 'Test2'


 Run the above piece of code, refresh statistics on the table and observe.
You can see the new object under statistics as below

_WA_Sys_00000002_1920BF5C

It means 00000002 is the number of the column in the table
where 1920BF5C is the Hexdecimal number for the (table-starttest)Object_ID.

--You can get some useful info from here.

No comments:

Post a Comment