ISNULL() vs COALESCE()

ISNULL()	COALESCE()
Specifc to SQL SERVER	ANSI standard. The query can be used in another RDBMS which follows the ANSI standard.
SELECT ISNULL(NULL, NULL) --O/P null	SELECT COALESCE(NULL, NULL) Error: None of the result expressions in a CASE specification can be NULL. In coalesce atleast one parameter should not be null.
DECLARE @a VARCHAR(10) DECLARE @b VARCHAR(10) SELECT ISNULL(@a, @b) --O/P null	DECLARE @a VARCHAR(10) DECLARE @b VARCHAR(10) SELECT COALESCE (@a, @b) --O/P null
Required 2 parameters	Requires atleast 2 parameters
Accepts only 2 parameters. But we can nest like this if we want more than 2 parameter. ISNULL(ISNULL(Col1,Col2), Col3)	I tried 18600 parameters. If it reaches more than that or with this count itself the SQL SERVER throws "System.OutOfMemoryException". So I think this blongs to hardware specification.
Consider 2nd parameter data type as first datatype. (i.e. It uses as the first parameter datatype.) If the length of the second parameter is greater than the first parameter it will be truncated to first parameter size. Eg.1 DECLARE @a VARCHAR(10) DECLARE @b DATETIME SET @b = GETDATE() SELECT CONVERT(VARCHAR(11), @b, 100) SELECT ISNULL(@a, @b) --O/P Apr 13 201 --It took the format 100 and varchar size to 10. --Change the varchar size of below query and you will understand. SELECT ISNULL(@a, CONVERT(VARCHAR(11), @b, 100)) SELECT ISNULL(CAST(@a as varchar(11)), @b) --O/P Apr 13 201 --O/P Apr 13 2010 Eg.2 DECLARE @a VARCHAR(5) DECLARE @b VARCHAR(8) SET @b = 'SQL SERVER' SELECT ISNULL(@a, @b) --O/P SQL S It truncates to first parameter size. Eg.3 DECLARE @a VARCHAR(5) DECLARE @b VARCHAR(8) DECLARE @c INT SET @c = 123456 SELECT ISNULL(@a, @c) --O/P * If SET @c = 12345--O/P 12345 Since @a size is 5 which is the first parameter.	Multiple datatype is allowed. It took highest data type in the expression list. If we are having int and double. It took double. Eg.1 DECLARE @a VARCHAR(10) DECLARE @b DATETIME SET @b = GETDATE() SELECT COALESCE(@a, @b) --O/P 2010-04-13 17:21:12.330 Eg.2 DECLARE @a VARCHAR(5) DECLARE @b VARCHAR(8) SET @b = 'SQL SERVER' SELECT COALESCE(@a, @b) --O/P SQL SERV It tooks the datatype of the parameter which is not null. Eg.3 DECLARE @a VARCHAR(5) DECLARE @b VARCHAR(8) DECLARE @c INT SET @c = 123456 SELECT COALESCE(@a, @b, @c) --O/P 123456 DECLARE @a INT DECLARE @b DATETIME SET @a = 2 SET @b = GETDATE() SELECT COALESCE (@a, @b) --O/P 1900-01-03 00:00:00.000 if we set SET @a = 0 --O/P 1900-01-01 00:00:00.000 if we set, it increments month and date SET @a = 35 --O/P 1900-02-05 00:00:00.000

Reference: sqlserver-qa.net
Deciding between COALESCE and ISNULL