Hi,
I have a very huge transaction table. It degrade my application performance in reporting. I decided to have archieve feature so that user can purge "old" data to another table.

By doing so, I would have problem of my report will not able to retrieve these "old" data. I read some articles that introduce partitional view in SQL Server. It look meet my requirement. I understand that SQL view doesn't support parameter however my report allow user to specify range of date. If my SQL is like :

myView: SELECT * FROM tableA UNION SELECT * FROM tableB

Ad-Hoc SQL : SELECT * FROM myview WHERE mydate BETWEEN @P1 AND @P2

Would this bring any performance improvement compare with store all record in one table and query it?

Thank you.