UNION and UNION ALL in sql server

Hi,
In This article we will What is UNION and UNION ALL? What is difference between both?
and details about both.

1) Union and Union All::-

The purpose of the SQL UNION
and UNION ALL commands are to combine the results of two or more queries
into a single result set consisting of all the rows belonging to all the queries
in the union.
but their is one condition a column count and datatype of all must same when you use union .
The question becomes whether or not to use the ALL syntax.

2)What is difference between Union and Union All?

The difference between Union and Union all is that 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.

A UNION statement effectively does a SELECT DISTINCT on the results set.
If you know that all the records returned are unique from your union,
use UNION ALL instead, it gives faster results.

In simple word a union is give you a distinct data from both query,and
union all is give all data with all duplicate records.in case of union all
their is no need to check duplicate records thats why a union all is faster than
union.

3)Check below query for understanding how to use UNION and UNION ALL

A) Create table and fill some data ::

 create table #emp(Emp_name varchar(100),dept varchar(50), emp_sal int)
insert into #emp(Emp_name ,dept , emp_sal)values('adi','IT',123)
insert into #emp(Emp_name ,dept , emp_sal)values('raj','IT',1231)
insert into #emp(Emp_name ,dept , emp_sal)values('nisha','WC',1232)
insert into #emp(Emp_name ,dept , emp_sal)values('shehal','WC',1233)
insert into #emp(Emp_name ,dept , emp_sal)values('kiran','aw',1234)
insert into #emp(Emp_name ,dept , emp_sal)values('shehal','WC',1233)
insert into #emp(Emp_name ,dept , emp_sal)values('kiran','aw',1234)


create table #manager(manager_name varchar(100),dept varchar(50), manager_sal int) insert into #manager(manager_name,dept ,manager_sal )values ('sandip','admin',5000)
insert into #manager(manager_name,dept ,manager_sal )values ('aditya','admin',5000)
insert into #manager(manager_name,dept ,manager_sal )values ('pranav','admin',5000)
insert into #manager(manager_name,dept ,manager_sal )values ('ajit','admin',5000)
insert into #manager(manager_name,dept ,manager_sal )values ('harshal','admin',5000)
insert into #manager(manager_name,dept ,manager_sal )values ('ajit','admin',5000)
insert into #manager(manager_name,dept ,manager_sal )values ('harshal','admin',5000)
insert into #manager(manager_name,dept ,manager_sal )values ('ajit','admin',5000)
insert into #manager(manager_name,dept ,manager_sal )values ('harshal','admin',5000)

B)UNION ::

select * from #emp union select * from #manager

C)UNION ALL ::

select * from #emp union all select * from #manager

Other Popular Posts ::

Column in comma separated strings
Pivot Unpivot data in sql server
Combine two or more column in one column in sql sever
rank fuctions in sql
Get month wise Number of working
Take Back up of table using select statement in sql server
Column data in comma seprated string
Delete duplicate records from table
Date function in sql server
Sql server::String with initial letter in uppercase.
Joins in Sql server
Dynamic Query in sql server
Basic About View
send sql table data via email using store procedure
How to use Store Procedure inside a store procedure
SET ANSI_NULLS in sql server
SET QUOTED_IDENTIFIER in sql
example of Cursor in sql server
basic of Cursor in sql.
delete duplicate records in sql server
select duplicate records in sql server
update duplicate records in sql server
comman table expression(CTE) in sql server
Split Funtions in sql server
Sql queries for sql server
Trigger on Update

Interview Question in sql server

Problem ::

Interchange 0 by 1 and 1 by 0 in given table ?

A most of time a interviewer asked this quetion,check below table.

Table Script with data ::

create table #temp (no_s int)

insert into #temp (no_s)values('0')     insert into #temp (no_s)values('1')
insert into #temp (no_s)values('0')     insert into #temp (no_s)values('1')
insert into #temp (no_s)values('0')     insert into #temp (no_s)values('1')
insert into #temp (no_s)values('0')     insert into #temp (no_s)values('1')
insert into #temp (no_s)values('0')     insert into #temp (no_s)values('1')
insert into #temp (no_s)values('0')     insert into #temp (no_s)values('1')
insert into #temp (no_s)values('0')     insert into #temp (no_s)values('1')
insert into #temp (no_s)values('0')     insert into #temp (no_s)values('1')

select * from #temp

Data ::

SCOPE_IDENTITY

SCOPE_IDENTITY (T-SQL) 

Returns the last identity value inserted into an identity column in the same scope. A scope is a module: a stored procedure, trigger, function, or batch.

• In simple words SCOPE_IDENTITY returns the last identity value generated for any table in the current session and the current scope.

For better understanding simply follow below steps and observe results

step 1 :-

create table policy_master (policy_no int identity(1,1),policy_owner varchar(40))

insert into policy_master values ('sandip')

select SCOPE_IDENTITY() C_SCOPE_IDENTITY

step 2 :-

insert into policy_master values ('sandip2')

insert into policy_master values ('sandip3')

select SCOPE_IDENTITY() C_SCOPE_IDENTITY

step 3 :-

insert into policy_master values ('sandip4')

insert into policy_master values ('sandip5')

insert into policy_master values ('sandip6')

select SCOPE_IDENTITY() C_SCOPE_IDENTITY

step 4 :-

create trigger policy_trigger on policy_master

 instead of insert as

 insert into policy_master values ('sandipg')

step 5 :-

 insert into policy_master values ('sandip7')

 select SCOPE_IDENTITY() C_SCOPE_IDENTITY

if you closely observe last step, record is inserted in table policy_master through trigger written on policy_master table itself but scope_identity() function cannot show you last identity of policy master table because insert statement in trigger is out of scope for scope_identity().

                                  

 I hope You got something useful in this article. I would like to have feedback from my blog readers.
Your valuable feedback, question, or comments about this article are always welcome.

TABS IN SSIS DESIGNER

What is SSIS Designer?
                      It is a graphical tool for creating packages. It has 4 tabs: Control Flow, Data Flow, Event Handlers and Package Explorer.

Control flow TAB:-
                     Use the Control Flow tab of SSIS Designer to build the control flow in an Integration Services package. By using control flow tab you can define your required functionality flow. Microsoft provides wide range of tools to design control flow like containers, for each loop etc.

Data flow tab:-
                       Data flow is nothing but the flow of data from the corresponding sources to the referred destinations. In this process, the data transformations make changes to the data to make it ready for the data warehouse. Three types for tools provide by Microsoft for data flow design 1) data source 2) transformations tools 3) destinations tools

Event Handlers tab:-
                                An event handler runs in response to an event raised by the package or by a task or container in the package. For example I don’t want fail my package on error or failure of one or more task in ssis package instead of that write mail to support team to look an fix issue.so for achieving this I will go to event handler tab and configure mail sending to task on failure event of those data flow or control flow task. We will discuss event handler in future post with real time example for better understanding.

Package Explorer tab:-
                           Package Explorer tab is useful for SSIS Designer to see a hierarchical view of all of the elements in a package: configurations, connections, event handlers, executable objects such as tasks and containers, log providers, precedence constraints, and variables

SQL server integration services(SSIS) introduction

    
         Microsoft SQL Server 2005 Integration Services (SSIS) is the tool set used to help you implement data integration process applications among your business application system’s files and databases. SSIS is much more than a simple extract, transform, and load (ETL) process

Basic Interactive Development Environment:-

ETL is stand for extract, transform and load:-

Transform Data: data are coming from various sources and we cannot assume that the data is structured in the same way across all the sources. We need to transform data in required format .SSIS provide large number transformation tools

 Load Data: Once the transformations are done and the data takes the form as per the requirement, we have to load the data to the destination systems.

 Load Data: Once the transformations are done and the data takes the form as per the requirement, we have to load the data to the destination systems.