How do I use indexes on temptables in SQLServer7

• mikaellowgren

  SSC Rookie

  Points: 38

  More actions

  November 5, 2002 at 9:22 am

  #79654

  I'm trying to use indexes on a temptable used in a storedprocedure.

  The storedprocedure does this:

  1. create the temptable

  2. do some inserts

  3. create 2 indexes (nonclustred)

  4. delete some rows

  5. create 1 clustered index

  6. sometimes delete, allways updates and inserts

  The strangest things occure to me when I do this.

  If I add a drop index between (3.) and (4.) and end with a last drop on the third index, I get results with data in the wrong collumn, different errors including one that suggest i should use DBCC CHECKTABLE on something (i have to recreate the storedprocedure when that one comes).

  Are indexes created on temptables scooped to only that table or is it possible that if two diffrent sessions share the same indexes?

  I can't understand a thing about this.

  Is it because all temptables (in different sessions) have indexes with the same name?

  Allthough it seams to work when I skip the dropping of indexes...

  Thanks for any imput.

  /Mikael

  Edited by - mikaellowgren on 11/05/2002 09:23:06 AM

• Mkumari

  Hall of Fame

  Points: 3498

  More actions

  November 5, 2002 at 9:35 am

  #440343

  Try creating Indexes with timestamp in the Index name so that whenever a new Index is created the name will be unique for the Index.This way I think you can test it if it is hapenning because of Indexes .

• mikaellowgren

  SSC Rookie

  Points: 38

  More actions

  November 5, 2002 at 9:53 am

  #440349

  quote:

  ---

  Try creating Indexes with timestamp in the Index name

  ---

  I tried but I guess I can't figure out how to give an index a variable name...

  Something like this I guess (but this don't work)

  DECLARE @indexname varchar(12)

  SELECT @indexname = 'hej_baberiba'

  CREATE INDEX @indexname

  ON #temptable ( col1 )

  Thanks.

• Mkumari

  Hall of Fame

  Points: 3498

  More actions

  November 5, 2002 at 12:32 pm

  #440374

  I think this is what you are looking for.

  DECLARE @indexname varchar(50),@Cmd VARCHAR(500)

  SELECT @indexname = 'hej_baberiba' + CAST(DATEPART(MM,GETDATE()) AS VARCHAR(5))+ CAST(DATEPART(SS,GETDATE()) AS VARCHAR(5)) + CAST(DATEPART(MS,GETDATE()) AS VARCHAR(5))

  PRINT @Indexname

  SELECT @Cmd = 'CREATE INDEX ' + @indexname + ' ON #temptable ( col1 )'

  PRINT @Cmd

  EXEC (@Cmd)