Select first value if exists, otherwise select default value

The query feels tortured because SQL is designed to work with sets of data. You're using SQL like an ordinary programming language, trying to turn tables into scalars, and embedding special values in the query rather than storing it as data.

I recommend that you create and populate a ConfigurationDefaults table that is analogous to your Configuration table.

CREATE TABLE Configuration
( ID INTEGER IDENTITY PRIMARY KEY
, Name VARCHAR(123) UNIQUE
, Data VARCHAR(123)
);

CREATE TABLE ConfigurationDefaults
( Name VARCHAR(123) PRIMARY KEY
, Data VARCHAR(123)
);

INSERT INTO ConfigurationDefaults VALUES ('NameOfConfiguration', 'Default Value');

Then, create a view that merges the two, such that the Configuration settings override the defaults.

CREATE VIEW EffectiveConfiguration AS
    SELECT COALESCE(Cfg.Name, Def.Name) AS Name
         , CASE WHEN Cfg.Name IS NULL THEN Def.Data ELSE Cfg.Data END AS Data
        FROM Configuration AS Cfg
            FULL OUTER JOIN ConfigurationDefaults AS Def
                ON Cfg.Name = Def.Name;

Once that infrastructure is in place, the query is a joy to read and write!

SELECT Data FROM EffectiveConfiguration WHERE Name = 'NameOfConfiguration';

I would think that the first query you tried would perform better, as it doesn't have to aggregate anything. I believe it's how I would approach it. I would just reformat it so we can read it a little easier.

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

There are a couple of other little improvements to be made though. You need to alias this column. Otherwise you'll end up with a mile long name for it. (Depending on your RDBMS. I know SQL Server will name it with the expression string.)

The second thing I would do is declare a variable for 'NameOfConfiguration'. You're using the same parameter in two places, so it makes sense to declare a variable. This will also make things easier if you should decide to wrap this query up in a stored procedure.

If you have a static default value, you can just assign that to a variable and conditionally overwrite if a row is found. This first select returns data1, and the second select returns the default value as the name is not in the table.

create table #cfg (name varchar(32) primary key, id int, data varchar(32))
insert #cfg values ('name1', null, 'data1');
insert #cfg values ('name2', null, 'data2');
go

declare @data varchar(32)
set @data='defaultvalue';
select @data=data
from #cfg
where name='name1';
select @data;
go

declare @data varchar(32)
set @data='defaultvalue';
select @data=data
from #cfg
where name='name3';
select @data;
go

Why don't you just do a simple count with an if statement?

DECLARE @GenericVariableName VARCHAR(MAX)
SET @GenericVariableName = (SELECT TOP (1) Data
                             FROM Configuration 
                             WHERE Name = 'NameOfConfiguration')

SELECT isnull(@GenericVariableName, 'Default Value')

this would probably be a lot faster than using the EXIST statement

This is clearly a case where you would like to use ISNULL. But for that you need to return a row in case the config name doesn't exist. So that in turn implies you need a left outer join. You then create a dummy temp table with the value you are looking for, and left outer join on the actual data.

select
case when c.Name is null then 'Default Value' else c.Data end [Data]
from
(select 'NameOfConfiguration' [Name]) Target
left outer join Configuration c on c.[Name] = Target.[Name]