Getting Started with Adobe After Effects - Part 6: Motion Blur


Upload Image Close it
Select File

Welcome to my blog. I work as Database lead at Synaptic Digital. Hope you find some interesting stuff here.
Browse by Tags · View All
BRH 17
SQL Server 15
#SQL Server 11
#BI 10
#TSQL 8
TSQL 8
BI 7
SSRS 6
#SQLServer 6
SSRS 2008R2 5

Archive · View All
January 2011 6
December 2010 5
September 2012 4
May 2012 4
March 2011 4
November 2012 2
October 2012 2
January 2012 2
February 2011 2
November 2010 2

How Index_id are generated...

Oct 16 2012 12:00AM by Chintak Chhapia   

When we use index query hint, we can give the index number or index name. I use an index hint for testing performance purpose only and almost never use it in actual code. I tend to use index numbers in test code. But, till some time ago I used to wonder why the index id of the newly created index is quite bigger, for one wide table index_id of the newly created index is 70 and We do not have these many indexes:). So, what's the reason that newly created index is having ID 70?

After looking into sys.indexs catalog view, I am only able to find out the indexes on the table. But, When I looked into sys.stats catalog view, I found the answer. When we create an index, statistics with same index key columns is also gets created. So, the index_id picked is the next value from sys.stats view.

Have a look at this example

use tempdb go if object_id('t1') is not null drop table t1 go Create table t1 ( c1 int primary key , c2 nvarchar(10) , c3 nvarchar(10) ) go insert into t1 (c1,c2,c3) select top 100 number,type,cast(number as nvarchar(4)) + 'test'

from master.dbo.spt_values where type like 'p%' go Create index ix_t1_c2 on t1(c2) go select index_id,name from sys.indexes where object_id = object_id('t1') select stats_id,name from sys.stats where object_id = object_id('t1') go

Two rows will be returned from both queries. Now, we query the table with predicate on where column C3, so in a database where we have auto create statistics turn on (default), SQL will create statistics for the column c3.

select c1,c2,c3 from t1 where c3 like '0%'
go
select index_id,name 
from sys.indexes where object_id = object_id('t1')
select stats_id,name 
from sys.stats where object_id = object_id('t1')
go

So now, sys.stats will give 3 rows. So, if we create index now, the newly created index will have id of 4.

Create index ix_t1_c3 on t1(c3)
go
select index_id,name 
from sys.indexes where object_id = object_id('t1')

select stats_id,name 
from sys.stats where object_id = object_id('t1')
go

Now, we can query the either way as shown below

select c1,c2,c3 
from t1 with (index=4) where c3 like '0%'

select c1,c2,c3 
from t1 with (index=ix_t1_c3) where c3 like '0%'

Please note that explicit hints are not recommended but even if you need to use ever always use the index name in actual code.

Tags: 


Chintak Chhapia
40 · 5% · 1477
4
 
0
Lifesaver
 
0
Refreshed
 
 
0
Incorrect



Submit

4  Comments  

  • Chintak: Very informative! I knew about the relationship between indexes and statistics in the sense that reorganizing/recreating an index would update the statistics, but never thought about looking for a relationship between the index Id and the statistics Id. Good one!

    commented on Oct 16 2012 12:18PM
    Nakul Vachhrajani
    4 · 36% · 11648
  • @Nakul: Glad that you like the post.

    commented on Oct 17 2012 1:06AM
    Chintak Chhapia
    40 · 5% · 1477
  • Nice post Chintak.

    commented on Oct 17 2012 2:09AM
    Hardik Doshi
    20 · 9% · 2864
  • @Hardik: Thanks for the appreciation.

    commented on Oct 17 2012 2:30AM
    Chintak Chhapia
    40 · 5% · 1477

Your Comment


Sign Up or Login to post a comment.

"How Index_id are generated..." rated 5 out of 5 by 4 readers
How Index_id are generated... , 5.0 out of 5 based on 4 ratings
    Copyright © Rivera Informatic Private Ltd Contact us      Privacy Policy      Terms of use      Report Abuse      Advertising      [ZULU1097]