How Union works?

Question

How Union works?

endo64

Ten Centuries

Points: 1013
More actions
August 18, 2011 at 3:58 am

#246374

Hi, I have a question about the Union operator.
Lets say I have 2 tables each have 5 million rows.
And I have a view like;
CREATE VIEW myView AS SELECT a,b FROM tableA UNION ALL SELECT a,b FROM tableB
When I select from the view filtering the rows with WHERE clause, let say, my query will return 20 rows total. 10 rows from table A and 10 rows from table B.
SELECT a,b FROM myView WHERE a = 5 AND b > 5
1) SQL Server will Union the whole tables then filter the result?
2) Or it filters the table and then union the results?
4) Does it help if the view is indexed? (which I don't want to, because the tables can be used separately and are also indexed, this will take too much space)
3) Or Should I use 2 select query filtering the results and then union them, do not use a view?
Thank you.

Viewing 7 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

colin.Leversuch-Roberts SSC Guru Points: 52643 More actions · Answer 1

an indexed view is a different question/subject area.

my best suggestion is that you examine the query plans for your various queries, this way you'll hopefully get an understanding of what happens rather than someone just telling you a solution.

[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/

Jayanth_Kurup SSC-Insane Points: 22967 More actions · Answer 2

The where clause applies to the underlying tables first the union happens only after that.

the order goes something like

Table scan or index seek with where clause

Merge the results

Perform Distinct /order as required.

Jayanth Kurup[/url]

Grant Fritchey SSC Guru Points: 399278 More actions · Answer 3

More than likely it filters the tables and then combines the results. But, you should look at the execution plan. That will tell you precisely what happens.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

endo64 Ten Centuries Points: 1013 More actions · Answer 4

Thank you for the replies. I'll prepare sample data and examine the plan. Then I can share the results here as well.

I asked the question because if I get a reply like "it combines the tables then filters" from a guru, then I would not waste the time to setup a sample db. But now I at least know that I should try and examine.

Thank you all.

Jayanth_Kurup SSC-Insane Points: 22967 More actions · Answer 5

As a rule never bank of the advice of even a GURU without first trying it out yourself. Nobody know what your trying to achieve better than yourself. The rest are only suggestions.

Jayanth Kurup[/url]

colin.Leversuch-Roberts SSC Guru Points: 52643 More actions · Answer 6

seconded - the best advice I ever had was " It just depends"

[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/