Does COALESCE with SELECT Statement Lazy evaluate?

SQL Server 2008 related question

I have a table that records people who have applied for a credit card

CREATE TABLE [dbo].[CREDITCARD_APPLICATION](
    [CREDITCARD_APPLICATION_ID] [bigint] IDENTITY(1,1) NOT NULL,
    [PRIMARY_CUSTOMER_ID] [int] NOT NULL,
    [SECONDARY_CUSTOMER_ID] [int] NULL,
    [PRIMARY_CARD_ID] [int] NULL,
    [SECONDARY_CARD_ID] [int] NULL,
    [STORE_ID] [int] NULL,
    [CARD_REFERRAL_SOURCE] [nvarchar](50) NULL,
    [CAMPAIGN_CODE] [varchar](30) NULL,
    [CREATED_DATE] [datetime2](3) NOT NULL,
    [CREATED_BY] [varchar](50) NOT NULL,
    [LAST_MODIFIED_DATE] [datetime2](3) NULL,
    [LAST_MODIFIED_BY] [varchar](50) NULL,
    [VERSION] [timestamp] NOT NULL
    )

I also have a view that joins to this table, and contains cards and customers. I need to fetch one piece of information out of this table, which is campaign_code, and I am doing this in the view by using coalesce with nested selects.

SELECT....some data....
    ,coalesce((SELECT TOP 1 CAMPAIGN_CODE FROM dbo.CREDITCARD_APPLICATION cca where     cca.PRIMARY_CARD_ID=cc.card_id ORDER BY cca.CREATED_DATE DESC),
        (SELECT TOP 1 CAMPAIGN_CODE FROM dbo.CREDITCARD_APPLICATION cca     where cca.SECONDARY_CARD_ID=cc.card_id ORDER BY cca.CREATED_DATE DESC))

  AS LatestCampaignCode

What I am wondering is whether the 2 select statements will always execute, or if it finds a result from the first one it will stop processing there. It seems a bit inefficient to have these selects always execute, in fact in a lot of (most) cases there won't be any record that matches in the table

Solution

Yes, I believe it's going to execute both queries (with around 90% confidence), but not necessarily as you have them.

I don't know exactly what it does, but I would guess that it's going to go get all the information from the primary query, then it's going to execute something similar to a join on the second query using data from the first. then it will go execute the third query similar to a join using only the rows that still had null values. But it's up to the optimizer how to do it, and it may actually pull the data in a way that is different than how your sql statement is intended to flow.

What I said will make more sense if you go look at the execution plan. A few things that I will say that may make things a bit more clear as well.

COALESCE is just turns into a CASE statement, and thus the same rules apply as a case statement
The query you have could likely be turned into joins instead of correlated sub-queries and I would expect that the query analyzer / optimizer will do just that and that is in-fact how it's being executed.