Tuesday, November 17, 2009

Sql Server - Union and Union All

Union and Union All, both are used to select data from one or more than one tables but still they have some differences. For Example, Union is used to select distinct data from the tables but Union All allows duplicate rows to be selected from the tables.


Suppose we have a table called tbl_Manager and the structure of this table is given below:-


CREATE TABLE tbl_Manager(Managerid int identity(1,1) not null,Managername nvarchar(255),Departmentname nvarchar(255))


Now Suppose we insert the data into the table with the help of the query given below:-


Insert into tbl_Manager(Managername,Departmentname)
select 'Vivek Johari', 'Technology' union all
select 'Atul','Testing' union all
select 'Vivek Johari', 'Mobile development' union all
select 'Atul','HR' union all
select 'Virender Singh', 'Web development' union all
select 'Virender Singh', 'R&D development' union all
select 'Jagdish','Quality Assurance' union all
select 'Jagdish','Mobile development' union all
select 'Atul','Technology'




Now Suppose, we want to select the “Managername” from the table who work in the department “Technology” and “Mobile development”.


First, we try to get the result with the help of Union.In this case the query will be given below:-


select Managername from tbl_Manager where departmentname='Technology'
union
select Managername from tbl_Manager where departmentname='Mobile development'


Result :-







Now if we use the Union All instead of union to selet the managername from the table who work in the department “Technology” and “Mobile development”, we get the following result:-

select Managername from tbl_Manager where departmentname='Technology'
union all
select Managername from tbl_Manager where departmentname='Mobile development'



Result:-








By analyzing the two results, one can easily find out that the Union all allowed dublication of the data and Union do not allows the duplicate data to appear.


The second difference between Union and Union All can be given on the basic of their uses. Both Union and Union All  can be used to insert multiple rows in the table in a single query. You can see the use of the Union All in the insert statement, which we have used to insert the data into the table tbl_Manager.But in case where we required to insert multiple rows containing duplicate values , we can’t use Union for this purpose. Also the use of Union will increase the cost of execution plan for the insert query as compared to Union all..


Also since Union does not allowed the duplication of the data, therefore it has to first select the whole data and then use the Distinct function to eliminate the duplicate data.It will increase the cost of the execution plan for the query since it have to use the two functions whereas Union All allow the duplication of the data so it only needs the select statement to show the data. So it will cost little as compare to the Union.


Summary
Both Union and Union All are used to select the data from the tables but we should use Union All unless it is required to fetch only the distinct data since the use of Union will increase the cost of the execution of the query.







Thursday, November 12, 2009

IDENTITY Property in SQL Server


IDENTITY Property
Sometimes we need a column whose values can uniquely identifying the rows in the table. To achieve this purpose, this column should contains the unique values and it can’t contains the NULL or empty values. We can use this column in the “Where” clause of the Select statement to retrieve the data from the table quickly.

But as a human being we sometime forget to insert the data into this column or sometimes we insert the duplicate data into this column. So it will be very helpful if the SQL Server itself insert the value in this column whenever we try to insert any row into the table. SQL Server does this with the help of the identity column. We can set the initial value for this column and the value which the previous value is incremented to get the new value. We sometimes use this like the primary key of the table.



For example, suppose we want to create a table named student whose structure is given below




           CREATE TABLE Student (Studentid int IDENTITY (1, 1) NOT NULL,Firstname nvarchar (200) NULL,Lastname nvarchar (200),Email nvarchar (100) NULL )


Here column Studentid is defined as the identity column. In the Identity column, the value of the first argument defined the initial value for this column and the value of the second argument defined the value used to add in the last value of this column for getting the next value.


Now if we want to insert the row in the table Student, we do not need to specify the value for the column Studentid. For Example,



              insert into Student (Firstname,Lastname,Email)
              Values('Vivek', 'Johari', ‘vivekjohari@abc.com')



Here we do not specify the value for the studentid column in the Insert Statement.


Although the Identity column is very useful in maintain the data in the table, sometimes we need to set this constraint off like when we importing the data into the database.


To set the Identity column property off, the following command is used.



          Set Identity_insert Tablename Off



For Example if we want to keep the identity column (studentid) property off for the Student table, we need to use the following Sql Query:-



         set identity_insert Student off



To set the Identity column property On, the following command is used.



         Set Identity_insert Tablename On


For Example,


         set identity_insert Student on


We can also reset the values of the identity column to some different value with the help of the following command:-


          dbcc checkident (Tablename, reseed, 10)



For example, to reset the value of the studentid column of the table Student we need to use the following Sql Command

         dbcc checkident (Student, reseed, 10)




Summary:-
Identity column in the Sql server is a very useful property and it can be used to retrieve the data very quickly specially in the table where no primary key is defined.


Note:-
1) Only one Identity column is possible for a table.
2) In case of truncate command on a table the identity column value is reset to its initial seed value.