December 7, 2007 at 8:38 am
I have a developer that want his database split into 2.
1 = current year information
2 = is the history info that will never change.
He wants to do insert, delete, change..etc. on the current database only (example 2007) but he wants that his select statement will read both databases. Maintenance like backup and reorg ect. is only applied to the current database. The purpose of this split is to reduce the backup and reorgs etc.. time.
Thanks,
Pete
December 9, 2007 at 3:23 pm
Not sure i fully understand what you want acheive, You can query different database on the same server using the three part name SELECT * FROM dbname.schame.tablename etc if the other db is a diffrent server you need to setup a linked server. if you want data from tables on one and tables on the other then you can using the above combined with a JOIN.
Hope this helps?
Gethyn Elliswww.gethynellis.com
December 9, 2007 at 8:38 pm
You can do a partitioned table or partitioned view that will help here. Look these up in books online and be sure you set the security the same on both databases for the table/view.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
December 11, 2007 at 7:23 am
I have reached the conclusion (possibly unjustified) that splitting a table into history v. current is usually a bad idea, and creates problems like being unable to do a complete select without having to bother with Unions, etc. (Exactly the problem you foresee.) I can't think of any situation where a partitioned table won't buy exactly what you are after.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply