Least and Greatest New T-SQL Enhancements in SQL Server

This article will explore these two new T-SQL enhancements, LEAST and GREATEST, covering their syntax, use cases, and simple examples. Additionally, we will also explore their impact on day-to-day database operations and how they compare with traditional approaches. For SQL developers, data engineers, and DBAs, these functions can bring efficiency to many querying tasks.

Introduction

Before introducing these functions in SQL Server 2022, finding the minimum or maximum value from a set of columns or expressions required more complex logic or verbose CASE statements. The LEAST and GREATEST functions simplify these operations, offering more intuitive and concise ways to achieve the desired outcomes.

Syntax Overview

LEAST: Returns the smallest value from a list of expressions.

LEAST ( 
    expression1, 
    expression2, 
    ..., 
    expressionN 
)

GREATEST: Returns the largest value from a list of expressions.

GREATEST (
    expression1,
    expression2,
    ...,
    expressionN
)

Both functions can compare multiple arguments (expressions), and their arguments can be any data type that supports comparison (numeric, date, or even string types). The result will be of the same data type as the input expressions.

LEAST and GREATEST vs. Traditional Methods

Prior to SQL Server 2022, SQL developers typically had to use CASE statements or a combination of MIN and MAX functions along with complex logic to compare multiple columns or values. While these methods worked, they were cumbersome and often led to less readable queries.

Examples of Traditional Methods

To compare multiple columns before SQL Server 2022.

SELECT 
    CASE 
        WHEN Column1 <= Column2 AND Column1 <= Column3 THEN Column1
        WHEN Column2 <= Column1 AND Column2 <= Column3 THEN Column2
        ELSE Column3
    END AS SmallestValue,
    
    CASE 
        WHEN Column1 >= Column2 AND Column1 >= Column3 THEN Column1
        WHEN Column2 >= Column1 AND Column2 >= Column3 THEN Column2
        ELSE Column3
    END AS LargestValue
FROM MyTable;

This logic can quickly become unwieldy if you have many columns to compare. Now, with the new functions in SQL Server 2022, this task becomes much simpler.

Using LEAST and GREATEST

The same example uses LEAST and GREATEST.

SELECT 
    LEAST(Column1, Column2, Column3) AS SmallestValue,
    GREATEST(Column1, Column2, Column3) AS LargestValue
FROM 
    MyTable;

Examples

Example 1. Using LEAST to Compare Different Tax Rates

Let's assume you want to compare the TaxAmt for several sales orders across different order dates and determine the minimum tax amount for each sales order. The Sales. The salesOrderHeader table includes details about each sales order, including the tax amount.

We’ll use the LEAST function to identify the minimum tax amount for several orders.

USE [AdventureWorks2022];
GO

SELECT 
    SalesOrderID,
    LEAST(TaxAmt, Freight, SubTotal) AS SmallestAmount
FROM 
    Sales.SalesOrderHeader WITH (NOLOCK)
WHERE 
    SalesOrderID IN (43659, 43660, 43661);

Output

Output

Example 2. Use GREATEST to Find the Maximum Bonus, Sick Leave, and Vacation Hours

Let’s now use the HumanResources.Employee table. We want to compare each employee's VacationHours, SickLeaveHours, and Bonus (we’ll assume a fixed bonus column for demonstration) and find out which of these values is the largest for each employee.

USE [AdventureWorks2022];
GO

SELECT 
    BusinessEntityID,
    GREATEST(VacationHours, SickLeaveHours, 10) AS MaxBenefit
FROM 
    HumanResources.Employee
WHERE 
    BusinessEntityID BETWEEN 1 AND 10;

Output

Table

Performance Considerations

While the LEAST and GREATEST functions simplify queries, it’s important to consider their performance impact in large datasets or complex queries. Typically, these functions are efficient, especially compared to more verbose alternatives such as CASE statements. However, in cases where you're comparing large datasets or many columns, performance tuning may still be necessary.

Use Cases for DBAs, Data Engineers, and SQL Developers

For SQL developers, LEAST and GREATEST streamline common query patterns, making code easier to read and maintain. This is especially beneficial when writing queries that compare multiple values across different columns.

For data engineers, these functions simplify data pipeline transformations, where selecting the minimum or maximum value from a set of columns is common. Whether dealing with dates, numeric data, or even strings, LEAST and GREATEST can reduce the complexity of transformation logic.

DBAs will also appreciate the reduced complexity when working with large, production-level queries that must compare multiple values or optimize reporting views. These functions can reduce the need for additional table scans or subqueries in complex reports.

Conclusion

Whether you're comparing sales figures, tracking project dates, or handling any scenario that involves finding the smallest or largest value among multiple expressions, LEAST and GREATEST make the job easier. By understanding their behavior, particularly with respect to NULL values, you can ensure that your queries are both efficient and accurate.