We have a number of servers which often run into tight-memory situations, we've tried the above approach quite exhaustively. The frequent creation and destruction of temporary tables can make a subsecond SP end up taking over 30 seconds to wait for object memory to become available. If this query was frequently run on a busy server, I'd expect to start seeing vast slow-downs. If the server's idling, then we found this approach can help in certain situations. However, a good index is as fast.

If your app is running very slowly because of date comparison, a good solution is to add another int column, index it, and copy over the dates into it. Then cast any querrent date to int and use the new column(s) instead. Everything runs super-fast, especially if the table is transactional, so the index fill-factor can be set to 100% to make it compact and take fewer IOs. The increase in table size is a good trade-off of hard-disk usage versus the memory usage of table variables.

🙂