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.

Creating table with Identity property

Hi, if I want to create a table with IDENTITY(1,1) property and do not
state any PK or simular constraints. Does that IDENTITY serve as a
unique identifier to that tabel or is it nessecary to implement some
constraints as well'
I'm new to all this with SQL and trying to learn about it!
Ex:
CREATE TABLE Employee
{
EMPID int NOT NULL IDENTITY(1,1),
EMPNAME varchar(8) NOT NULL,
EMPADDRESS varchar(30) NULL
}zekevarg skrev:

> Hi, if I want to create a table with IDENTITY(1,1) property and do not
> state any PK or simular constraints. Does that IDENTITY serve as a
> unique identifier to that tabel or is it nessecary to implement some
> constraints as well'
>
The IDENTITY property does not impose any constraints on its own, you
can do a search on Google for IDENTITY in this news group and you'll
get a lot to read :)
/impslayer, aka Birger Johansson|||zekevarg wrote:
> Hi, if I want to create a table with IDENTITY(1,1) property and do not
> state any PK or simular constraints. Does that IDENTITY serve as a
> unique identifier to that tabel or is it nessecary to implement some
> constraints as well'
> I'm new to all this with SQL and trying to learn about it!
> Ex:
> CREATE TABLE Employee
> {
> EMPID int NOT NULL IDENTITY(1,1),
> EMPNAME varchar(8) NOT NULL,
> EMPADDRESS varchar(30) NULL
> }
I answered this in the thread you started yesterday:
http://groups.google.co.uk/group/mi...372625141cc0196
You should always add a UNIQUE or PRIMARY KEY constraint to an IDENTITY
column if you want it to be unique. Typically IDENTITY will be declared
as the primary key and used as the referenced column in foreign key
constraints.
However, even where you do put a key constraint on an IDENTITY column
it shouldn't be the ONLY key of a table. That's because IDENTITY won't
prevent duplicate data in the table. Also it isn't a good idea to
expose IDENTITY columns to end users (one reason being that you can't
ever update the column) - therefore if IDENTITY is the only key your
users will not have a useable key at all.
That's my opinion. It's true that many people will have differing
opinions on this topic. I would urge you however to read and understand
relational design theory and principles such as normalization rather
than just listen to advice in newsgroups (including my own). You can
make better decisions and implement better solutions if you understand
the fundamentals rather than basing your knowledge only on examples and
tips.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||>> if I want to create a table with IDENTITY(1,1) property and do not state
any PK or simular constraints. <<
Then **by definition** it is not a table. A key must be made up of a
unique subset of the atributes of the entity, not the internal state of
a counter in the hardware that stores the data.
No, it is an exposed physical locator -- like a record number was in a
1950's mag tape file system. Ask yourself if the IDENTITY would be
different if you inserted the same data in a different order (yes!0,
then ask yourself if you part numbers would stay the same irregardless
of the order of insertion (yes, of course!). That is because a part
number is a key and an IDENTITY is not.
There is no "as well" on this. You will never use an IDENTITY in a
properly designed RDBMS.
It is a whole different model of data and it will take you awhile to
get it. Think abstract, no physical; think sets and not sequences;
think declarative, not procedural (i.e. whar, not how) and think
parallel, not sequential.|||FWIW, I thought you should know that "irregardless" is not a proper
word in the english language.
check out: http://en.wiktionary.org/wiki/irregardless
We all expect perfection from you, as you do from us.|||On 16 Mar 2006 07:25:20 -0800, "JeffB" <jeff.bolton@.citigatehudson.com>
wrote:
in <1142522719.973281.149580@.j52g2000cwj.googlegroups.com>

>FWIW, I thought you should know that "irregardless" is not a proper
>word in the english language.
>check out: http://en.wiktionary.org/wiki/irregardless
>We all expect perfection from you, as you do from us.
While I agree with your pedantic point, regretfully the word
irregardless has crept into the language by virtue of its use. Ack!
As you can see in the article you've pointed to, its status has already
risen from not-a-word to non-standard. Another 10-15 years and it
~will~ be a word. :-(
This posting is provided "AS IS" with no warranties, no guarantees, and no c
onferred rights.
Stefan Berglund|||In some parts of the country, "irregardless" is the word used when folks
want to show off using a 50 cent word.
that's scary.
-Paul
"Stefan Berglund" <sorry.no.koolaid@.for.me> wrote in message
news:i08j12te8qbvmc0i57jlhq90jh5jg05d7f@.
4ax.com...
> On 16 Mar 2006 07:25:20 -0800, "JeffB" <jeff.bolton@.citigatehudson.com>
> wrote:
> in <1142522719.973281.149580@.j52g2000cwj.googlegroups.com>
>
> While I agree with your pedantic point, regretfully the word
> irregardless has crept into the language by virtue of its use. Ack!
> As you can see in the article you've pointed to, its status has already
> risen from not-a-word to non-standard. Another 10-15 years and it
> ~will~ be a word. :-(
> --
> This posting is provided "AS IS" with no warranties, no guarantees, and no
> conferred rights.
> Stefan Berglund|||>> Think abstract, no physical
I like the white board as much as anyone, but just remember that abstract
has never held any actual data. Every data table that has actually had a
practical use has been, and will always be, a physical table. The relational
abstract at some point has to be converted into the physical SQL.
-Paul Nielsen
SQL Server MVP
www.SQLServerBible.com
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1142521746.581278.276740@.u72g2000cwu.googlegroups.com...
> Then **by definition** it is not a table. A key must be made up of a
> unique subset of the atributes of the entity, not the internal state of
> a counter in the hardware that stores the data.
>
> No, it is an exposed physical locator -- like a record number was in a
> 1950's mag tape file system. Ask yourself if the IDENTITY would be
> different if you inserted the same data in a different order (yes!0,
> then ask yourself if you part numbers would stay the same irregardless
> of the order of insertion (yes, of course!). That is because a part
> number is a key and an IDENTITY is not.
>
> There is no "as well" on this. You will never use an IDENTITY in a
> properly designed RDBMS.
>
> It is a whole different model of data and it will take you awhile to
> get it. Think abstract, no physical; think sets and not sequences;
> think declarative, not procedural (i.e. whar, not how) and think
> parallel, not sequential.
>|||Paul Nielsen (MVP) wrote:
> I like the white board as much as anyone, but just remember that abstract
> has never held any actual data. Every data table that has actually had a
> practical use has been, and will always be, a physical table. The relation
al
> abstract at some point has to be converted into the physical SQL.
A table is part of the logical data model, not the physical one. DATA
has to have a physical representation in storage of course, but the
principle of Physical Data Independence means that there is absolutely
no reason why a table itself has to have a direct physical counterpart.
The only requirement is that the DBMS can construct the results of
queries using persisted data and metadata.
SQL Server's data structures are such that (loosely speaking) you can
point to a set of pages in files and say "that's a table" but the same
isn't true in all other SQL databases (column-based storage is one
alternative for example).
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--

Creating Identity Field

Below is a simple CREATE TABLE statement in my DTS job that drops myTable,
recreates it and then transfers data from myTable in database 1 to the
myTable in destination database. Given the fact that myTable contains data
and that the causeID field may not begin with a 1 value, what parameters
should I use so SQL will not change or start with causeID = 1?
I just want my data to import and retain whatever identity values it has. Do
I have to specify the (1,1) after IDENTITY?
CODE:
CREATE TABLE [myDatabase].[dbo].[myTable] (
[causeID] int IDENTITY(1,1) NOT NULL,
[causeCode] varchar (3) NULL,
[causeName] varchar (50) NULL
)Scott wrote:

> I just want my data to import and retain whatever identity values it
> has. Do I have to specify the (1,1) after IDENTITY?
> CODE:
>
> CREATE TABLE [myDatabase].[dbo].[myTable] (
> [causeID] int IDENTITY(1,1) NOT NULL,
> [causeCode] varchar (3) NULL,
> [causeName] varchar (50) NULL
> )
You can use
Set IDENTITY_INSERT [myTable] ON
then insert the data and afterwards
Set IDENTITY_INSERT [myTable] OFF
New data which will be inserted later will get the correct identities.
HTH,
Stijn Verre^t.|||Why are you using IDENTITY at all? Why is "cause_code" not the proper,
relational key'|||causeID is the primary key, causeCode is a text field code that a user
enters.
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1133140303.738683.141210@.g43g2000cwa.googlegroups.com...
> Why are you using IDENTITY at all? Why is "cause_code" not the proper,
> relational key'
>|||Let me help out with Celko's angle here.
If I type causeCode = "foobar" and so does Celko, why do we have different
causeID values?
Now, let me say that I am not against using Identity (or a similar surrogate
key approach), but I do believe that causeCode should at least be unique /
non-repeating.
A
"Scott" <sbailey@.mileslumber.com> wrote in message
news:ex2v1t88FHA.3048@.TK2MSFTNGP10.phx.gbl...
> causeID is the primary key, causeCode is a text field code that a user
> enters.
> "--CELKO--" <jcelko212@.earthlink.net> wrote in message
> news:1133140303.738683.141210@.g43g2000cwa.googlegroups.com...
>|||Probably something like this...
CREATE TABLE [myDatabase].[dbo].[myTable] (
[causeID] int IDENTITY(1,1) NOT NULL constraint SK_myTable unique
clustered,
[causeCode] varchar (3) NULL constraint PK_myTable primary key
nonclustered,
[causeName] varchar (50) NULL
)
This then satisfies a natural key and surrogate key, you would then use
causeID as the foreign key in other tables...
CREATE TABLE myAudit (
causeID int not null references myTable( causeID )
...
)
rather than...
CREATE TABLE myAudit (
causeCode varchar(3) not null references myTable( causeCode )
)
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:eudsZJ98FHA.1184@.TK2MSFTNGP12.phx.gbl...
> Let me help out with Celko's angle here.
> If I type causeCode = "foobar" and so does Celko, why do we have different
> causeID values?
> Now, let me say that I am not against using Identity (or a similar
> surrogate key approach), but I do believe that causeCode should at least
> be unique / non-repeating.
> A
>
> "Scott" <sbailey@.mileslumber.com> wrote in message
> news:ex2v1t88FHA.3048@.TK2MSFTNGP10.phx.gbl...
>|||I think Scott simply has terminology mixed up which isn't a crime, some
people confuse a 'relational key' with a the standard Natural Key.
CREATE TABLE [myDatabase].[dbo].[myTable] (
[causeID] int IDENTITY(1,1) NOT NULL constraint SK_myTable unique
clustered,
[causeCode] varchar (3) NULL constraint PK_myTable primary key
nonclustered,
[causeName] varchar (50) NULL
)
This then satisfies a natural key and surrogate key, you would then use
causeID as the foreign key in other tables...
CREATE TABLE myAudit (
causeID int not null references myTable( causeID )
...
)
rather than...
CREATE TABLE myAudit (
causeCode varchar(3) not null references myTable( causeCode )
)
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1133140303.738683.141210@.g43g2000cwa.googlegroups.com...
> Why are you using IDENTITY at all? Why is "cause_code" not the proper,
> relational key'
>