Sunday, October 20, 2013

Which is better Union or Union All- Sql Query optimization

Let us know the answer of following queries-
Que- What is Union and Union ALL in SQL sever

In this article, I am explaining both with most of unique differences in point of SQL query optimization.

Both UNION and UNION ALL concatenate the result of two different SQLs. They differ in the way they handle duplicates.

UNION
  • UNION performs a DISTINCT on the result set, eliminating any duplicate rows.
  • The UNION command is used to select related information from two tables, much like the JOIN command. However, when using the UNION command all selected columns need to be of the same data type.
  • A UNION statement effectively does a SELECT DISTINCT on the results set. 


UNION ALL
  • UNION ALL is much faster than UNION
  • The UNION ALL command is equal to the UNION command, except that UNION ALL selects all values.
  • Union ALL will not eliminate duplicate rows, instead it just pulls all rows from all tables fitting your query specifics and combines them into a table.
  • If you know that all the records returned are unique from your union, use UNION ALL instead, because it gives faster results.

Difference between Union & Union ALL

 /* Declare First Table */
DECLARE @TblWeekDays TABLE (Days VARCHAR(10))
INSERT INTO @TblWeekDays VALUES('Sunday')
INSERT INTO @TblWeekDays VALUES('Monday')
INSERT INTO @TblWeekDays VALUES('Tuesday')
INSERT INTO @TblWeekDays VALUES('Wednesday')
INSERT INTO @TblWeekDays VALUES('Thursday')

/* Declare Second Table */
DECLARE @TblDays TABLE (Din VARCHAR(10))
INSERT INTO @TblDays VALUES('Friday')
INSERT INTO @TblDays VALUES('Saturday')
INSERT INTO @TblDays VALUES('Sunday')
INSERT INTO @TblDays VALUES('Monday')

Union example in sql server


Difference between Union and Union ALL in SQL

Difference between Union and Union ALL in SQL

Difference between Union and Union ALL in SQL



















Let me know what do you think about this article. If you have any complain or suggestion regarding improvement please feel free to write your valueble comment, I will update article accordinly

1 comments:

Anonymous said...

Thanks for another fantastic post. Where else may just anybody get that kind
of info in such an ideal approach of writing? I've a presentation subsequent week, and I'm at
the look for such info.

My websiteat Hack Into Twitter User Accounts To Get Data Back

 
Powered by Code Imagine