Most Efficient Way to Return Y/N If One or More Columns Contain Non-Null Value?

What is the most concise way to return CHAR(1) indicator Y/N if one of n columns in a row contain a non-null value?

Performance is important, but not the primary consideration, in this case.

The straightforward way seems to be:

SELECT      CASE WHEN (C.TerminatedDate IS NULL
                       AND C.SelfClosedDate IS NULL
                       AND ...)
                 THEN 'Y'
                 ELSE 'N' END AS 'OpenI开发者_如何学Cnd'

  FROM      Customers C

Curious if there is a better way; aware of COALESCE():

SELECT      CASE WHEN COALESCE (C.TerminatedDate, C.SelfClosedDate, ...) IS NULL
                 THEN 'Y'
                 ELSE 'N' END AS 'OpenInd'

  FROM      Customers C

Is there a better way?

Database server is SQL Server 2008.

Since no one is suggesting it, and the question asks for concise..

For the same data types, a straight COALESCE is best

coalesce(a,b,c,d) is not null

If you will be dealing with different data types, try a modified COALESCE

coalesce(LEFT(a,1),LEFT(b,1),LEFT(c,1)) is not null

Sample:

create table abc (a int, b datetime, c varchar(max), d image)
insert into abc select 1, GETDATE(), '', null
insert into abc select 1, null, '', null
insert into abc select 1, null, '', 0x123123
insert into abc select null, null, '', 0x123123
insert into abc select null, GETDATE(), '', 0x123123
insert into abc select null, null, null, null
insert into abc select 88, GETDATE()+3, null, null
insert into abc select 88, GETDATE()+3, 'gdasdf', null
insert into abc select null, null, '222', 0x123123
insert into abc select null, null, 'abcdef', 0x123123

select *, case when coalesce(LEFT(a,1),LEFT(b,1),LEFT(c,1)) is not null then 'N' else 'Y' end
from abc

If you are not using exotic types like VARCHAR(MAX) or IMAGE, then you can use SQL_VARIANT with COALESCE

create table abc (a int, b datetime, c varchar(10), d image)
insert into abc select 1, GETDATE(), '', null
insert into abc select 1, null, '', null
insert into abc select 1, null, '', 0x123123
insert into abc select null, null, '', 0x123123
insert into abc select null, GETDATE(), '', 0x123123
insert into abc select null, null, null, null
insert into abc select 88, GETDATE()+3, null, null
insert into abc select 88, GETDATE()+3, 'gdasdf', null
insert into abc select null, null, '222', 0x123123
insert into abc select null, null, 'abcdef', 0x123123

select *, case when coalesce(convert(sql_variant,a),b,c) is not null then 'N' else 'Y' end
from abc

Theoretically, you should get the advantages of short-circuiting here by immediately returning when any value is not null

Select Case
        When C.TerminatedDate Is Not Null Then 'N'
        When C.SelfClosedDate Is Not Null Then 'N'
        ...
        Else 'Y'
        End

That said, I doubt there is a significant difference in performance between this solution and your above solutions (assuming you can use Coalesce).

Here is a "outside the box" solution. A persisted computed column.

CREATE TABLE [dbo].[Customer](
  [CustomerID] [int] IDENTITY(1,1) NOT NULL,
  [Date1] [datetime] NULL,
  [Date2] [datetime] NULL,
  [AllNulls]  AS (case when [date1] IS NULL AND [date2] IS NULL then 'Y' else 'N' end) PERSISTED NOT NULL,
 CONSTRAINT [PK_Cusomter] PRIMARY KEY CLUSTERED 
 (
    [CustomerID] ASC
 )
)

Without further information, the former (a case statement) is the most efficient means in MS SQL Server. (Although with the same assumptions, I personally would return it as a bit value and perform conversion to Y/N, which is really a display representation or viewmodel concern.)

Now, if filtering is going to be done repeatedly, and for performance reasons it is appropriate to do so at the database engine, performing the NULL test in the where clause would improve things further. If you have many of these, you could consider aggregating them on update (via a persisted computed column beautifully suggested by another answerer, Stored Proc, data model, or heaven forbid a trigger).

If all columns are datetime, this works unless the total overflows. Note zero = 01 Jan 1900 so there is a lot of headroom before 31 Dec 9999

NULL added to any other expression gives NULL

SELECT
    CASE
         WHEN C.TerminatedDate + C.SelfClosedDate + C.OtherDate + .. IS NULL THEN
                 THEN 'Y'
                 ELSE 'N'
         END AS 'OpenInd'

  FROM      Customers C

One case where the concise coalesce approach could definitely hurt performance is dealing with off row data that also requires an implicit cast.

CREATE TABLE coalescetest
(
id int identity(1,1) PRIMARY KEY,
bigvarchar1 varchar(max) NULL,
bigvarchar2 varchar(max) NULL,
bignvarchar1 Nvarchar(max) NULL,
bignvarchar2 Nvarchar(max) NULL
)

INSERT INTO coalescetest
SELECT TOP 100
     REPLICATE(CAST('A' AS VARCHAR(MAX)),12000),
     REPLICATE(CAST('A' AS VARCHAR(MAX)),12000),
     REPLICATE(CAST('A' AS NVARCHAR(MAX)),12000),
     REPLICATE(CAST('A' AS NVARCHAR(MAX)),12000)   
FROM master..spt_values             

SET STATISTICS IO ON    

SELECT      id, CASE WHEN COALESCE (bigvarchar1,bigvarchar2,bignvarchar1,bignvarchar2) IS NULL
                 THEN 'Y'
                 ELSE 'N' END AS Result

FROM coalescetest

Gives

Table 'coalescetest'. Scan count 1, logical reads 5, physical reads 0, read-ahead reads 0, lob logical reads 400, lob physical reads 0, lob read-ahead reads 300.

SELECT      CASE WHEN (bigvarchar1 IS NULL
                       AND bigvarchar2 IS NULL
                       AND bignvarchar1 IS NULL
                       AND bignvarchar2 IS NULL)
                 THEN 'Y'
                 ELSE 'N' END AS Result
  FROM     coalescetest

Gives

Table 'coalescetest'. Scan count 1, logical reads 5, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.