You are going to see a performance drop, because you are going to have to union the two tables together and then query on those just to be able to accomplish an artificial partition of the data.
SQL Server is NOT supposed to give up memory after each query. Memory allocation is an expensive operation. A SQL Server will give up memory if something else on the machine needs it. Since nothing else needs it, SQL Server is going to keep it, because it will probably need the memory again at a later time. If the SQL Server gave up memory after every query, you would quickly have a system that would spend more than half of the time doing nothing but allocating and deallocating memory at which point there would be very little purpose to even trying to run something on it.
For More Information
- Dozens more answers to tough SQL Server questions from Michael Hotek are available here.
- The Best Microsoft SQL Server Web Links: tips, tutorials, scripts, and more.
- The Best SQL Web Links
- Have a SQL Server tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize. Submit your tip today!
- Ask your technical SQL Server questions -- or help out your peers by answering them -- in our live discussion forums.
- Ask the Experts yourself: Our SQL, database design, Oracle, SQL Server, DB2, metadata, object-oriented and data warehousing gurus are waiting to answer your toughest questions.