What to use ISNULL() or COALESCE()

Introduction

One of my Blog post is very much appritiated by my reader is different between ISNULL() and COALESCE (). But in early time I post my blogs at http://sqlservernet.blogspot.in/2012/03/what-to-use-isnull-or-coalesce.html

So I decide to move this post in my web that all my reader can enjoy it.

Let's start

The NULL value always makes some problem to database developer. To handle the null value properly the SQL server provides two functions:

1.    ISNULL()

2.    COALESCE ()

Both functions act like same.

Before finding the difference between two functions we must understand, what is the functionality of both functions to take care of null values.

Suppose we have a Table names TBL-A

Roll	StudentName	StudentClass
1	JOYDEEP	1
2	TUHIN	1
3	NULL	1

To handle the NULL value with ISNULL()

SELECT ISNULL(StudentName,'NOT FOUND') StudentName

FROM   Table1-A

It returns "NOT FOUND" in studentname columns for roll = 3

The syntax of the COALESCE is mentioned bellow

COALESCE ( expression  [ ,...n ] )

Unlike ISNULL, COALESCE takes multiple columns as expression. To understand it properly I give an example. Please study the example well and you will understand the COALESCE

CREATE TABLE dbo.wages

(

    emp_id             tinyint   identity,

    hourly_wage   decimal   NULL,

    salary               decimal   NULL,

    commission     decimal   NULL,

    num_sales       tinyint      NULL

);

GO

INSERT dbo.wages

    (hourly_wage, salary, commission, num_sales)

VALUES

    (10.00, NULL, NULL, NULL),

    (20.00, NULL, NULL, NULL),

    (30.00, NULL, NULL, NULL),

    (40.00, NULL, NULL, NULL),

    (NULL, 10000.00, NULL, NULL),

    (NULL, 20000.00, NULL, NULL),

    (NULL, 30000.00, NULL, NULL),

    (NULL, 40000.00, NULL, NULL),

    (NULL, NULL, 15000, 3),

    (NULL, NULL, 25000, 2),

    (NULL, NULL, 20000, 6),

    (NULL, NULL, 14000, 4);

GO

SELECT 

CAST(COALESCE(hourly_wage * 40 * 52,  salary,

                                commission * num_sales) AS money) AS 'Total Salary'

FROM dbo.wages

ORDER BY 'Total Salary';

The Output is mentioned bellow

 Total Salary

10000.00

20000.00

20800.00

30000.00

40000.00

41600.00

45000.00

50000.00

56000.00

62400.00

83200.00

120000.00

Now what are the differences between them?

1.    COALESCE is ANSI-92 compliant and ISNULL is not. So if u migrate the SQL statement in any          other RDBMS the COALESCE is helpful.

2.    COALESCE accepts greater than two expressions, whereas ISNULL accepts only two. In order          to compare three expressions with ISNULL, you would have to nest expressions:

       SELECT ISNULL(ISNULL(Col1, Col2), Col3)

3.    In this SQL the ISNULL can provide some Error

       DECLARE @Field1 char(3),

               @Field2 char(50)

      SET @Field2 = 'Some Long String'

      SELECT ISNULL(@Field1, @Field2)

      --Returns 'Som'

      SELECT COALESCE(@Field1, @Field2)

      --Returns 'Some Long String'

 4.    Performance wise ISNULL and COALESCE are equal. It is found that the ISNULL is  

        slightly better performer then COALESCE.

In my advice use COALESCES() rather than ISNULL().

Hope you like this.

Posted by: MR. JOYDEEP DAS

NULL effects with IN and NOT IN Clause

Introduction

Treatment of NULL values is an important factor when we are writing query. Sometimes it provide vigorous output if we don't treat NULL. Here in this article I am going to explain a particular scenario with NULL effects.

Scenario

-- Query-1

SELECT 'OK' AS RESULT WHERE 10 IN(1, 2, 4, 10,NULL);

-- Query-2

SELECT 'OK' AS RESULT WHERE 10 NOT IN(1, 2, 4, NULL);

Consider the Query-1 WHERE conditions. It provides the proper output. But in Query-2 not provide any output.

Reason

If we have a closer look, the Query-1 is actually.

 SELECT 'OK' AS RESULT WHERE 10 = 1

                            OR 10 = 2

                            OR 10 = 4

                            OR 10 = 10

                            OR 10 = NULL;

In WHERE condition we find OR operators, so it works fine for 10 = 10.

But for Query-2

SELECT 'OK' AS RESULT WHERE 10 <> 1

                            AND 10 <> 2

                            AND 10 <> 4

                            AND 10 <> NULL; 

Here we find the AND operator and 10 is comparing with NULL. Here NULL is UNKNOWN.

ANSI_NULLS ON|OFF

When this is on, any query that compares a value with a null returns a 0. When off, any query that compares a value with a null returns a null value.

What MSDN says

"When SET ANSI_NULLS is ON, a SELECT statement that uses WHERE column_name = NULL returns zero rows even if there are null values in column_name. A SELECT statement that uses WHERE column_name <> NULL returns zero rows even if there are nonnull values in column_name.

