Regarding #Table - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Regarding #Table

Author

Topic

CSK
Constraint Violating Yak Guru

489 Posts

Posted - 2006-05-24 : 00:43:16

Hi all,
I have 100000 records to manipulate in to temp table
which one is best #temp table or user temp table why please

Thanks
KK

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-05-24 : 00:52:48

Temp table is table name prefix with #. Do you mean #temp (local) vs ##temp (global) ? And also table variable ?

Why would you have so many records in temp table for processing ?

CSK
Constraint Violating Yak Guru

489 Posts

Posted - 2006-05-24 : 00:57:32

Yes khtan i mean #temp (local). At the time of payroll processing, they have used. I preferd local table like pay_process_tmp, because we have to put index for the table.it retrives the record fastly. if it's a #table means taking time. is it right..?

Thanks
KK

Kristen
Test

22859 Posts

Posted - 2006-05-24 : 01:33:29

You can create indexes on a #TempTable (and you can have a Primary Key [only] on an @TableVar - which may be sufficient?)

Kristen

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-05-24 : 03:02:46

>>which one is best #temp table or user temp table why please

A temp table is the one created by user

Madhivanan

Failing to plan is Planning to fail

CSK
Constraint Violating Yak Guru

489 Posts

Posted - 2006-05-24 : 03:25:49

No need for Primary key. Index is enough. can U give me a Sysntax for creating index for temp table..?

Thanks
KK

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-05-24 : 04:17:52

There isn't any different from creating index for your normal table. Same syntax.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-05-24 : 04:20:31

Read about Alter table in sql server help file

Madhivanan

Failing to plan is Planning to fail

nr
SQLTeam MVY

12543 Posts

Posted - 2006-05-24 : 04:29:26

quote:
Originally posted by Kristen

You can create indexes on a #TempTable (and you can have a Primary Key [only] on an @TableVar - which may be sufficient?)

Kristen

You can create constraints on a table variable so can have a pk and/or unique indexes. The only thing you can't have is a non-unique index but you can get round that by having an identity and using that as the last column in a unique index.

declare @a table
(
i int ,
j int ,
id int identity (1,1) ,
unique (i, id)
)

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

CSK
Constraint Violating Yak Guru

489 Posts

Posted - 2006-05-24 : 04:40:38

thanks khtan and NR

Thanks
KK

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-05-24 : 04:41:04

quote:
Originally posted by nr

quote:
Originally posted by Kristen

You can create indexes on a #TempTable (and you can have a Primary Key [only] on an @TableVar - which may be sufficient?)

Kristen

You can create constraints on a table variable so can have a pk and/or unique indexes. The only thing you can't have is a non-unique index but you can get round that by having an identity and using that as the last column in a unique index.

declare @a table
(
i int ,
j int ,
id int identity (1,1) ,
unique (i, id)
)

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Now id plays as unique column?

Madhivanan

Failing to plan is Planning to fail

nr
SQLTeam MVY

12543 Posts

Posted - 2006-05-24 : 04:49:45

Yep - it has to be a constraint hence a unique index. If you have a column that isn't unique that you want to index then you can add an identity and include that in the constraint making it unique.
Won't be as good an index but might help.

For anything that is big enough to need this I would use a temp table instead as they have more optimisation options so would tend to perform better.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-05-24 : 07:48:31

Thanks Nigel.
If id has to be unique, then why cant you use this?

declare @a table
(
i int ,
j int ,
id int identity (1,1) unique
)

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources