I often get request to write puzzles using SQL Server. Today, I am presenting one very simple but very interesting puzzle.
What will be the output of following two SQL Scripts. First try to answer without running this two script in Query Editor.
Script 1
SELECT 1
UNION ALL
(SELECT 1
UNION
SELECT 2)
GO
Script 2
(SELECT 1
UNION ALL
SELECT 1)
UNION
SELECT 2
GOHint : This puzzle is based on my previous article SQL SERVER - Union vs. Union All - Which is better for performance?
Answer : SQL SERVER - Simple Puzzle Using Union and Union All - AnswerReference : Pinal Dave (http://www.SQLAuthority.com)
pinaldave
I forgot to mention that i got the first one right without running it… the second one is helped by Query Analyzer… can’t claim a prize then can I ? :-)
Answer is
1
1
2
Union All is faster than Union coz… by default Union apply
DISTINCT to remove duplicate record in set
And Union All show all rows including duplicates.
#1
1
1
2
#2
1
2
I like UNION ALL :)
[...] 25, 2008 by pinaldave Yesterday I posted a puzzle SQL SERVER - Simple Puzzle Using Union and Union All, today we will see the answer of this. Following image explains the answer of [...]
can u explain how it will produce the result
1
2
thanks,
ashok
^
First it will do the UNION ALL so the result set would be
1
1
Next it would do a UNION (so it would do a DISTINCT) so the final result set would be.
1
2
Hope it helps,
geoff
Script 1#
———-
1
1
2
Script 2#
————
1
2