index temp tables?

Howdy all. Ive got some SP's that make heavy use of temp tables. So when I
look at the Execution Plan I see about 25 table scans on these guys. The Row
Count and Estimated Row Size are usually only about 3-120 rows though. Do you
guys Index temp tables this size?
TIA,
ChrisR
Depends on the row size to some degree but usually tables that small it
won't matter much. Most if not all the rows are on a single page anyway.
But the only way to know for sure is to test it with and without to see if
the gain is worth the time and effort to create the indexes.
Andrew J. Kelly SQL MVP
"ChrisR" <ChrisR@.discussions.microsoft.com> wrote in message
news:A23705AA-6B95-43E0-877A-05B1E457DE27@.microsoft.com...
> Howdy all. Ive got some SP's that make heavy use of temp tables. So when I
> look at the Execution Plan I see about 25 table scans on these guys. The
> Row
> Count and Estimated Row Size are usually only about 3-120 rows though. Do
> you
> guys Index temp tables this size?
>
> --
> TIA,
> ChrisR

index temp tables?

Howdy all. Ive got some SP's that make heavy use of temp tables. So when I
look at the Execution Plan I see about 25 table scans on these guys. The Row
Count and Estimated Row Size are usually only about 3-120 rows though. Do yo
u
guys Index temp tables this size?
TIA,
ChrisRDepends on the row size to some degree but usually tables that small it
won't matter much. Most if not all the rows are on a single page anyway.
But the only way to know for sure is to test it with and without to see if
the gain is worth the time and effort to create the indexes.
Andrew J. Kelly SQL MVP
"ChrisR" <ChrisR@.discussions.microsoft.com> wrote in message
news:A23705AA-6B95-43E0-877A-05B1E457DE27@.microsoft.com...
> Howdy all. Ive got some SP's that make heavy use of temp tables. So when I
> look at the Execution Plan I see about 25 table scans on these guys. The
> Row
> Count and Estimated Row Size are usually only about 3-120 rows though. Do
> you
> guys Index temp tables this size?
>
> --
> TIA,
> ChrisR

index temp tables?

Howdy all. Ive got some SP's that make heavy use of temp tables. So when I
look at the Execution Plan I see about 25 table scans on these guys. The Row
Count and Estimated Row Size are usually only about 3-120 rows though. Do you
guys Index temp tables this size?
--
TIA,
ChrisRDepends on the row size to some degree but usually tables that small it
won't matter much. Most if not all the rows are on a single page anyway.
But the only way to know for sure is to test it with and without to see if
the gain is worth the time and effort to create the indexes.
--
Andrew J. Kelly SQL MVP
"ChrisR" <ChrisR@.discussions.microsoft.com> wrote in message
news:A23705AA-6B95-43E0-877A-05B1E457DE27@.microsoft.com...
> Howdy all. Ive got some SP's that make heavy use of temp tables. So when I
> look at the Execution Plan I see about 25 table scans on these guys. The
> Row
> Count and Estimated Row Size are usually only about 3-120 rows though. Do
> you
> guys Index temp tables this size?
>
> --
> TIA,
> ChrisRsql

Index Problem

Greetings,

In a SQL Query window (2000) I can create a #temp table and then create an index on the # temp table with this code:

create clustered index IX_UpinNbr on #temp20 (UpinNbr)

go

However when I try to compile the same code within a Stored Procedure, I get the following error:

Msg 208, Level 16, State 0, Line 4

Invalid object name '#temp20'.

I realize that the #temp20 table is not a physical table in my database. Can you put an index on a #temp table within a stored procedure?

Thanks!

Tom

Can you post the exact code you are using?

I wonder if you are altering that temporary table after cteating it.

Code Snippet

use northwind

go

createprocedure dbo.p1

as

setnocounton

createtable #t (

Record# intnotnullunique,

DateStamp datetimenotnull

)

createuniqueclusteredindex #t_Record#_u_c_ix

on #t(Record#)

insertinto #t values(1,'6/14/2007 1:00')

insertinto #t values(2,'6/14/2007 2:00')

insertinto #t values(3,'6/14/2007 2:10')

insertinto #t values(4,'6/14/2007 5:10')

select*from #t

droptable #t

go

exec dbo.p1

go

dropprocedure dbo.p1

go

AMB

|||

Here is the entire Stored Procedure script:

set ANSI_NULLS ON

set QUOTED_IDENTIFIER ON

go

