To be with NULL or not to be with NULL

using ISNULL and COALESCE  sql functions.

From time to time you probably catch an idea about optimizing performance in your sql queries.
At time I was written this article, I was using  SQL Server 2005.

I had read much articles about optimizing queries in SQL queries. Some authors had wroted, that ISNULL function is better and other had said that COALESCE is faster, because COALESCE function work similar to CASE statement in programming languages.

So I created a small SQL script to measure which method should be faster.

I ran this script on my machine, so results were better for ISNULL function. I thought it's because COALESCE must hold datatype for each tested value.

Please also notice differences between selecting values from NULL-enabled and non NULL enabled columns with the same data types. I think, that querying over not NULL enabled columns should gain some speed.

Function	Data type	NullValues Presence	MinCPUTime	MaxCPUTime	AvgCPUTime	Min Timelength [ms]	Max Timelength [ms]	Avg Timelength [ms]
COALESCE	int	AllColumnsNull	13.33	14.66	14.00	388.95	597.64	493.30
COALESCE	int	AllThreeNotNull	14.02	15.77	14.90	455.90	534.09	495.00
COALESCE	int	FirstColumnNull	15.06	17.93	16.50	509.25	624.54	566.90
COALESCE	int	LastColumnNotNull	13.16	15.23	14.20	457.96	568.63	513.30
COALESCE	varchar	AllColumnsNull	27.82	30.97	29.40	854.74	1287.25	1071.00
COALESCE	varchar	AllThreeNotNull	29.65	32.54	31.10	972.10	1301.89	1137.00
COALESCE	varchar	FirstColumnNull	33.50	39.09	36.30	1075.53	1567.66	1321.60
COALESCE	varchar	LastColumnNotNull	31.36	34.03	32.70	1029.25	1169.94	1099.60
ISNULL	int	AllColumnsNull	11.16	12.43	11.80	350.55	494.64	422.60
ISNULL	int	AllThreeNotNull	9.53	10.66	10.10	245.90	526.09	386.00
ISNULL	int	FirstColumnNull	11.16	12.63	11.90	367.54	419.65	393.60
ISNULL	int	LastColumnNotNull	11.28	13.11	12.20	367.91	463.48	415.70
ISNULL	varchar	AllColumnsNull	24.79	26.20	25.50	825.06	1103.33	964.20
ISNULL	varchar	AllThreeNotNull	19.64	21.75	20.70	585.75	965.64	775.70
ISNULL	varchar	FirstColumnNull	25.36	26.83	26.10	808.47	984.52	896.50
ISNULL	varchar	LastColumnNotNull	28.47	30.12	29.30	929.79	1095.00	1012.40