SQL: Case, Switch and Data Models

I was trying to remember how to do a case statement in an MS-Access Query. Having worked mostly in SQL Server the last while I forgot that MS-Access uses the switch keyword instead of case. The syntax is very similar.

A case or switch statement in SQL allows you to translate a specific value of a field into another value. It is often used to translate something meaningless like an enum or numeric identifier into something readable. It is often used in views or reports.

In the following example the MSAccess switch statement is used to translate an enumeration field into readable text:

 SELECT switch([T_Member.FamilyTypeId]=1,"parent",[T_Member.FamilyTypeId]=2,"dependent") AS SomeFieldName FROM SomeTable

SQL Server Books Online (2005) shows a simple example of the case statement:

USE AdventureWorks2008R2;
GO
SELECT   ProductNumber, Category =
      CASE ProductLine
         WHEN 'R' THEN 'Road'
         WHEN 'M' THEN 'Mountain'
         WHEN 'T' THEN 'Touring'
         WHEN 'S' THEN 'Other sale items'
         ELSE 'Not for sale'
      END,
   Name
FROM Production.Product
ORDER BY ProductNumber;
GO

Now that I've shown you how to do these statements properly, I'm going to argue why you should never use them.

Almost always a case statement means the data model is incomplete. A case statement basically inserts ad hoc lookup tables randomly though-out your SQL statements. As good designers will say, hard-coded text values or numeric literals in any type of code are a maintenance nightmare. How hard is it to extend the data model to provide text definitions of the things you are translating with a case statement?

I feel the same way about the use of case statements in programming languages. The logic is static and cannot be changed except through rebuilding and redeploying. Adding an additional case label to a statement is out of reach for the system administrator, whether SQL or code.

Case or switch fields are not editable by the user. In some cases views that use them can make the entire row not editable. But almost all GUIs support editing of foreign keys through drop downs boxes.

I would argue that case and switch statements should never be used in SQL statements, and focus instead on complete data models, with proper relationships.