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”
0 comments:
Post a Comment