When SET ANSI_NULLS is OFF, the Equals (=) and Not Equal To (<>) comparison operators do not follow the ISO standard. A SELECT statement that uses WHERE column_name =NULL returns the rows that have null values in column_name. A SELECT statement that uses WHERE column_name <> NULL returns the rows that have nonnull values in the column. Also, a SELECT statement that uses WHERE column_name <> XYZ_value returns all rows that are not XYZ_value and that are not NULL."

By default the ANSI_NULLS is ON. Now we make it OFF and see the result set of Query-1 and Query-2.

SET ANSI_NULLS OFF

GO

-- Query-1

SELECT 'OK' AS RESULT WHERE 10 IN(1, 2, 4, 10,NULL);

GO

-- Query-2

SELECT 'OK' AS RESULT WHERE 10 NOT IN(1, 2, 4, NULL);

GO

SET ANSI_NULLS ON

GO

Here in this case both the query is returning the same output.

Finding NULL Values

To find NULL values we can use

SELECT * FROM table_name WHERE columns_name IS NULL;

But if ANSI_NULLS is OFF we can write this also

SET ANSI_NULLS OFF                           

SELECT * FROM table_name WHERE columns_name = NULL;

Or by treating NULL values we can write this

SELECT * FROM table_name

WHERE ISNULL(columns_name, '') = '';

Hope you like it.

Posted by: MR. JOYDEEP DAS

Sparse Columns

Introductions

In this article I am trying to discuss about a very interesting feture of MS SQL Server and it is called the Sparse Columns. This feature is started from MS SQL Server 2008 and onwards. Hope it will be interesting and informative.

Point in Focus

What is Sparse columns

Understand the Sparse columns with Example

Which columns not support it

Which datatypes not support it.

Where it is Best use

Others restrictions

What is Sparse columns

Sparse columns are nothing but a ordinary columns which is designed for optimized the NULL values. The benefits of the Sparse columns is they reduce space required for NULL values.

Another benefits is its reduce the costs of increased processing overhead to retrive the non-null values.

Let see the MSDN defination related to Sparse columns.

“Sparse columns are ordinary columns that have an optimized storage for null values. Sparse columns reduce the space requirements for null values at the cost of more overhead to retrieve nonnull values. Consider using sparse columns when the space saved is at least 20 percent to 40 percent”

http://msdn.microsoft.com/en-us/library/cc280604.aspx

In MS SQL Server 2008 the maximum columns supported by a table objects is 1024. But the Sparse columns does not comes under this limit. The maximum Sparse coum supported by a table is 100,000. So a table objects may conatins 1024 regular columns + 100,000 Sparse columns.

Understand the Sparse columns with Example

In this example I am using a system stored procedure sp_spaceused. Before staring the example a samll note related to sp_spaceused from MSDN is mentioned bellow. For more details, please follow the MSDN link.

“Displays the number of rows, disk space reserved, and disk space used by a table, indexed view, or Service Broker queue in the current database, or displays the disk space reserved and used by the whole database.”

http://msdn.microsoft.com/en-us/library/ms188776.aspx

Step-1 [ Creating the Base Table ]

-- Without SPARSE Columns

CREATE TABLE ExmapleTable1

      (EMPID   INT IDENTITY(1,1),

       FNAME   VARCHAR(50),

       LNAME   VARCHAR(50));

GO

-- With SPARSE

CREATE TABLE ExmapleTable2

      (EMPID   INT IDENTITY(1,1),

       FNAME   VARCHAR(50) SPARSE,

       LNAME   VARCHAR(50) SPARSE);

Step-2 [ Now Insert Some Records ]

DECLARE @idx INT = 0;

WHILE @idx < 50000

      BEGIN

         INSERT INTO ExmapleTable1 VALUES (NULL, NULL);

         INSERT INTO ExmapleTable2 VALUES (NULL, NULL);

         SET @idx+=1;

      END

Step-3 [ Now compre both ]

sp_spaceused 'ExmapleTable1'

GO

sp_spaceused 'ExmapleTable2'

Name	Rows	Reserved	Data	index_size	Unused
ExmapleTable1	50000	2824 KB	2808 KB	8 KB	8 KB
Name	Rows	Reserved	Data	index_size	Unused
ExmapleTable2	50000	1416 KB	1408 KB	8 KB	8 KB

                                                           

Which columns not support it

Some of the columns mentioed bellow is not supported by Sparse

Computed Columns / RowGuid / Filestream / Identity / XML

Which datatypes not support it

The following datatype not supported Sparse

Geography / Geometry / Image / Ntext / Text / Timestamp / Userdefine data type

Where it is Best use

The Sparse columns is best used in Fintered Index. Where data are filtered in the row.

Others restrictions

We can not bound rueles with sparse columns and it can not have any defualt value.

It can not be a part of Clustered or a unique Primary key indexes. It can not be used as a partition key of a clustered index or heap.

Sparse columns can not be used with userdefine table type (Table variable and Table valued parameters)

Hope you like it.

Posted By: MR. JOYDEEP DAS