ISNULL, COALESCE or CONCAT_NULL_YIELDS_NULL in SQL Server

Before we jump into the core logic, let me explain you whatare the use of this functions / property.
BTW, this is the table to be used in example:
CREATE TABLE EMPTEST
(
FirstName varchar(10)
,MiddleName varchar(10)
,LastName varchar(10)
)
INSERT INTO EMPTEST
select ‘Ritesh’,‘A’,‘Shah’ UNION ALL
select ‘Roger’,NULL,‘Federer’ UNION ALL
select ‘Steffi’,NULL,‘Graf’
GO
Select FirstName,LastName fromEMPTEST
ISNULL: Thisfunction replaces the NULL value with specified value given in function.Suppose we have probability to have Middle Name NULL in Employee table, wecould do something like below given TSQL statement.
Select FirstName, ISNULL(Lastname,) as LastName From EMPTEST
If Lastname will be NULL, it will be replaced with blank inresult set.
COALESCE: BasicallyCOALESCE function will return first not null value from the given list, so wecan use this function in place of ISNULL too, like this:
Select FirstName, COALESCE(Lastname,) as LastName From EMPTEST
NULL is nothing but the absent of value, it doesn’t evenrepresent a blank or space or zero. When you try to concatenate two or morethan two strings and any of the string is NULL, it will return the NULL only. Seefollowing example:
 
Select FirstName + ‘ ‘ + MiddleName + ‘ ‘ + LastName as FullName fromEMPTEST
You will get first record “Ritesh A Shah” as full name and remainingtwo records as NULL as those two records are having NULL value in itsMiddleName field so concatenate results will be NULL only.
You can have solution for that with ISNULL & COALESCE.
Select FirstName + ‘ ‘ + ISNULL(MiddleName,) + ‘ ‘ + LastName asFullName from EMPTEST
Select FirstName + ‘ ‘ + COALESCE(MiddleName,) + ‘ ‘ + LastName asFullName from EMPTEST
In this solution, you have to wrap up all the fields all thefields with either ISNULL or with COALESCE if it has probability of containingNULL value. If you have many fields in one query which needs this wrapping, itis bit tedious for you as a developer. At this time, CONCAT_NULL_YIELDS_NULLproperty comes as a rescue.
CONCAT_NULL_YIELDS_NULL:this property controls concatenation of the string, like what should do if anyof the string is NULL and it is being used in concatenation.
You can set this property at database level by ALTERDATABASE command or you can use this for your current session/connection.
Select FirstName + ‘ ‘ + MiddleName + ‘ ‘ + LastName as FullName fromEMPTEST
As I have already told you that above query returns tworecord with NULL value as FullName, you can do something like below TSQL to setCONCAT_NULL_YIELDS_NULL  for your batch.
SET CONCAT_NULL_YIELDS_NULL OFF;
Select FirstName + ‘ ‘ + MiddleName + ‘ ‘ + LastName as FullName fromEMPTEST
SET CONCAT_NULL_YIELDS_NULL ON;
By default CONCAT_NULL_YIELDS_NULL  set with TRUE (ON) for every database but youcan turn it OFF for your batch or for your database (by ALTER DATABASEcommand). It will save you from writing ISNULL and COALESCE command for manytimes in your query.
If you want to check whether your database is set to TRUE orFALSE for this property or your SESSION has this property TRUE or FALSE, youcan use following TSQL.
SELECT DATABASEPROPERTYEX(‘YourDatabaseName’, ‘IsNullConcat’)
SELECT SESSIONPROPERTY(‘CONCAT_NULL_YIELDS_NULL’)
Personally I prefer to go for either ISNULL or COALESCErather than CONCAT_NULL_YIELDS_NULL . There are few reasons for that.
–> CONCAT_NULL_YIELDS_NULL  is supported in even SQL Server 2008 R2 alongwith previous versions of SQL Server but as per Microsoft, They will remove this feature in new releases, it will be by default ONin new versions and you will not be able to set it OFF
 –> If you havethis option in Stored procedure, it will force your Stored Procedure to recompile every time you execute your SP.
Apart from these, as long as possible, we should avoid tempering with default settingsof SQL Server unless you know what exactly your doing.
 Reference: Ritesh Shah

http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles butexamples and explanations prepared by Ritesh Shah, founder of
http://www.SQLHub.com
Ask me any SQL Server related question at my “ASK Profile
Advertisements

ISNULL, COALESCE or CONCAT_NULL_YIELDS_NULL in SQL Server

Before we jump into the core logic, let me explain you whatare the use of this functions / property.
BTW, this is the table to be used in example:
CREATE TABLE EMPTEST
(
FirstName varchar(10)
,MiddleName varchar(10)
,LastName varchar(10)
)
INSERT INTO EMPTEST
select ‘Ritesh’,‘A’,‘Shah’ UNION ALL
select ‘Roger’,NULL,‘Federer’ UNION ALL
select ‘Steffi’,NULL,‘Graf’
GO
Select FirstName,LastName fromEMPTEST
ISNULL: Thisfunction replaces the NULL value with specified value given in function.Suppose we have probability to have Middle Name NULL in Employee table, wecould do something like below given TSQL statement.
Select FirstName, ISNULL(Lastname,) as LastName From EMPTEST
If Lastname will be NULL, it will be replaced with blank inresult set.
COALESCE: BasicallyCOALESCE function will return first not null value from the given list, so wecan use this function in place of ISNULL too, like this:
Select FirstName, COALESCE(Lastname,) as LastName From EMPTEST
NULL is nothing but the absent of value, it doesn’t evenrepresent a blank or space or zero. When you try to concatenate two or morethan two strings and any of the string is NULL, it will return the NULL only. Seefollowing example:
 
