Out of Interest - 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)

Out of Interest

Author

Topic

elwoos
Master Smack Fu Yak Hacker

2052 Posts

Posted - 2003-10-01 : 08:47:40

Can you put an index (in particular a clustered index) on a temporary table. I want to do this as I want to update a main table based on whether the code in the temporary table is in the main table. The main table has about 70000 records and the temporary table has about 8000

My code is something like

UPDATE Table1
SET Table1.Field = -1
WHERE Table1.PK_field not in (SELECT OnlyField from #TempTable)

I'm not clear that indexing will produce any advantage. It can be re-written so that the temporary table has the opposite of the data it has at the moment which would turn the where clause into

WHERE Table1.PK_field in (SELECT OnlyField from #TempTable)

(or the equivalent with EXISTS - though I never could get on with EXISTS).

If anyone has any advice or suggestions I would be interested

thanks

steve

Steve no function beer well without

JustinBigelow
SQL Gigolo

1157 Posts

Posted - 2003-10-01 : 09:44:46

You can create an index on a temporary table...


use tempdb
go
create table #names
(
	somename varchar(50)
)
go
create clustered index myindex on #names(somename)
go
select * from sysindexes where name='myindex'

Justin

"Look at it out there orbiting like it's so cool, we will rule it with an army of replicants!"

Nazim
A custom title

1408 Posts

Posted - 2003-10-01 : 10:45:32

Exists are more faster then in

UPDATE Table1 t
SET Table1.Field = -1
WHERE Table1.PK_field EXISTS (SELECT OnlyField from #TempTable where pk_field=t.Pk_Field)

you should have a look at EXISTS at BOL. if you still have doubts. Post here.

-------------------------
What lies behind you and what lies ahead of you are small matters compared to what lies within you.-Ralph Waldo Emerson

elwoos
Master Smack Fu Yak Hacker

2052 Posts

Posted - 2003-10-02 : 14:18:58

That's great, thanks to both for the replies

steve

Steve no function beer well without

mohdowais
Sheikh of Yak Knowledge

1456 Posts

Posted - 2003-10-02 : 14:49:12

This should do pretty good as well:

UPDATE t1
SET t1.Field = -1
FROM Table1 t1
LEFT JOIN #TempTable t2
ON t1.PK_column = t2.OnlyField
WHERE t2.OnlyField IS NULL

Owais

Make it idiot proof and someone will make a better idiot

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources