## A New (and Hopefully Better) Approach to Constants

Author
Message
Eric Wilson
SSC-Enthusiastic

Group: General Forum Members
Points: 137 Visits: 44
This just shows how people will overly complicate a set-oriented or SQL based solution because they are trying to fit it into a world they are more comfortable with.

In compiled-programming we use constants so we don't have the magic #100 spread throughout the code. When the "max-cartons-per-palette" rule changes to 150, we don't want to find all the cases of "100" and try to figure out whether it was *that* 100 or some other 100 in order to safely change it to 150.

Under the hood, the compiler turns "MAX_CARTONS" into the literal 100 for when the program actually executes.

In SQL and table-oriented designs, there are 2 major differences.

First, the code is interpreted (generally) and not compiled. Other posters have already pointed out the pitfalls with some of these "functions-for-constants" approach.

Second, and more importantly, this is precisely what lookup tables were designed for-- to assign a simple constant to something bigger that could change, like a status-code-description.

Far and away the most common "constant" situation I encounter are things like "status code 3 means 'failed'". So people start coding around it in all sorts of convoluted ways because they come from a programming background where they were taught that hard-coding a "3" in their code is EVIL and they will go to programmer hell for it. I saw one case where the programmer was doing:

select ...
from ...
where StatusCode = (select StatusCode from LookupTable where description = 'failed')

Yikes!

The problem is-- it's not the NUMBER that will change. It's the other parts. Anything *except* the number might change. "Failed" might become "Failure" or "Didn't Work". But in good lookup table design, the 3 will remain always and forever the surrogate to identify the underlying concept of a failed state.

The lookup table provides a constant, never changing source of this fact and is always available for reference if needed. The 3 will never change.

It would be NICE if T-SQL allowed more structured programming, like defining other modules that you can "include" and such. Basically if it were compiled. But for now it's not. And workarounds like this article proposes just make it more complex without adding value.

The best solution I've seen is simply to work-around T-SQL's language by declaring constants at the top of a procedure:

declare @STATUS_FAILED int; set @STATUS_FAILED = 3;
... (later in code) ...
select field
from table
where Status = @STATUS_FAILED;

... Then use that variable (upper-case to make visually distinct from ones that can change during the procedure) in the rest of the code.

We don't have to worry about ever changing the 3 to mean something else, so it isn't a case where having some named constant makes maintenance easier.

An almost-as-good alternate is to just hard-code the numeric with a comment:

SELECT ....
FROM MyTable
WHERE Status = 3 /*failed*/

Remember, for all the cases driven by lookup tables, it's the *number* (or whatever PK you used) that doesn't change.

For other numeric constants like Int-Upper-Bound or Pi or something, I agree with the other poster about using a one-row table. I personally use an instead-of upd/del trigger to disallow changes to that table except for official rollout scripts we publish. But same idea.

To the author: By the way, please go back and read more on relational. Not only because of the nonsense about constants, but to also understand why identifying them by "Western" or "Region.Western" has nothing to do with relational theory at all. Start with Chris Date's stuff.

noeld
SSCertifiable

Group: General Forum Members
Points: 6320 Visits: 2048
Jobob Smith (10/16/2007)
Does the constant HAVE to be stored in a table??

Would it not be easier to just have the UDF return the value? (it is constant after all)

as in:

CREATE FUNCTION fnGetConst(@cWanted INT)
RETURNS VARCHAR(5)
AS
BEGIN
DECLARE @RetValVARCHAR(5)

SELECT @RetVal = case @cWanted when 1 THEN 'boo'
when 2 THEN 'hoo' END
return @RetVal
END

In relational databases lookup tables are the way to go. Period.
For several reasons:
1. Flexibility
2. The engine is optimized to handle tables ( use statistics of the values etc...)
3. That is the way Relational databases are supposed to be handled.
4. Totally agree that the table will remain in memory most of the time and no disk access is incurred!

Cheers,

* Noel
Paul.
SSC Veteran

Group: General Forum Members
Points: 207 Visits: 971
I would've thought a simple view would be OK for the previous example, and hard-code the "3" in it. This view can then be used everwhere, thus there should only be one hard-coded occurence of "3", (which is the minimum required - even constants hard-code the value in their definition!). Or am I missing something?

