How Does The CHOOSE Command Affect Performance?

Questions absolutely drive my blog content and I really liked this one: how does the T-SQL CHOOSE command affect performance.

On the face of it, I honestly don’t think it will affect performance at all, depending on where and how you use it. However, the answer is always best supplied by testing.

T-SQL CHOOSE Command

The CHOOSE command was added in SQL Server 2012. It’s fairly straight forward. You supply an array and a numbered index for that array and CHOOSE will pull the matching value for that index. It works like this. We’ll start with a simple proc and execute it:

CREATE OR ALTER PROC dbo.CarrierAndFlag
(
    @SalesOrderID INT,
    @Flag INT
)
AS
BEGIN
    SELECT sod.CarrierTrackingNumber,
           CHOOSE(@Flag, 'A', 'B', 'C') AS Flag
    FROM Sales.SalesOrderDetail AS sod
    WHERE sod.SalesOrderID = @SalesOrderID;
END;
GO

EXEC dbo.CarrierAndFlag @SalesOrderID = 43662, -- int
                        @Flag = 2;             -- int

The query runs. The results come back showing the ‘B’ value for the Flag. The execution plan looks like this:

Simple seek against the clustered index. Nothing to it. The work is done in the Compute Scalar operator of course. We can see that here:

Now, we could modify our proc as follows, to use the CHOOSE against columns instead of a supplied array:

CREATE OR ALTER PROC dbo.CarrierAndFlag
(
    @SalesOrderID INT,
    @Flag INT
)
AS
BEGIN
    SELECT sod.CarrierTrackingNumber,
           CHOOSE(@Flag, sod.OrderQty,sod.LineTotal,sod.UnitPrice) AS Flag
    FROM Sales.SalesOrderDetail AS sod
    WHERE sod.SalesOrderID = @SalesOrderID;
END;
GO

The resulting execution plan is identical to the one above, except of course that the Compute Scalar has changed:

The documentation explains about how it uses data type precedence to determine the conversion, but overall, we’re not looking at performance issues here. In fact, on average, the first iteration of the proc ran in about 449 mc with 3.24 reads. The second about 447 mc with 3.24 reads. In other words, identical in every way.

So we can now safely conclude that CHOOSE doesn’t affect performance, right?

Well, hold on a sec.

Nonclustered Indexes and CHOOSE

I’m going to change the procedure to look for ProductID. There’s a nonclustered index that should be useful in AdventureWorks:

CREATE OR ALTER PROC dbo.CarrierAndFlag
(
    @ProductID INT,
    @Flag INT
)
AS
BEGIN
    SELECT sod.CarrierTrackingNumber,
           CHOOSE(@Flag, sod.OrderQty,sod.LineTotal,sod.UnitPrice) AS Flag
    FROM Sales.SalesOrderDetail AS sod
    WHERE sod.ProductID = @ProductID;
END;
GO

And, depending on the value you pass for ProductID (parameter sniffing, different story), you’ll get the following plan:

So, CHOOSE doesn’t seem to affect index choice. Can we add an INCLUDE operator to eliminate the Key Lookup and still have CHOOSE work appropriately? Here’s my index:

CREATE INDEX ChooseTest
CREATE INDEX ChooseTest
ON Sales.SalesOrderDetail (ProductID)
INCLUDE (
            CarrierTrackingNumber,
			OrderQty,
            LineTotal,
            UnitPriceDiscount,
			UnitPrice
        );

Now when we execute the query we get the following execution plan:

There we go. Question answered, you can use CHOOSE in any situation and performance won’t be affected.

Hang on, one more point.

CHOOSE and the WHERE Clause

What happens when we move it out of the nice safe spot in SELECT? Let’s modify the proc again:

CREATE OR ALTER PROC dbo.CarrierAndFlag
(
    @Flag INT
)
AS
BEGIN
    SELECT sod.CarrierTrackingNumber
    FROM Sales.SalesOrderDetail AS sod
    WHERE sod.ProductID = CHOOSE(@Flag, 897, 998, 432);
END;
GO

If we then execute the code for the @Flag value of 2, we get the following plan:

And this is where things get fun. If we recompile the proc or remote it from cache and pass in the @Flag value of 1, the results change from 685 to 2. However the row estimate stays the same. It’s using an average against the statistics in the index. However, it’s using the index.

Let’s break it.

Breaking Index Use of CHOOSE

I’m honestly shocked that moving that to the WHERE clause didn’t result in an immediate scan. Sure, maybe of the nonclustered index, but absolutely a scan, not seek.

So, doing some experiments, changing the value to a string just resulted in a benign implicit conversion, but the index was still used. Trying it with decimals became the same. That makes sense based on the documentation. Even mixing the data types didn’t affect the plans.

Then, I added a column to the CHOOSE statement. And we got this for the plan:

The key is found in the predicate:

Adding in the column, and I tried with different data types, safe ones, nothing crazy, and no matter what, I was back on the scan.

Conclusion

There are a whole bunch more tests I could write up. If you had asked me how this would behave in the WHERE clause, I would have predicted that it was always going to be scan. I was a little surprised that it could use the indexes a little better. However, the behavior of the addition of a column made a lot of sense to me.

Overall, I’d say using CHOOSE in the SELECT clause is fine from a performance standpoint. In a filter clause, if you’re using values, you’re probably OK. If you’re trying to toss columns in there, don’t.

NOTE: The post originally stated that CHOOSE was introduced in 2016. It was actually introduced in 2012 and the text now reflects that. Thanks for catching this Robert.