To be with NULL or not to be with NULL

with help from ISNULL and COALESCE functions.

From time to time you probably catch an idea about making better performance on your SQL Server. So, I mean SQL Server 2005 or "Yukon", as you wish.

I have read plenty of articles on the internet about performance related questions. Some authors say, that ISNULL is better and other say that COALESCE is speed like CASE vs. IF-THEN, for instance.

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

I ran this script on my machine, so results are better for ISNULL function. IMHO 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 NON-NULL columns will be always faster.

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