Problem creating Index for Temporary Table

Problem creating Index for Temporary Table

Post by Tomas Santandreu Polanc » Sat, 01 Feb 1997 04:00:00



Let me explain my problem a bit more.

If I issued the following in the query window,the create index works great
select * into #tomas from amsclaim where 1=2
go
create index tomas1 on #tomas(deano)
go
select * from #tomas(index=tomas1)
go

But, if I create an index for a temporary table in a sp , then , this is
the case where I receiving the error message.

Quote:> >       Msg 308, Level 16, State 1
> >       Index 'uws1' on table '#deano_list' (specified in the FROM clause)
does
> > not exist.

my SP is as follow


as
begin

select amsclaim.deano

into #deano_list

from    amsclaim(INDEX = rxfill_payor)



        (amsclaim.nosub = '1')

execute(create index uws1 on #deano_list(deano))

select * from #deano_list(index = uws1)


begin
  return 0
end
return 1
end
GO

Please help me. Thank you very much
Tomas S Polanco
Sanpoco Computer System

 
 
 

1. Please help: Creating an index on a temporary table

Hi,

I wish to create a simple nonclustered index on a temporary table.
I've read that it it just like creating an index on a normal table,
e.g.

CREATE INDEX IX_MyTemporaryTable_MyColumn ON
#MyTemporaryTable(MyColumn)

Is there a need to check for the existence of the index in the
sysindexes table or does it apply to indexes on temporary tables, i.e.
should I include/exclude the following

IF EXISTS (SELECT name FROM sysindexes WHERE name =
'IX_MyTemporaryTable_MyColumn')
DROP INDEX #MyTemporaryTable.IX_MyTemporaryTable_MyColumn

Is it better to create the index after you have entered data into the
table so the inserts are quicker - the alternative being before? The
only reason I'm creating the index is to hopefully speed up things in
subsequent selects I have to perform on this temporary table.

On something that may be a bit harder to answer:

The data in the temporary table originates from a few other tables. I
have to get distinct values across all these tables.

For example
Table A may contain the following entries
CustomerID 100
CustomerID 200
CustomerID 200

Table B may contain the following entries
CustomerID 100
CustomerID 300
CustomerID 300

Table C may contain the following entries
CustomerID 400
CustomerID 500
CustomerID 500

Is it likely to make a difference (or is it impossible to say) if I
select the minimum number of rows from these other tables first (using
the GROUP clause), insert them into the temporary table, then select
the distinct rows from the temporary table, e.g.

(i) Insert CustomerID 100 and 200 from Table A, 100 and 300 from B,
and 400 and 500 from C into temporary table after performing
individual group bys on tables A, B and C.
(ii) Then select distinct rows from temporary table, giving me
Customer IDs 100,200,300,400,500

OR

should I insert all relevant rows from these other tables, then get
the distinct rows e.g.

(i) Insert CustomerID 100, 200, 200 from Table A, 100, 300, 300 from
B, and 400, 500, 500 from C into temporary table
(ii) Select distinct rows from temporary table, giving me Customer IDs
100,200,300,400,500

The first method would mean less inserts but more group bys then the
second method. The fields on which I'd be performing the group bys
have appropriate indexes.

I appreciate this may be difficult to answer with the limited
information given but any input with reasons would be appreciated.
Please don't criticise me too much for using temporary tables or for
using the approach I have but I'd have to go into a lot more detail
first.

Thanks,

Paul

2. return failure from sp_xml_removedocument and I don't know why.

3. Revise question on Creating an Index on temporary table

4. Dynamic Sql Q (esqlc)

5. Creating Index in Temporary table

6. VB upgrades - again

7. help Creating Index on Temporary Tables

8. Create view connecting to sybase via odbc

9. Create Index for temporary table

10. Create Index to Temporary Table

11. Creating Index for Temporary tables

12. Temporary Table: Implicitely created index not

13. Temporary Table: Implicitely created index not shown in \d i