My ex-colleague Paul Mcmillan pointed me at a thread on Stack Overflow that demonstrated a neat T-SQL trick to get the maximum value from a collection of columns in a row. Paul had never seen it before and neither had I so I figure one or two of you out there might learn something from it too.
In short you can use the VALUES clause to effectively union the values into a dataset and get the MAX from that dataset. Better demonstrated with code:
DECLARE @t TABLE(a INT,b INT,c INT);
INSERT @t VALUES(1,2,3),(9,8,7),(4,6,5);
, ( SELECT MAX(val)
FROM (VALUES (a)
) AS value(val)
) AS MaxVal
I'm sure many of you knew this already but if you didn't, well, you too have learnt something today. See more uses for the VALUES clause at Interesting enhancements to the VALUES Clause in SQL Server 2008
P.S. Oh, this only works in SQL Server 2008 and beyond.