SQL Tips

From SQLBrowser

[edit] Forcing an index by name, when index is created in a proc

• When forcing an index created in a proc on a temp table, you may get a message saying that the index will be ignored because the nme is unknown. This message can be ignored as at run time, the index will still be used. Forcing the index by position would have worked.

   use download 
   go 
   drop proc p1
   go
   create proc p1 as 
      create table #t ( i int ) 
      create index i1 on #t(i) 
      insert #t values ( 1 ) 
      /* at runtime, the next instruction generates a message at initial plan compilation 
      * Index 'i1' specified as optimizer hint in the FROM clause of table '#t' does not exist.  
      * Optimizer will choose another index instead. 
      */
      select * from #t (index i1)  
   go 
   exec p1
   go