SQL Server ColumnStore – Building Index Hogs System Resources

I would not make modifications like adding primary/unique or foreign key constraints to a legacy database that you haven't built yourself. Chances are that the original developer, given the problems and design issues you mention, may have built logic that breaks with such constraints.

For instance, if the app uses a 0 in a key column instead of NULL to represent "nothing", a foreign key constraint would fail that UPDATE/INSERT, even if the value is just used temporarily in a procedure.

The best place to start is probably to identify specific queries that show performance issues or cause locking, and deal with those. Just adding proper indexes on strategic tables may prove a huge improvement for you.

As for cursors.. some devs just don't know better. :)

The error message is there for a reason. Using UNION on thousands of tables in a single query is performance suicide. Yes, I understand that your reporting application is already in production, but I would still strongly recommend that you to go back to your development team and update the application or, even better, rebuild its logic in a stored procedure instead.

As you suggest in your question, create a temp table, run each of those queries sequentially and dump the results into the temp table. Then, compile the report from the temp table. All of this can be done in a stored procedure, which will also cover other important aspects like security and performance.

This carries a range of advantages:

• Temp table can be indexed for performance
• You won't bloat your server's query plan cache (and in the process evict pretty much any other query plan that has ever been cached)
• The query won't take forever to "compile" (i.e. turn into an execution plan)

I'm not trying to mess with you. It's my firm conviction that if you don't rebuild the report now, you will most likely have to some day, going forward.