Select FirstName + ‘ ‘ + MiddleName + ‘ ‘ + LastName as FullName fromEMPTEST
You will get first record “Ritesh A Shah” as full name and remainingtwo records as NULL as those two records are having NULL value in itsMiddleName field so concatenate results will be NULL only.
You can have solution for that with ISNULL & COALESCE.
Select FirstName + ‘ ‘ + ISNULL(MiddleName,) + ‘ ‘ + LastName asFullName from EMPTEST
Select FirstName + ‘ ‘ + COALESCE(MiddleName,) + ‘ ‘ + LastName asFullName from EMPTEST
In this solution, you have to wrap up all the fields all thefields with either ISNULL or with COALESCE if it has probability of containingNULL value. If you have many fields in one query which needs this wrapping, itis bit tedious for you as a developer. At this time, CONCAT_NULL_YIELDS_NULLproperty comes as a rescue.
CONCAT_NULL_YIELDS_NULL:this property controls concatenation of the string, like what should do if anyof the string is NULL and it is being used in concatenation.
You can set this property at database level by ALTERDATABASE command or you can use this for your current session/connection.
Select FirstName + ‘ ‘ + MiddleName + ‘ ‘ + LastName as FullName fromEMPTEST
As I have already told you that above query returns tworecord with NULL value as FullName, you can do something like below TSQL to setCONCAT_NULL_YIELDS_NULL  for your batch.
SET CONCAT_NULL_YIELDS_NULL OFF;
Select FirstName + ‘ ‘ + MiddleName + ‘ ‘ + LastName as FullName fromEMPTEST
SET CONCAT_NULL_YIELDS_NULL ON;
By default CONCAT_NULL_YIELDS_NULL  set with TRUE (ON) for every database but youcan turn it OFF for your batch or for your database (by ALTER DATABASEcommand). It will save you from writing ISNULL and COALESCE command for manytimes in your query.
If you want to check whether your database is set to TRUE orFALSE for this property or your SESSION has this property TRUE or FALSE, youcan use following TSQL.
SELECT DATABASEPROPERTYEX(‘YourDatabaseName’, ‘IsNullConcat’)
SELECT SESSIONPROPERTY(‘CONCAT_NULL_YIELDS_NULL’)
Personally I prefer to go for either ISNULL or COALESCErather than CONCAT_NULL_YIELDS_NULL . There are few reasons for that.
–> CONCAT_NULL_YIELDS_NULL  is supported in even SQL Server 2008 R2 alongwith previous versions of SQL Server but as per Microsoft, They will remove this feature in new releases, it will be by default ONin new versions and you will not be able to set it OFF
 –> If you havethis option in Stored procedure, it will force your Stored Procedure to recompile every time you execute your SP.
Apart from these, as long as possible, we should avoid tempering with default settingsof SQL Server unless you know what exactly your doing.
 Reference: Ritesh Shah

http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles butexamples and explanations prepared by Ritesh Shah, founder of
http://www.SQLHub.com
Ask me any SQL Server related question at my “ASK Profile

Return comma separated value with For XML Path in SQL Server 2008/2005

We many time needs to return value of one column as a comma separated value, we can use COALESCE or ISNULL for this task but it is a lengthy process. I also have written one article for same task with COALESCE function, have a look:
Today I am going to show you one of the easy methods to do same task. Let us create one dummy table and move ahead.
use Adventureworks
CREATE TABLE [dbo].[emps](
[Name] [varchar](50),
[Dept] [varchar](10),
[Company] [varchar](15)
) ON [PRIMARY]
GO
–insert records
INSERT INTO emps
SELECT ‘RITESH’,‘MIS’,‘ECHEM’ UNION ALL
SELECT ‘Rajan’,‘MIS’,‘mar’
Now this is a time to show you how to get all names with separated by comma.
select left(t.name,len(t.name)-1) as ‘allName’ from
(
select name + ‘,’  from emps for xml path()
) as t(name)
Isn’t is easy to use method rather than ISNULL and COALESCE? Yes, it is!!!
Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of
http://www.SQLHub.com

COALESCE() function in SQL-Server for getting comma seperated value


Microsoft Definition:
This function returns first nonnull expresion among its argument.

You can use this function to get column values in one record set with separator like comma, dash etc. Before invention of this function, people used to achieve this kind of stuff by cursor which is time consuming. So, lets have a look at magical function by Microsoft.

Create following table in your database.
create table Testing( mid int ,name varchar(10) ,[18QI] varchar(10))

After creating the table, insert following records.
insert into Testing VALUES(1,’BC’,’01’)
insert into Testing VALUES(2,’BC’,’10’)
insert into Testing VALUES(3,’BC’,’02’)
insert into Testing VALUES(4,’BC’,’04’)

Now, we are all set to see the new powerful function COALESCE() in SQL-Server. Too see the magic, create following function in your database.

CREATE FUNCTION DBO.Get18QI(@ID char(100))RETURNS VARCHAR(1000)
AS
BEGIN
DECLARE @QIList varchar(1000)
SELECT @QIList = COALESCE(@QIList + ‘, ‘, ”) + convert(varchar,T.[18QI]) FROM Testing T WHERE T.name = @ID and T.[18QI] is not null
RETURN @QIList
END

after successfully creating the function, here we go to see the cool result by following query.
select DBO.Get18QI(‘BC’) as ComaValues

Hope you people will find this article useful.
Happy Working!!!!