Creating temp table in SP - what about indexes ?

I am creating temporary tables in a Stored Procedure, to create paging for a
web frontend.
I do this by creating a temp table with a "identity" field, then copying all
relevant data into the temp table, and then in the end I select out the
actual "page" from the total temp table, ie. from record ID 100 to 150.
During this process, I was wondering if my temp table should have an index,
for optimal performance ?
If my temp table has ie. 5000 records, and I want to select and return only
records from 3500 to 3550, I select with a "where clause" specifying only
records from 3500 to 3550, using the identity field, which automatically
works as a "record counter" for my totalt recordset.
But should I create an index on the identity field, before filling the temp
table with records, and then selecting the actual page to return ?
I mean, if I query using a where clause specifying an column without an
index, wouldnt this create table scans ?
-
Regards,
Tony G.I would think creating the clustered index before filling the table would
give you optimal performance as the data is ordered on insert.
Test each scenario and then you'll be satisfied that you have the best metho
d.
regards,
Mark Baekdal
http://www.dbghost.com
http://www.innovartis.co.uk
+44 (0)208 241 1762
Build, Comparison and Synchronization from Source Control = Database change
management for SQL Server
"Tony Godt" wrote:

> I am creating temporary tables in a Stored Procedure, to create paging for
a
> web frontend.
> I do this by creating a temp table with a "identity" field, then copying a
ll
> relevant data into the temp table, and then in the end I select out the
> actual "page" from the total temp table, ie. from record ID 100 to 150.
> During this process, I was wondering if my temp table should have an index
,
> for optimal performance ?
> If my temp table has ie. 5000 records, and I want to select and return onl
y
> records from 3500 to 3550, I select with a "where clause" specifying only
> records from 3500 to 3550, using the identity field, which automatically
> works as a "record counter" for my totalt recordset.
> But should I create an index on the identity field, before filling the tem
p
> table with records, and then selecting the actual page to return ?
> I mean, if I query using a where clause specifying an column without an
> index, wouldnt this create table scans ?
>
> -
> Regards,
> Tony G.|||> I mean, if I query using a where clause specifying an column without an
> index, wouldn't this create table scans ?
Correct.
I'm a bit through. You say that you create a temp table in the proc
. But how do you re-use
this temp table? I hope that you don't create the temp table, populate it an
d then select from it
each time a user want to display a page?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Tony Godt" <TonyGodt@.discussions.microsoft.com> wrote in message
news:1EFE7BC9-C4A4-4902-84C3-0B79B78A58FA@.microsoft.com...
>I am creating temporary tables in a Stored Procedure, to create paging for
a
> web frontend.
> I do this by creating a temp table with a "identity" field, then copying a
ll
> relevant data into the temp table, and then in the end I select out the
> actual "page" from the total temp table, ie. from record ID 100 to 150.
> During this process, I was wondering if my temp table should have an index
,
> for optimal performance ?
> If my temp table has ie. 5000 records, and I want to select and return onl
y
> records from 3500 to 3550, I select with a "where clause" specifying only
> records from 3500 to 3550, using the identity field, which automatically
> works as a "record counter" for my totalt recordset.
> But should I create an index on the identity field, before filling the tem
p
> table with records, and then selecting the actual page to return ?
> I mean, if I query using a where clause specifying an column without an
> index, wouldnt this create table scans ?
>
> -
> Regards,
> Tony G.