coalesce function in sql

Introduction

When we have multi-value attribute with single or more null values in a Table, the Coalesce() function is very useful.

Using the Code

If you consider the below facts placed in a employee table with Id, Name, ph_no, Alt_no, Office no.

id	Name	Ph_ no	Alt_ no	Office no
101	Albert	999999	456453	321333
102	khan	null	null	123455
103	victor	112121	null	null
104	lovely	null	null	1897321

The above Employee table may have single value or three values. If it has single value, then it fills null values with remaining attributes.

When we retrieve the number from employee table, that number Should Not be Null value. To get not nullvalue from employee table, we use Coalesce() function. It returns the first encountered Not Null Value from employee table.

Hide   Copy Code

select id , name ,coalesce(Ph_no,Alt_no,Office_no) as contact number from employee 

It returns:

id	Name	Contactnumber
101	Albert	999999
102	khan	123455
103	victor	112121
104	lovely	1897321

Migrations in sql server

Difference between Sql server 2000 and 2005

Difference between Sql server 2005 and 2008

Difference between Sql server 2008 and 2008r2

Difference between Sql server 2008 and 2012

What is Functions in sql server

Function is a database object in Sql Server. Basically it is a set of sql statements that accepts only input parameters, perform actions and return the result. Function can return only single value or a table. We can’t use function to Insert, Update, Delete records in the database table(s). For more about stored procedure and function refer the articleDifference between Stored Procedure and Function

Types of Function

1. System Defined Function

   These functions are defined by Sql Server for different purpose. We have two types of system defined function in Sql Server

   1. Scalar Function

      Scalar functions operates on a single value and returns a single value. Below is the list of some useful Sql Server Scalar functions.
      System Scalar Function

      Scalar Function

      Description

      abs(-10.67)

      This returns absolute number of the given number means 10.67.

      rand(10)

      This will generate random number of 10 characters.

      round(17.56719,3)

      This will round off the given number to 3 places of decimal means 17.567

      upper('dotnet')

      This will returns upper case of given string means 'DOTNET'

      lower('DOTNET')

      This will returns lower case of given string means 'dotnet'

      ltrim(' dotnet')

      This will remove the spaces from left hand side of 'dotnet' string.

      convert(int, 15.56)

      This will convert the given float value to integer means 15.

   2. Aggregate Function

      Aggregate functions operates on a collection of values and returns a single value. Below is the list of some useful Sql Server Aggregate functions.
      System Aggregate Function

      Aggregate Function

      Description

      max()

      This returns maximum value from a collection of values.

      min()

      This returns minimum value from a collection of values.

      avg()

      This returns average of all values in a collection.

      count()

      This returns no of counts from a collection of values.

2. User Defined Function

   These functions are created by user in system database or in user defined database. We three types of user defined functions.

   1. Scalar Function

      User defined scalar function also returns single value as a result of actions perform by function. We return any datatype value from function.

      
       --Create a table 
      CREATE TABLE Employee
      (
       EmpID int PRIMARY KEY,
       FirstName varchar(50) NULL,
       LastName varchar(50) NULL,
       Salary int NULL,
       Address varchar(100) NULL,
      )
      --Insert Data
      Insert into Employee(EmpID,FirstName,LastName,Salary,Address) Values(1,'Mohan','Chauahn',22000,'Delhi');
      Insert into Employee(EmpID,FirstName,LastName,Salary,Address) Values(2,'Asif','Khan',15000,'Delhi');
      Insert into Employee(EmpID,FirstName,LastName,Salary,Address) Values(3,'Bhuvnesh','Shakya',19000,'Noida');
      Insert into Employee(EmpID,FirstName,LastName,Salary,Address) Values(4,'Deepak','Kumar',19000,'Noida');
      --See created table
      Select * from Employee 
      
      

      

      
       --Create function to get emp full name 
      Create function fnGetEmpFullName
      (
       @FirstName varchar(50),
       @LastName varchar(50)
      )
      returns varchar(101)
      As
      Begin return (Select @FirstName + ' '+ @LastName);
      end 
      
      

      

      
       --Calling the above created function
      Select dbo.fnGetEmpFullName(FirstName,LastName) as Name, Salary from Employee 
      
      

      

   2. Inline Table-Valued Function

      User defined inline table-valued function returns a table variable as a result of actions perform by function. The value of table variable should be derived from a single SELECT statement.

      
       --Create function to get employees
      Create function fnGetEmployee()
      returns Table
      As
       return (Select * from Employee) 
      
      

      

      
       --Now call the above created function
      Select * from fnGetEmployee() 
      
      

      

   3. Multi-Statement Table-Valued Function

      User defined multi-statement table-valued function returns a table variable as a result of actions perform by function. In this a table variable must be explicitly declared and defined whose value can be derived from a multiple sql statements.

      
       --Create function for EmpID,FirstName and Salary of Employee
      Create function fnGetMulEmployee()
      returns @Emp Table
      (
      EmpID int, 
      FirstName varchar(50),
      Salary int
      )
      As
      begin
       Insert into @Emp Select e.EmpID,e.FirstName,e.Salary from Employee e;
      --Now update salary of first employee
       update @Emp set Salary=25000 where EmpID=1;
      --It will update only in @Emp table not in Original Employee table
      return
      end 
      
      

      

      
       --Now call the above created function
      Select * from fnGetMulEmployee() 
      
      

      

      
       --Now see the original table. This is not affected by above function update command
      Select * from Employee 
      
      

      

