Select first value if exists, otherwise select default value Given an instance of SQL Server, imagine there's a table named Configuration, which has three columns: ID, Name, and Data.  There should be no duplicate rows for Name.

Now imagine that you want to select the data stored for a particular configuration, but if that configuration doesn't have a row in the table, then you just want to select a default value instead.

This, then, is what I have been using:

SELECT CASE
         WHEN EXISTS(SELECT 1
                     FROM   Configuration
                     WHERE  Name = 'NameOfConfiguration')
           THEN (SELECT Data
                 FROM   Configuration
                 WHERE  Name = 'NameOfConfiguration')
         ELSE 'Default Value'
       END 


But that looks bad, so I was trying to think of a way to shorten the EXISTS() call, make the code a little clearer, get rid of duplicate code, and possibly even speed it up, and this is what I've been able to find so far:

SELECT CASE
         WHEN COUNT(1) &gt; 0
           THEN MIN(Data)
         ELSE 'Default Value' 
       END
FROM   Configuration
WHERE  Name = 'NameOfConfiguration' 


Is this latter code snippet the right way to go about this sort of thing in general? Unfortunately, SQL Server requires an aggregate function call or GROUP BY clause to be applied to the latter, and FIRST() evidently doesn't exist in SQL Server.  I am not restricting this call to MIN() to any sort of datatype whatsoever.  Is there a better way to write this?
The shorter query I can think of is

SELECT COALESCE(MAX(Data), 'Default Value')
FROM   Configuration
WHERE  Name = 'NameOfConfiguration';


If the configuration is present it'll take it.
If the configuration is not present the MAX will generate a NULL value (the MAX of nothing is NULL) that will be coalesced to the default value



There is a more generic approach that takes care of what said by Martin Smith in his comment, but is more verbose (using the hypothesis that the configuration name are unique)

WITH Param AS (
  SELECT ID, Data
  FROM   Configuration
  WHERE  Name = 'NameOfConfiguration'
  UNION ALL
  SELECT NULL, 'Default Value'
)
SELECT TOP 1
       Data
FROM   Param
ORDER BY ID DESC


The ID of the Default Value is set to NULL to avoid to use a magic number.
From the BOL: NULL values are treated as the lowest possible values.

IMO a general solution should be used only where a specific one is not possible, and that doesn't seems the case. Also in a generic case the Default Value will need to be a valid value for the datatype of the configuration values otherwise the UNION will return a conversion error.