ALTERPROCEDURE [dbo].[proc_san_prov_hr_rpt] AS

-- Created 06/13/2007 by TLeach, CR# C7095, PDAT 1011 Sanctioned Provider Report - Hospital Roster.

-- Hospital Roster

Select a.temp_tracking_number,

a.TaxIDNumber,

a.Section1011IdentificationNbr,

a.LegalBusinesssName,

a.DBAName,

a.AuthorizedRepFirstName,

a.AuthorizedRepLastName,

a.PhysicalAddressLine1,

a.PhysicalAddressLine2,

a.PhysicalCity,

a.PhysicalState,

a.PhysicalZipCode,

b.UpinNbr,

b.PhysicianName

Into #temp20

From dbo.EnrollmentApplication a, dbo.HospitalRoster b

Where a.StatusID ='6'and

a.SecondaryStatus ='FA'and

a.ID = B.ApplicationID

go

createclusteredindex IX_UpinNbr on #temp20 (UpinNbr)

go

-- Now join #temp20 to the cumulative table to check for matches.

-- #temp21 will have PhysicianName and busname matches.

Select a.temp_tracking_number AS'PDAT Temptracknbr',

a.TaxIDNumber AS'PDAT TaxIDNumber',

a.Section1011IdentificationNbr AS'PDAT Section1011nbr',

b.SSN AS'CMS SSN',

a.LegalBusinesssName AS'PDAT LegalBusinessName',

a.DBAName AS'PDAT DBAName',

a.AuthorizedRepFirstName AS'PDAT AuthorizedRepFirstName',

a.AuthorizedRepLastName AS'PDAT AuthorizedRepLastName',

a.PhysicalAddressLine1 AS'PDAT PhysicalAddressLine1',

a.PhysicalAddressLine2 AS'PDAT PhysicalAddressLine2',

a.PhysicalCity AS'PDAT PhysicalCity',

a.PhysicalState AS'PDAT PhysicalState',

a.PhysicalZipCode AS'PDAT PhysicalZipCode',

a.UpinNbr AS'PDAT UPINNbr',

a.PhysicianName AS'PDAT PhysicianName',

'HR Physician Name'AS'Matched On'

Into #temp21

From #temp20 a,

dbo.[0406Cumulative] b

Where(a.PhysicianName = b.busname)

-- #temp22 will have upin matches. (11)

Select a.temp_tracking_number AS'PDAT Temptracknbr',

a.TaxIDNumber AS'PDAT TaxIDNumber',

a.Section1011IdentificationNbr AS'PDAT Section1011nbr',

b.SSN AS'CMS SSN',

a.LegalBusinesssName AS'PDAT LegalBusinessName',

a.DBAName AS'PDAT DBAName',

a.AuthorizedRepFirstName AS'PDAT AuthorizedRepFirstName',

a.AuthorizedRepLastName AS'PDAT AuthorizedRepLastName',

a.PhysicalAddressLine1 AS'PDAT PhysicalAddressLine1',

a.PhysicalAddressLine2 AS'PDAT PhysicalAddressLine2',

a.PhysicalCity AS'PDAT PhysicalCity',

a.PhysicalState AS'PDAT PhysicalState',

a.PhysicalZipCode AS'PDAT PhysicalZipCode',

a.UpinNbr AS'PDAT UPINNbr',

a.PhysicianName AS'PDAT PhysicianName',

'HR Upin'AS'Matched On'

Into #temp22

From #temp20 a,

dbo.[0406Cumulative] b

Where(a.UpinNbr = b.Upin)

Select*

Into #temp23

From #temp21

Union

Select*

From #temp22

Select*from #temp23

Here is the exact error message when I cry to create:

Msg 1906, Level 11, State 1, Line 2

Cannot create an index on '#temp20', because this table does not exist in database 'PDAT_1011'.

Msg 208, Level 16, State 0, Line 4

Invalid object name '#temp20'.

Thanks!

|||

When the code is compiled, the temp table does not yet exist -since it is created by the SELECT...INTO.

IF you want to create the index, then CREATE the table first.

|||Thanks for the help.|||

Why are you using "go" inside the sp?

...

Select a.temp_tracking_number,

a.TaxIDNumber,

a.Section1011IdentificationNbr,

a.LegalBusinesssName,

a.DBAName,

a.AuthorizedRepFirstName,

a.AuthorizedRepLastName,

a.PhysicalAddressLine1,

a.PhysicalAddressLine2,

a.PhysicalCity,

