Category Archives: UNION

T-SQL "UNION" Vs "TABLE variable"

Posted on by

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