T-SQL "UNION" Vs "TABLE variable"

Very recently I learned this. And what a change it has made to all of my SQL based projects! I am sure people who are hardcore SQL programmers would already know this. Thought of sharing this for those who would want to know something interesting.

I had a SQL Stored Procedure that is about to retrieve records of a same table from two different databases. The usual approach was something like below:

SELECT column_name_1, column_name_2, column_name_3, …
FROM db_name_1..table_name

UNION

SELECT column_name_1, column_name_2, column_name_3, …
FROM db_name_2..table_name

This was working till these tables contained records less than 30000 rows. And it started taking significantly unacceptable time (like 10-15 seconds). I then thought of creating a temporary table and dump records from one DB then from other. There is a potential issue in that, in the form MULTI-USER environment. When two different users invoke this stored procedure at the same time, this temporary table must cater for both. I was not really sure about how I can address this.

Then came this idea of using TABLE VARIABLE. It’s like a Structure in C++. This is how I modified my stored procedure:

DECLARE @TABLE_NAME TABLE
(
Column_1 DATATYPE,
Column_2 DATATYPE,
…
)

INSERT INTO @TABLE_NAME (Column_1, Column_2, …)
SELECT column_name_1, column_name_2, …
FROM db_name_1..table_name

INSERT INTO @TABLE_NAME (Column_1, Column_2, …)
SELECT column_name_1, column_name_2, …
FROM db_name_2..table_name

SELECT Column_1, Column_2, …
FROM @TABLE_NAME

After this, the program started taking around 2-5 seconds with more than 50000 records to handle. Not to forget, this program contained several grouping and calculations.

I don’t have to worry about delay and also TEMP TABLE issues that are potentially disastrous. I am still not sure how this is handled in terms of memory allocation. What happens after this program completes it’s process, is also a factor that I am yet to figure out.

But, so far, I have found this as one of the best methods in recent times.

VAIDY

VN:F [1.9.17_1161]

VN:F [1.9.17_1161]