How to retrieve the largest value between multiple columns in a SQL Server table

Views: 7.323
Reading Time: 2 minutes

Hello readers,
Good night!

In this post I will briefly demonstrate how to retrieve the largest value between multiple columns in a SQL Server table. I've seen several solutions to this need, but are you using the most performative of them?

Generating a test mass

To make it easier to see the results, let's create some test data:

Original Data:
SQL Server - Find MAX value from multiple columns in a SQL Server table

Expected outcome:
SQL Server - Find MAX value from multiple columns in a SQL Server table - Expected Result

1 Solution - Using VALUES

2 Solution - Using UNPIVOT

3 Solution - Using UNION

Performance test

Once we understand the 3 solutions proposed above, let's test performance to identify which runs at the shortest possible time and cost. Thus, we can say what should be the best solution adopted for this situation:

1 Solution
SQL Server - Find MAX value from multiple columns in a SQL Server table - Performance 1

2 Solution:
SQL Server - Find MAX value from multiple columns in a SQL Server table - Performance 2

3 Solution:
SQL Server - Find MAX value from multiple columns in a SQL Server table - Performance 3

Final result:
SQL Server - Find MAX value from multiple columns in a SQL Server table - Performance

As we can see, the 1 solution, besides being very small, is the most performative.

Thanks for the visit!

sql server greater value multiple multiple columns of a table view query Find MAX value from multiple columns in a SQL Server table

sql server greater value multiple multiple columns of a table view query Find MAX value from multiple columns in a SQL Server table