Storing Single Key/Value Settings

by data-centric on November 16, 2010

I read an interesting post today by Arnie Rowland about a simple but clever way to ensure only one row in a table used for application settings or config items.

I have found that tables like this can, if allowed to go unchecked, proliferate through a database which can cause minor, niggling maintenance issues and I can never remember where all the individual values are stored.

The solution I prefer to implement is a single ApplicationSetting table that holds all the individual settings using a key/value model. This means that there is just one place to look rather than having individual values scattered throughout various tables.

Such a table might look like this:

USE tempdb;
GO

IF OBJECTPROPERTY(OBJECT_ID(N'[dbo].[ApplicationSetting]'), N'IsUserTable') = 1
DROP TABLE [dbo].[ApplicationSetting];
GO

CREATE TABLE [dbo].[ApplicationSetting]
(
[SettingName] [varchar] ( 64 ) COLLATE Latin1_General_CI_AS NOT NULL
, [SettingValue] [sql_variant] NOT NULL
, CONSTRAINT [PK_ApplicationSetting] PRIMARY KEY CLUSTERED ([SettingName])
) ON [DEFAULT]
GO

And be populated like this:
<pre class="brush:sql">SET NOCOUNT ON;</pre>
IF NOT EXISTS
(
SELECT 1 FROM [dbo].[ApplicationSetting] AS [src]
WHERE [SettingName] = 'Database Debug Level'
)
INSERT INTO [dbo].[ApplicationSetting] ( [SettingName] , [SettingValue] )
VALUES ('Database Debug Level', 3);

IF NOT EXISTS
(
SELECT 1 FROM [dbo].[ApplicationSetting] AS [src]
WHERE [SettingName] = 'Extract Path'
)
INSERT INTO [dbo].[ApplicationSetting] ( [SettingName] , [SettingValue] )
VALUES ('Extract Path', 'C:\temp\');

SET NOCOUNT OFF;

Then I just retrieve the values I’m interested, as in this sample procedure:
IF OBJECTPROPERTY(OBJECT_ID(N'[dbo].[StoredProcedureName]'), N'IsProcedure') IS NULL
EXEC (N'CREATE PROCEDURE [dbo].[StoredProcedureName] (@ExtractFileName varchar(128), @Debug tinyint = NULL) AS RAISERROR(''StoredProcedureName Not Implemented'', 16, 1)');
GO

ALTER PROCEDURE [dbo].[StoredProcedureName]
(
@ExtractFileName varchar ( 128 )
, @Debug tinyint = NULL
)

AS

BEGIN
SET NOCOUNT ON

--! Standard/common variables
DECLARE @Error int
, @DatabaseDebug tinyint

--! Working variables
DECLARE @ExtractFolder varchar ( 128 )
, @ExtractPath varchar ( 256 )

--! Define any default inputs and other working values
SELECT @DatabaseDebug = CAST(SettingValue AS tinyint)
FROM dbo.ApplicationSetting WITH (NOLOCK)
WHERE SettingName = 'Database Debug Level'

SELECT @ExtractFolder = CAST(SettingValue AS varchar(128))
FROM dbo.ApplicationSetting WITH (NOLOCK)
WHERE SettingName = 'Extract Path'

SET @Error = 0
SET @Debug = COALESCE(@Debug, @DatabaseDebug, 0)
SET @ExtractPath = @ExtractFolder + @ExtractFileName

IF @ExtractPath IS NULL RAISERROR('Path component is missing', 16, 1)

--!
--! IMPLEMENTATION OMMITTED
--!

RETURN(@Error)
END
GO

Notice how for both values, I code defensively in case the expected key/value pairs are not found.

One might think that something like this might lend itself to a scalar function but as noted a couple of years back by Simon Sabin among others, we need to be careful about the performance impacts of using scalar functions. Besides, the amount of SQL required to retrieve a value from the ApplicationSetting table and cast or convert it to the required data type isn’t much different to that required to call a UDF.

Previous post:

Next post: