Insert Into {Temp Table } Exec {Stored Procedure}

• Mahesh Bote

  SSCrazy Eights

  Points: 9107

  More actions

  June 23, 2008 at 6:26 am

  #70866

  [font="Verdana"]Hi,

  After creating Temp Table inside SProc, I created Unique Clustered Index on Temporary table. Later I use to insert the resultset from SProc into temporary table. Then I had one Select statement with inner joins on some other tables, including temp table.

  When I view the execution plan, it is showing me two query texts. 1 for Insert Into {Temp Table} Exec {SProc} And the 2nd one is for Select statement. The 2nd query is using Unique Clustered Index but not the 1st query. It is showing Table Scan though it has Unique Clustered Index on it.

  Can anybody tell me the reason behind it? Why the Isert statement is not using Unique Clustered Index? Do it is happening due to using the resultset from SProc?

  Thanks in advance,

  Mahesh[/font]

  MH-09-AM-8694

• Mahesh Bote

  SSCrazy Eights

  Points: 9107

  More actions

  June 23, 2008 at 6:39 am

  #832608

  [font="Verdana"]I got the answer. At 1st occurance Temp Table does not have data, so it obviously going to Scan the table.

  Mahesh[/font]

  MH-09-AM-8694