Before we jump into the core logic, let me explain you what are 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 from EMPTEST

ISNULL: This function replaces the NULL value with specified value given in function. Suppose we have probability to have Middle Name NULL in Employee table, we could 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 in result set.

COALESCE: Basically COALESCE function will return first not null value from the given list, so we can 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 even represent a blank or space or zero. When you try to concatenate two or more than two strings and any of the string is NULL, it will return the NULL only. See following example:

 

Select FirstName + ' ' + MiddleName + ' ' + LastName as FullName from EMPTEST

You will get first record “Ritesh A Shah” as full name and remaining two records as NULL as those two records are having NULL value in its MiddleName field so concatenate results will be NULL only.

You can have solution for that with ISNULL & COALESCE.

Select FirstName + ' ' + ISNULL(MiddleName,'') + ' ' + LastName as FullName from EMPTEST

Select FirstName + ' ' + COALESCE(MiddleName,'') + ' ' + LastName as FullName from EMPTEST

In this solution, you have to wrap up all the fields all the fields with either ISNULL or with COALESCE if it has probability of containing NULL value. If you have many fields in one query which needs this wrapping, it is bit tedious for you as a developer. At this time, CONCAT_NULL_YIELDS_NULL property comes as a rescue.

CONCAT_NULL_YIELDS_NULL: this property controls concatenation of the string, like what should do if any of the string is NULL and it is being used in concatenation.

You can set this property at database level by ALTER DATABASE command or you can use this for your current session/connection.

Select FirstName + ' ' + MiddleName + ' ' + LastName as FullName from EMPTEST

As I have already told you that above query returns two record with NULL value as FullName, you can do something like below TSQL to set CONCAT_NULL_YIELDS_NULL  for your batch.

SET CONCAT_NULL_YIELDS_NULL OFF;

Select FirstName + ' ' + MiddleName + ' ' + LastName as FullName from EMPTEST

SET CONCAT_NULL_YIELDS_NULL ON;

By default CONCAT_NULL_YIELDS_NULL  set with TRUE (ON) for every database but you can turn it OFF for your batch or for your database (by ALTER DATABASE command). It will save you from writing ISNULL and COALESCE command for many times in your query.

If you want to check whether your database is set to TRUE or FALSE for this property or your SESSION has this property TRUE or FALSE, you can use following TSQL.

SELECT DATABASEPROPERTYEX('YourDatabaseName', 'IsNullConcat')

SELECT SESSIONPROPERTY('CONCAT_NULL_YIELDS_NULL')

Personally I prefer to go for either ISNULL or COALESCE rather than CONCAT_NULL_YIELDS_NULL . There are few reasons for that.

--> CONCAT_NULL_YIELDS_NULL  is supported in even SQL Server 2008 R2 along with previous versions of SQL Server but as per Microsoft, They will remove this feature in new releases, it will be by default ON in new versions and you will not be able to set it OFF

 --> If you have this 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 settings of 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 but examples and explanations prepared by Ritesh Shah, founder of http://www.SQLHub.com

Ask me any SQL Server related question at my “ASK Profile”

Sometime we may need to see some data of one column as a column header and aggregated results in those columns as a data. There are many programmatic ways to cater this need. Herewith, I am going to explained Fixed Column Cross Tab results with PIVOT. I will write few more articles for other ways of doing the same task.

Here is the table, I will be using for demo.

Create Table SalesSummaryOfRegions

(

Item VARCHAR(10) NOT NULL,

State VARCHAR(10) NOT NULL,

TotalSales INT NOT NULL

)

INSERT INTO SalesSummaryOfRegions

SELECT 'LAPTOP','CA',100 UNION ALL

SELECT 'LAPTOP','NJ',1200 UNION ALL

SELECT 'ADAPTER','CA',910 UNION ALL

SELECT 'MOUSE', 'NY',1100 UNION ALL

SELECT 'MOUSE','NY',2000

Now, I want to get list of all the Items of NJ and CA with its total and average sales. It seems quiet easy as we can use simple GROUP BY clause and SUM aggregate function. But what if I want to see four column named Item, CA, NJ, Average.

Item column should contain all the Items we used to sell. CA column contain total of each item we sold in CA state in respective item’s row and same with NJ. Average column should contain average of particular item sold for both the state. Not you can say, its bit challenging. Yes, it is, if you are going to do it with sub query logic or cursor. But with the help of PIVOT in Microsoft SQL Server 2005, it is very simple. Have a look at following query.

SELECT Item,CA,NJ,(isnull(CA,0)+isnull(NJ,0))/2 as 'Average' FROM SalesSummaryOfRegions

PIVOT

(

sum(TotalSales) FOR state in (CA,NJ)

)AS pivo

In above query, we gave Item from SalesSummaryOfRegions table in select list but we don’t have CA and NJ column in our table. Rest assure, it won’t show you error as it was not written by mistake, it’s a use of PIVOT. You can see I use one aggregate function after PIVOT words in above query and it was used for state column. As will be showing average also, so there is one possibility of NULL value as respective value so I want to make sure that NULL value will be treated as 0 that is why I have used isnull() function.

Reference: Ritesh Shah