a.PhysicalState,

a.PhysicalZipCode,

b.UpinNbr,

b.PhysicianName

Into #temp20

From dbo.EnrollmentApplication a, dbo.HospitalRoster b

Where a.StatusID ='6'and

a.SecondaryStatus ='FA'and

a.ID = B.ApplicationID

createclusteredindex IX_UpinNbr on #temp20 (UpinNbr)

...

AMB

|||

Hi Arnie,

Not really, see example.

createprocedure dbo.p1

as

selectcast(1 asint)as c1

into #t

createindex #t_c1_nu_nc_ix

on #t(c1)

select*from #t

droptable #t

go

exec dbo.p1

go

dropprocedure dbo.p1

go

The problem with his code is that he has a couple of "GO" in the script. The sp is altered an inmediately it is trying to create an index in a table that does not exists.

AMB

|||No reason, forgot to take it out. Started as a .sql script...|||

I commented out the 'GO' statements and the Stored Procedure compiled cleanly.

Thanks for the help!

|||

I took out the 'GO' statements and the stored procedure compiled and ran cleanly.

Thanks for the help!

|||Yes, I noticed the GO statements, THAT is why the #temp table is not created. He just created the sproc with the SELECT...INTO. Then when running the CREATE INDEX statement, the #temp table did not exist. -I think I said something about it not in existance.

Index on temp table?

Hi,
Had a quick look around BOL and can't seem to find whether one can create an
index on a temp table?
Can anyone shed any light on this, please?
Cheers, SimonHi Simon,
You can create index on tem table. To view the information about index
please look into tempdb.
Regards,
Jagan Mohan
MCDBA
"SBeetham" wrote:

> Hi,
> Had a quick look around BOL and can't seem to find whether one can create
an
> index on a temp table?
> Can anyone shed any light on this, please?
> Cheers, Simon
>
>|||Ok, Cool... I can see the tempdb info... Thanks.
"Jagan Mohan" wrote:
> Hi Simon,
> You can create index on tem table. To view the information about index
> please look into tempdb.
> Regards,
> Jagan Mohan
> MCDBA
> "SBeetham" wrote:
>

Index on a Temp Table?

Hi,
Had a quick look around BOL and can't seem to find whether one can create an
index on a temp table?
Can anyone shed any light on this, please?
Cheers, Simonit's the same syntax as creating an index on a regular table.|||yes on # (local) and ## (global) prefixed tables. They are just tables and
can have everything that normal table have.
@. (local variable) tables can only have constraints defined in the DDL.
----
Louis Davidson - drsql@.hotmail.com
SQL Server MVP
Compass Technology Management - www.compass.net
Pro SQL Server 2000 Database Design -
http://www.apress.com/book/bookDisplay.html?bID=266
Blog - http://spaces.msn.com/members/drsql/
Note: Please reply to the newsgroups only unless you are interested in
consulting services. All other replies may be ignored :)
"SBeetham" <sbeetham@.xtra.co.nz> wrote in message
news:urISWzsHFHA.3624@.tk2msftngp13.phx.gbl...
> Hi,
> Had a quick look around BOL and can't seem to find whether one can create
> an
> index on a temp table?
> Can anyone shed any light on this, please?
> Cheers, Simon
>
>|||SBeetham wrote:
> Hi,
> Had a quick look around BOL and can't seem to find whether one can
> create an index on a temp table?
> Can anyone shed any light on this, please?
> Cheers, Simon
You can create one.
Create Table #test (col1 int)
Create unique Clustered Index test_idx on #test(col1)
David Gugick
Imceda Software
www.imceda.com|||Even though it might not find its place on BOL, it is quite possible for
temp and global temp tables.
HTH,
Vinod Kumar
MCSE, DBA, MCAD, MCSD
http://www.extremeexperts.com
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp
"SBeetham" <sbeetham@.xtra.co.nz> wrote in message
news:urISWzsHFHA.3624@.tk2msftngp13.phx.gbl...
> Hi,
> Had a quick look around BOL and can't seem to find whether one can create
an
> index on a temp table?
> Can anyone shed any light on this, please?
> Cheers, Simon
>
>|||From the Books Online article "CREATE INDEX":
"Indexes can be created on a temporary table."
Steve Kass
Drew University
SBeetham wrote:

>Hi,
>Had a quick look around BOL and can't seem to find whether one can create a
n
>index on a temp table?
>Can anyone shed any light on this, please?
>Cheers, Simon
>
>
>