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.
| Author |
Topic |
|
DAMAS
Starting Member
2 Posts |
 Posted - 2010-09-29 : 08:50:21
|
| Dear All,I would be very grateful if anyone could assist with my enquiry.We have two databases LIVE and ARCHIVE, based on a specific date transactions are removed from LIVE and loaded into the ARCHIVE database. Once the archive process has been completed, i.e. COPIED then DELETED, I then update a flag in the LIVE database to indicate a record now resides in the ARCHIVE database.The question I have is, Is it possible to create a single view which would either SELECT directly from the LIVE database if not archived, or create a UNION if it was archived - I don't really want to permanately create a UNION view across two databases for performance reasons as the archive data is rarely queried, however this requirement is still needed. I think what I am trying to ask is... can I create a dynamic view, based on the archive flag?I am grateful for any advice.David. |
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2010-09-29 : 09:37:10
|
| if the date field that you're basing the archiving on is indexed, both in the live and archive tables, and your query utilizes the index AND you use UNION ALL instead of UNION then just create the view. Performance won't be degraded. |
 |
|
|
DAMAS
Starting Member
2 Posts |
Posted - 2010-09-29 : 11:00:00
|
| Thanks Russell for your reply,I'll carry out some benchmark testing before implementing this into our business system.Regards,David. |
|
|
|
|
|
|
Retrieving data over linked server tables