joining 2 tables together - 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

SQL Server Development (2000)

joining 2 tables together

Author

Topic

sengoku
Starting Member

29 Posts

Posted - 2004-04-16 : 11:14:05

hello there

i have a database table that contains a lot of records. and it's queried quite a bit. sometimes these records expire, but we need to keep them stored for future retrieval.

but the table consequently gets quite big.

however, i had an idea about speeding it up. i would like to split the table into 2 tables, one which just has the 'current' records and one which has the 'expired' records.

now, what i want to know is : is there any point to this? and what's the easiest way to do it? i was thinking of making a quick query that selects all from the 2 tables UNION ALL'd, with fields from the 'expired' table named to match the ones in the 'current' table. like this

current table :
articleID (autokey)
articleName
articleText
etc etc

expired table :
expiredID (autokey)
articleID (copied from 'current' when record is moved)
articleName
articleText
etc etc

select * from (currentTable UNION ALL expiredTable) where articleName like '...'

will this work? will they join together to form 1 LONG table? and more to the point, can i do this :

select * from (exec getTotalTable) where articleName like '...'

(where getTotalTable is a stored proc doing the table join) - i have a feeling that'd be faster than just a UNION, especially as i can do clever checking in the stored proc.

the other thing is, sometimes i only have the articleID number to work with - can i do a stored proc like so

select * from currentTable where articleID=@requestedArticle
if @@FETCH_STATUS<>0
select * from expiredTable where articleID=@requestedArticle

?

i know this is quite complicated, but i hope you understand what i mean. any help is greatly appreciated :)

thanks!

X002548
Not Just a Number

15586 Posts

Posted - 2004-04-16 : 11:33:41

Look up

Horizintal partitioning

And Partitioned Views..

in BOL

And yes you can do this....

However it's...

select * from currentTable
UNION ALL
select * from expiredTable

And don't do SELECT *

And I wouldn't have an expiredid identity column...I'd have a dattime column to tell me when it moved...

Brett

8-)

sengoku
Starting Member

29 Posts

Posted - 2004-04-16 : 11:49:37

nice one brett, thanks. that pretty much answered my question :)

last thing, can i do something like this

select * from (exec getTotalTable) where...

where getTotalTable is a stored proc that does 'clever' joining?

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2004-04-16 : 12:06:32

I'd also stay away from cursors....as indicted by the use of "@@FETCH_STATUS"
good performance and cursors don't go in the same sentance.....

mohdowais
Sheikh of Yak Knowledge

1456 Posts

Posted - 2004-04-16 : 14:13:49

>>select * from (exec getTotalTable) where...

No you cannot do that, but you can do this:


CREATE TABLE yada (...)
INSERT INTO yada EXEC myProcGetTotalTable
SELECT * FROM yada WHERE foo = 1

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources