Monday, September 20, 2010

SQL Server - Deleting Duplicate Records

An easy method for deleting the duplicate records from a table is by using the Common table expressions.

Let us create a table - State with columns StateID and StateName of data types Int and Varchar(20) respectively.

Create table State (StateID Int,StateName varchar(10))
Go
Insert into State
Select 1, 'XXXX'
Union All
Select 2, 'YYYY'
Union All
Select 1, 'XXXX'
Union All
Select 3, 'ZZZZ'
Union All
Select 2, 'YYYY'
Union All
Select 1, 'XXXX'
Union All
Select 3, 'ZZZZ'
Union All
Select 1, 'XXXX'
Union All
Select 2, 'YYYY'
Union All
Select 2, 'YYYY'
Union All
Select 1, 'XXXX'
Union All
Select 3, 'ZZZZ'

Now if we check the data in the State table, it will return 12 records with duplicate values as shown below

Select * from State 

image 

Now our intention is to delete the duplicate records so that only 3 distinct records must exist.

For this we can use the Common table expression as below.

With DistinctStates(StateID,StateName,RecCount) as
(Select StateId, StateName, row_number() over(Partition by StateId,StateName Order by StateId) RecCount from State)
Delete from DistinctStates where RecCount > 1

Once we execute the above script all the duplicate Records will be deleted from the table resulting 3 distinct records

Select * from State 

image

Thursday, September 16, 2010

SQL SERVER - ISNULL() vs COALESCE()

Here is the Differences between ISNULL() and COALESCE()

ISNULL() is non-standard and provides less functionality.
COALESCE() is ANSI standard. and is more maintainable, more functional code

ISNULL() Replaces NULL with the specified replacement value
COALESCE() returns first non null value among a list of arguements

ISNULL() uses two arguements only
COALESCE() can use any number of arguements

ISNULL()  returns the same data type as of the first arguement.
COALESCE() is not guaranteed to retrun the same data type as of its first arguement.

Let us see this through an example. At first, Let us declare a variable @str of data type varchar(3). @Str will contain NULL value as we didn't assigned any value to it. Now,

ISNULL(@Str,'ABCDEFGHIJ') retruns 'ABC' , the first three characters of the replacement value as @Str is of datatype Varchar(3).

Where as COALESCE(@Str,'ABCDEFGHIJ') retruns the full string as it does not depend on the  data type of the first arguement.

Declare @str Varchar(3)
Select ISNULL(@STR,'ABCDEFGHIJ') 'ISNULL VALUE', COALESCE(@STR,'ABCDEFGHIJ')  'COALESCE VALUE'

Output :

image

Thursday, September 09, 2010

SQL SERVER – Uses of COALESCE() Function

Coalesce()  can be used to return the first Non-Null argument value among a series of values specified. If all the parameter/arguement values are null, then this  function will return the NULL value.

Examples:

Select Coalesce(NULL,NULL,34,NULL,23)  'First Non-Null Value'

Output :

image

Select Coalesce('',NULL,'','XYZ''First Non-Null Value'

Output :

image

Declare @Str varchar(10)
Select Coalesce(NULL,@Str,NULL)  'First Non-Null Value'

Output :

image

Let us consider a table which contains the marks of all the students who belongs to either Engineering or Medical streams.

If the student belongs to the Engineering stream, then the last two columns will have NULL values  where as if he belongs to Medical streams then the Mathematics column will have a null value.

Now Let us see how the query follows.

Create Table  StudentMarks(Id int identity(1,1), GroupId int , Mathematics int , Physics int ,Chemistry int , Zoology int ,Botany int )

Insert Into StudentMarks (GroupId, Mathematics, Physics, Chemistry, Zoology, Botany)
Select 1,100,96,98,NULL,NULL
Union All
Select 1,90,95,94,NULL,NULL
Union All
Select 2,NULL,94,99,89,97
Union All
Select 2,NULL,99,99,99,97

Now here is the query to calculate the total marks that each student, by using the Coalesce() 

Select ID, GroupId, Coalesce(Mathematics,0) + Coalesce(Physics,0) + Coalesce(Chemistry,0) + Coalesce(Zoology,0) + Coalesce(Botany,0) 'Total Marks'
From StudentMarks

Output :

image

Again Coalesce() can be used to Build Comma-Delimited String.

Here is an example to Build a Comma-Delimited String that contains all the columns of a table using the Coalesce() function.

Monday, September 06, 2010

SQL SERVER – CASE Expression \ Case Statement with Example

Case statement in SQL Server checks a list of conditions
and returns one of the multiple possible result values. Either it compares a single expression or Evaluates a list of Boolean expressions in order to retrun the result

Case statement can be used either in the Data Retrieval Statement-SELECT or in the Data Manipulation Statements- UPDATE and DELETE or in the SET statements.

Syntax:

CASE expression
     WHEN value_expression1 THEN result_expression1 WHEN value_expression2 THEN result_expression2
     [ ELSE else_result_expression ]
END

Or

CASE
     WHEN Boolean_expression1 THEN result_expression1
     WHEN Boolean_expression2 THEN result_expression2
     [ ELSE else_result_expression ]
END

Let us see this through a simple example. Let us first create a table called Student with columns StudentId,  Exam1, Exam2 and Exam3 

Example :

Create table Student
(StudentId int, Exam1 int,Exam2 int,Exam3 int

Insert Into Student
Select 1,89,80,90
Union All
Select 2,69,83,95
Union All
Select 3,35,70,90
Union All
Select 4,49,82,60
Union All
Select 5,99,98,95

Now,let us have a glance on the data that we have inserted

Select * From Student

image

Now Let us see the gradings of the students depending upon the following criteria :

If Total Marks >= 250 then Grade = 'A'
If Total Marks between 200 and 250 then Grade = 'B'
If Total Marks between 100 and 200 then Grade = 'C'
If Total Marks < 100 then Grade = 'E'

Now In order to see the Grades, we can use the Case statement as follows:

Select StudentId,                                                           Case WHEN (Exam1+Exam2+Exam3) > 250 THEN 'A'
       WHEN (Exam1+Exam2+Exam3) between 200 and 250 THEN 'B'
       WHEN (Exam1+Exam2+Exam3) between 100 and 200 THEN 'C' 
        Else
'D'
END 'Grade'
From Student

image

Friday, September 03, 2010

SQL SERVER – NULLIF() vs ISNULL()

NULLIF() Returns a null value if the two specified expressions are equal. If the Two expressions are not equal then it will return the first expression's value. Whether the returned value is NULL or NOT NULL, NULLIF() will return the same data type as the first expression 

Syntax  : NULLIF(expression1,expression2)

Example 1 :

Select NULLIF(100,50*2)  'NULLIF Value'

OutPut :

image

Example 2 :

Select NULLIF(2*2,2*7)  'NULLIF Value'

OutPut :

image

Example 3 :

Select NULLIF(20-2,19)  'NULLIF Value'

OutPut :

image

ISNULL() Replaces the NULL value with the specified expression value.

Syntax : ISNULL(check expression,replacement value)

Example 1 :

Select ISNULL(null,12) 'ISNULL VALUE'

OutPut :

image

Example 2:

Select ISNULL(marks,0)  'Marks'  from Student

OutPut :

image