Note

1. Unlike Stored Procedure, Function returns only single value.
2. Unlike Stored Procedure, Function accepts only input parameters.
3. Unlike Stored Procedure, Function is not used to Insert, Update, Delete data in database table(s).
4. Like Stored Procedure, Function can be nested up to 32 level.
5. User Defined Function can have upto 1023 input parameters while a Stored Procedure can have upto 2100 input parameters.
6. User Defined Function can't returns XML Data Type.
7. User Defined Function doesn't support Exception handling.
8. User Defined Function can call only Extended Stored Procedure.
9. User Defined Function doesn't support set options like set ROWCOUNT etc.

What is the Difference between ROW_NUMBER(), RANK() and DENSE_RANK()?

Introduction

All of these three functions are used to calculate RowID for the result set returned from a query but in a slightly different way.

Row_Number()

This function will assign a unique id to each row returned from the query.

Consider the following query:

Hide   Copy Code

DECLARE @Table TABLE (
      Col_Value varchar(2)
)

INSERT INTO @Table (Col_Value)
      VALUES ('A'),('A'),('A'),('B'),('B'),('C'),('C');

SELECT
      Col_Value,
      ROW_NUMBER() OVER (ORDER BY Col_Value) AS 'RowID'
FROM
      @Table;    

After executing it, we will get:

Col_Value	RowID
A	1
A	2
A	3
B	4
B	5
C	6
C	7

As we notice, each and every row has a unique ID.

Rank()

This function will assign a unique number to each distinct row, but it leaves a gap between the groups. Let me explain with a query, we will use the same query we used above with Rank().

Hide   Copy Code

SELECT
      Col_Value,
      Rank() OVER (ORDER BY Col_Value) AS 'RowID'
FROM
      @Table;

This query will return:

Col_Value	RowID
A	1
A	1
A	1
B	4
B	4
C	6
C	6

As we can see, rowid is unique for each distinct value, but with a gap. What is this gap?

This gap represents number of occurrence. For example: value ‘a’ is repeated thrice and has rank ‘1’, the next rank will be 1+3=4. Same with the next value 4+2=6 and so on.

Dense_Rank()

This function is similar to Rank with only difference, this will not leave gaps between groups.

So if we use the same query used for Rank, then:

Hide   Copy Code

SELECT
      Col_Value,
      DENSE_RANK() OVER (ORDER BY Col_Value) AS 'RowID'
FROM
      @Table;

We will get:

Col_Value	RowID
A	1
A	1
A	1
B	2
B	2
C	3
C	3

So it is clear that it generates a unique id for each group and without repetition.

As we are clear now what these functions do, we can use them in different scenarios based on our need.

For example: Row_Number() can be used if we want to skip and fetch some records. Take a look at this query:

Hide   Copy Code

WITH AllRecords
AS (
      SELECT
            Col_Value,
            ROW_NUMBER() OVER (ORDER BY Col_Value) AS 'RowID'
      FROM
            @Table)

SELECT
      Col_Value,
      RowID
FROM
      AllRecords
WHERE
      RowID >= 4
      AND RowID <= 5;

This will return only those rows with RowID 4 and 5. This function is very useful in paging data in SQL instead of in code till SQL SERVER 2012. In SQL Server 2012, Microsoft introduces a new feature Offset Fetch similar to what we did in the above query.

In SQL Server 2012, the above query will be written as:

Hide   Copy Code

SELECT
      Col_Value
FROM
      @Table
ORDER BY
      Col_Value OFFSET 3 ROWS FETCH NEXT 2 ROWS ONLY;

This will skip first 3 rows and will fetch next 2.

Similarly, we can use Dense_Rank() when we need to calculate rowid with Select Distinct.

Hide   Copy Code

SELECT
DISTINCT
      Col_Value,
      DENSE_RANK() OVER (ORDER BY Col_Value) AS 'RowID'
FROM
      @Table;

Will return:-

Col_Value	RowID
A	1
B	2
C	3

Or when we need a report where ranks clashes. For example: Two students scored same marks and share same division.

We can use Rank() if we don’t want consecutive numbers.