SQL Server max function from multiple columns
MAX function returns the maximum values across the rows in a column , but to retrieve the MAX values across columns then there is no function available like COALESCE which gives the first non-null value across columns.
There are many other techniques available to get the Max values , but I found this one very effective in terms of simplicity and performance.
To simulate this , create a table with some test data using the below query.
** same thing we can achieve through union as below , but the advantage of using the Query-1 is you can make the statement simpler even if the no.of columns are more than 2
but if you see the performance between both the queries , query 1 is taking 16% where query-2 is taking 84%
execute both the sql statements in SQL-1 and SQL-2 and Press Ctrl+L or click on [Display Estimated Execution Plan]
<click on the below Image to open in a new window>