Difference Between COALESCE and ISNULL

ISNULL is a T-SQL function to replace NULL values as COALESCE does. At some cases these two functions get the same results but there are some differences. Below is a summary of their differences and I hope that it will help you decide which function to use when you need to determine the one with better performance or better concurrency control.

	COALESCE	ISNULL
Standard ANSI SQL?	Yes	No (Only for T-SQL)
Numbers of Parameters	Two or more parameters.	Two parameters only.
Data type determination of the resulting expression	Determined by the data type of parameters with the highest precedence	Determined by data type of the first parameter
Allow both inputs to be untyped NULLs?	No. For SELECT COALESCE(NULL, NULL); , it will return an error. You need to have at least one of the null values be a typed NULL.  SELECT COALESCE(CAST(NULL AS INT), NULL);	Yes. For SELECT ISNULL(NULL, NULL); it will return a NULL typed as an integer.
NULLability of the resulting expression	Considered to be NULL with non-null parameters.	Always considered NOT NULLable
Working with subquery as input parameters	When executing COALESCE((subquery), 1), the subquery is evaluated twice.  you can get different results depending on the isolation level of the query.	Evaluated only once.

Conversion between SSIS Integers and SQL Server Strings

When you carry out database migration, one of main headaches is the conversion of different data types between sources and destinations. If you are using SSIS, be careful about the conversion between strings and int. Sometimes SSIS will fail because the integer type defined is not big enough to hold the original data. It is also known as overflow problem. So here is a summary that indicates which kind of integer data types you should use for SSIS for strings in the source table. Hope it helps!

SSIS	SQL Server	Range	Maximum String Converted
DT_I1	tinyint	0 to 255	varchar(2)
DT_I2	smallint	-2^15 (-32,768) to 2^15-1 (32,767)	varchar(4)
DT_I4	int	-2^31 (-2,147,483,648) to 2^31-1 (2,147,483,647)	varchar(9)
DT_I8	bigint	-2^63 (-9,223,372,036,854,775,808) to 2^63-1 (9,223,372,036,854,775,807)	varchar(18)

Note that for an empty string, you can use derived column task in SSIS with expression below to convert an empty string into a NULL integer in SSIS.

LEN(strCol) == 0 ? NULL(DT_I4) : (DT_I4)(RTRIM(strCol))

You can also refer to BOL: int, bigint, smallint, and tinyint (Transact-SQL)

How to: Query BIT Data Type in SQL Server and SSIS

If you have ever met problems when trying to export BIT data type columns to files or use them in SSIS, here are the tips for you.

The bit data type is an integer data type that can take a value of 1, 0, or NULL. Thus it is often used to represent Boolean type values such as Yes/No, True/False, and On/Off. However, BIT columns might be interpreted differently by different providers. When you query BIT columns in SSMS, it displays as 1 and 0 as its value. However, when you either query BIT columns in SSIS or export them to text files, it won’t be 1 or 0 any more. Below will show you some tricks to handle BIT columns.

How SSIS handle BIT columns?

In SSIS, BIT columns are interpreted as Boolean [DT_BOOL]. How to tell this?

You need right click the OLE DB Source component and choose “Show Advanced Editor”. Then click the “Input and Output Properties” Tab. Expand “Output Columns” under OLE DB Source Output. You will notice that the BIT column is Boolean type as shown below. Here column IsSHP is the BIT data type column.

So when you need to query BIT column, you need to directly use TRUE or FALSE. Below shows that how you can query BIT columns in the conditional split task.

Note that there is no quote around true in the expression.

How to export BIT columns to files as integers in SSIS?

Solution 1: Using Derived Columns

Or if you want to store it with less storage, you can use DT_UI1 data type.

IsSHP_bit = [IsSHP] ? (DT_UI1)1 : (DT_UI1)0

Solution 2: Tweak OLE DB Source

First of all, you need to convert BIT columns in OLE DB connection manager from OLE DB Source Editor,

select convert(int, IsSHP) as IsSHP

Then change the DataType in the OLE DB Source to integer type DT_I4 as shown below.

Now you will get 1 or 0 as output for BIT columns.

That’s it and hope it will help!

Implicit Conversion From Varchar to Datetime

Sometimes a simple little thing may bring us some sights. Today I'd like to share with you something related to implicit conversion in sql server.

Problem

A colleague brought me this scenario: when she tried to update some table by joining with other tables, She got the following error:

Msg 242, Level 16, State 3, Line 1
The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.
The statement has been terminated.

So the action to work around this is to change data type of the related column from varchar(10) to datetime by just using ALTER COLUMN function. However, another error jumped out:

Msg 241, Level 16, State 1, Line 1

Conversion failed when converting date and/or time from character string.

The statement has been terminated.

What? This is kind of common practices for me as well. What did the error come from?

So I went ahead to dig into the date column that was defined as varchar(10). The moment I saw the data, I got some hint. In that column, the date was displayed as "DD-MM-YYYY". 

Solution

After changing the format from "DD-MM-YYYY" to "YYYY-MM-DD" by the following script:

UPDATE schemaName.tableName

SET datecolumn = SUBSTRING(datecolumn,7,4)+'-'+ SUBSTRING(datecolumn,4,2)+'-' +SUBSTRING(datecolumn,1,2)

there is no more problem to use ALTER COLUMN to update that column's data type from Varchar to Datetime. 

Conclusion

 As a summary, SQL Server only allows implicit conversion from Varchar to Datetime when the date format is "YYYY-MM-DD". If you want to learn more about implicit/explicit data conversion, please read technet's article: Data Type Conversion.