As for all other "constants", why can't you define it anywhere you like? They'll all require the hard-coding of something. A function for its name, (plus any parameters if you follow the article's suggestions), whilst tables need their name and any identifying column/value, (depending if it only has one row/multiple columns, or two columns/multiple rows).

Until SQL Server offers an actual "CONSTANT" keyword, then just use whatever's appropriate to your project, and stop moaning about everyone else's choices.

Paul H

Paul

RiK Muñoz
SSC Veteran

Group: General Forum Members
Points: 260 Visits: 112
Well, I would have to agree that the article is a bit of a shameless plug in order to get us to download the cool stuff offa their site! But as far as I can see, the stuff I downloaded IS quite cool, so no harm done IMO, in fact saved me a bit of work!

Con mucho carino,

RiK Munoz

colin.Leversuch-Roberts
SSCrazy

Group: General Forum Members
Points: 2725 Visits: 715
constants don't belong in sql , set a view as a constant? god help us!

The GrumpyOldDBA
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
Ian Yates
Ten Centuries

Group: General Forum Members
Points: 1056 Visits: 445
I cannot see the benefit of invoking the CLR for such a trivial purpose. The ideas of a single row table/view with the constants as columns is probably best. Someone else has already rightly pointed out that a function that does not accept params and only has a simple return statement is easily handled by the SQL engine and doesn't result in table scans.

The other big problem I saw with the bit of the article I read was the author's claim that the .NET hashtable equivalent stores and returns varchars, which can IMPLICITLY be converted to ints, etc.... When you compare your int data in a table with the varchar result of this hashtable, you may have your nicely indexex int column being implicitly converted to a varchar... yuk.

JP-317675
SSC-Enthusiastic

Group: General Forum Members
Points: 137 Visits: 90
I'd agree with post instructing everyone to hard-code the value into a variable at the top of your code although I would always do so by referencing a lookup table of some kind.

Despite the fact that the value in the id column in your lookup table will never change you may find that your need to use that value in your code will. If you need to stop using that constant and replace it with another you need to be able to identify all your procedures/functions/views that use it.

As an example, say a table that contains status information has the status "failed" split into three new values.

Eg. Your original status FAILED (1) is split into FAILED - Pending (5) and FAILED - Notified (6) and FAILED - complete (7).

Now you want to change all stored procedures that use the constant value of 1 and change it to 5 (Dont' ask me why you just do). How would you easily identify which of your stored procedures have used the old failed status of 1 if the only reference to the value in your stored procedures is a declaration followed a set statement of SET @Failed = 1?

It's for that reason i'd advocate looking up the value in a lookup table within your code as much as possible. It is a simple thing to see which objects rely on other objects in your database if you refer to it within your code. If you simply hard code a value then well....good luck when you need to find it later.

This ability to "find" where you have used constants could be achieved using functions but it is my preference not to do so.

-- JP
tymberwyld
SSC Veteran

Group: General Forum Members
Points: 284 Visits: 274
I think I'm in the boat of not agreeing with this. Although I do like bleeding edge stuff being a developer AND a DBA, this just doesn't jive. I use constants in another way.

All my Lookup tables are in one table (well, two if you count the parent table that is just there to categorize the values). The parent table ReferenceLists stores the distinct list of Lookup "Lists". The ReferenceValues stores all the values for each list. Each List has a constant and each value has a constant (optional). I use the Constant for the Lists all the time (which you'll see in the View Triggers). However, the Constants for the values are only used when needed. This strategy gives complete flexibility for adding new Lookup Lists where I can even allow end-users to create their own Lookups (in the case of a CRM application where users want to add their own User-Defined Fields and one of those Fields has specific values).

### Table Scripts

`CREATE TABLE [dbo].[ReferenceLists](   [ID] [int] IDENTITY(1,1) NOT NULL,   [Name] [varchar](50) NOT NULL,   [Constant] [varchar](25) NULL,   [CreatedDate] [datetime] NULL CONSTRAINT [DF_ReferenceLists_CreatedDate]  DEFAULT (getdate()),   [ModifiedDate] [datetime] NULL CONSTRAINT [DF_ReferenceLists_ModifiedDate]  DEFAULT (getdate()),   CONSTRAINT [PK_ReferenceLists] PRIMARY KEY CLUSTERED ([ID] ASC) ON [PRIMARY],   CONSTRAINT [UIX_ReferenceList_Constant] UNIQUE NONCLUSTERED ([Constant] ASC) ON [PRIMARY]) ON [PRIMARY]GOCREATE TABLE [dbo].[ReferenceValues](   [ID] [int] IDENTITY(1,1) NOT NULL,   [ListID] [int] NOT NULL,   [Name] [varchar](50) NOT NULL,   [Abbreviation] [varchar](50) NULL,   [Constant] [varchar](25) NULL,   [Description] [varchar](255) NULL,   [Obsolete] [bit] NULL CONSTRAINT [DF_ReferenceValues_Obsolete]  DEFAULT ((0)),   [CreatedDate] [datetime] NULL CONSTRAINT [DF_ReferenceValues_CreatedDate]  DEFAULT (getdate()),   [ModifiedDate] [datetime] NULL CONSTRAINT [DF_ReferenceValues_ModifiedDate]  DEFAULT (getdate()),   CONSTRAINT [PK_ReferenceValues] PRIMARY KEY CLUSTERED ([ID] ASC) ON [PRIMARY]) ON [PRIMARY]GOCREATE TRIGGER [dbo].[trg_OnUpdateReferenceValue]ON [dbo].[ReferenceValues]AFTER UPDATEASBEGINSet NoCount On-- Update the ModifiedDate on any Rows where it wasn't specified explicitlyUpdate dbo.ReferenceValues Set ModifiedDate = GetDate()From dbo.ReferenceValues RInner Join inserted I ON (R.ID = I.ID)Where (I.ModifiedDate Is Null)Set NoCount OffENDGOALTER TABLE [dbo].[ReferenceValues]  WITH CHECK    ADD CONSTRAINT [FK_ReferenceLists_ReferenceValues] FOREIGN KEY([ListID])   REFERENCES [dbo].[ReferenceLists] ([ID])GO`

### Usage

Now, to make this easier on developers and me, I usually wrap views around these values for specific cases (although it could all be handled in code).

Let's say we want to store Address Types in the database. So we create an Address table and put AddressTypeID on it. Next we add a Foreign Key to the ReferenceValues table's ID column. We could also specify a Check Constraint that the value must only be one where the ListID = 1 ('ADDRESS_TYPE'). In order to maintain this list of values for AddressTypes, we create a nice little view for it.

Notice how the View doesn't expose the "ListID" column because it isn't necessary. This view should be used as a Table. So, the INSTEAD OF trigger looks up the ListID auto-magically (inserting it if neccessary).

`CREATE VIEW [dbo].[AddressTypes]WITH SCHEMABINDINGASSelect    ID, Name, Abbreviation, Constant,    Obsolete, CreatedDate, ModifiedDateFrom dbo.ReferenceValues WITH(NOLOCK)Where ListID IN(   Select ID From dbo.ReferenceLists WITH(NOLOCK) Where Constant = 'ADDRESS_TYPE')GOCREATE TRIGGER [dbo].[trg_OnUpdateAddressType]ON [dbo].[AddressTypes]INSTEAD OF INSERTASSet NoCount On-- Determine the ListIDDeclare @ListID IntSelect @ListID = ID From dbo.ReferenceLists Where (Constant = 'ADDRESS_TYPE')If (@ListID Is Null)   BEGIN   Insert Into dbo.ReferenceLists (Name, Constant) Values('Address Types', 'ADDRESS_TYPE')   Set @ListID = SCOPE_IDENTITY()   END-- Insert the ValuesInsert Into dbo.ReferenceValues   (ListID, Name, Abbreviation, Constant, Obsolete,    CreatedDate, ModifiedDate)Select @ListID, Name, Abbreviation, Constant, Obsolete,   IsNull(CreatedDate, GetDate()), IsNull(ModifiedDate, GetDate())From insertedSet NoCount OffGO`

You can also add other columns to the ReferenceLists table (ex. SequenceNo if you want to display values in a specific order).

JP-317675
SSC-Enthusiastic

Group: General Forum Members
Points: 137 Visits: 90
Doesn't this overly complicate what should be a simple design.

If you want to store address_types then create a table called address_types and store them in there. If you need a lookup table for phone number types then create a table called phone_types and store them in there. If you start mixing 'types' of things in the same table then you have changed the table from being an entity of one thing to being an entity of many things and make a joke of what a foreign key constraint is supposed to be used for.

What happens in future when you want to add more detailed information for an adress type? You cannot simply add a column and insert the data because your table does not only contain address_types. On top of that if you did decide to create a new address_type table then making all the modifications to your original design would be time consuming and complicated.

All foreign key constraints used in a mixed lookup table can never be relied upon to be 100% accurate. You can keep adding check constraints in every table that uses your one lookup table but this once again complicates what should be simple.

I prefer the design of many different lookup tables each unique and unambiguous in their purpose. It allows for foreign key constraint checks to be 100% accurate (what could be more important than accurate data) while having the flexibility to expand the tables columns as you see fit.

If there is a concern that the number of lookup tables will exponentially grow and you wont know where anything is....I guess that is a different topic.

-- JP