Elegantly handling 'enum-like' magic numbers in T-SQL code

Question

We run Dynamics GP, and anybody that's dealt with GP will be familiar with all of its "magic number" columns representing the various enum values inside the application. For example, a reporting query might look something like this:

...
WHERE sod.SOPTYPE = 2
 AND iv.VCTNMTHD = 3
 AND pod.POLNESTA IN (2, 3)

...which is about as far from self-documenting as possible.

At first I had the bright idea of creating a table called 'Enums', filling it with values, and making a scalar wrapper function so you could query like this:

...
WHERE sod.SOPTYPE = Enum('Sales Doc Type', 'Order')
 AND iv.VCTNMTHD = Enum('Valuation Method', 'Average Perpetual')
 AND pod.POLNESTA IN (Enum('PO Line State', 'Released'), Enum('PO Line State', 'Change Order'))

Of course, that sucked, because the optimizer couldn't see the underlying 'constant' value, and thus made some rather poor choices since it had to make cardinality guesses.

So it needs to be an actual constant value in the code, but I'm not sure if there are any nice features built into SSMS that would make this sort of thing easy. Could I (mis)use code snippets or templates in some way? If we could do some kind of lookup/insertion to make our code look something like this, that would be awesome:

...
WHERE sod.SOPTYPE = /*Order*/2
 AND iv.VCTNMTHD = /*Average Perpetual*/3
 AND pod.POLNESTA IN (/*Released*/2, /*Change Order*/3)

I'm also not opposed to checking out SSMS add-ins.

• S3S: Instead of the scalar function, have you tried simply joining to the table and adding a where clause for the string correlation?
• db2: @scsimon That does work better (reads and CPU are cut roughly in half), but it's still about double what I get with simple numeric constants directly in the script. (Not to mention all the extra code bloat with the additional joins.)
• Jacob H: Great Plains is an interesting application. Victoria Yudin's blog helps. There used to be a site called "Dynamics GP Table Reference" but it appears to be dead now. Maybe there is a replacement. Eventually you'll memorize them... :) Good luck on your quest!
• db2: @JacobH "Interesting" is definitely one way to describe it. We've got most of the tables we frequently touch memorized, but there are lots of little cryptic enum fields like this that are difficult to keep straight. (What was 11 in IV30300.DOCTYP again?...) Putting /*Inline Comments*/ into the code would help maintainability, and if I can also get automatic replacement when typing something like ValuationMethod:AveragePerpetual, then that would be totally sweet.
• Jacob H: I found this cheat sheet from the GP UG if it helps you. Maybe you could make this work with extended properties? Probably would be a significant effort that could be undone with a GP upgrade though... and I don't think it would be much faster than using @scsimon suggestion.

Answer 1

I would actually recommend against tying this lookup in any way to SSMS / SQLCMD -specific features. Doing so would require that the code could only ever be executed using just those two programs. You wouldn't even be able to put any such logic into a stored procedure, even if it was executed from SSMS or SQLCMD. You also can't use SQLCMD commands in SSRS, etc.

Some options (that are completely independent of the client tool used to execute them):

1. If SQL Server 2019 is an option, then it's possible that the new Scalar UDF Inlining feature might resolve the issue with your Enum('Sales Doc Type', 'Order') approach.
   

2. It's possible that an inline TVF might do the trick. You just might need to restructure those predicates in the WHERE clause to be INNER JOINs:

   FROM SOPxxxxx sod
   INNER JOIN dbo.Enum('Sales Doc Type', 'Order') sdt
    ON sod.SOPTYPE = sdt.Value
   

   Of course, the IN list is a little trickier as I think that might require a LEFT JOIN on each option and then a WHERE predicate to ensure that both of those did not result in a NULL:

   FROM dbo.POP10110 pod
   LEFT JOIN dbo.Enum('PO Line State', 'Released') pls_r
    ON pod.POLNESTA = pls_r.Value
   LEFT JOIN dbo.Enum('PO Line State', 'Change Order') pls_co
    ON pod.POLNESTA = pls_co.Value
   WHERE ( pls_r.Value IS NOT NULL
    OR pls_co.Value IS NOT NULL)
   

   OR, using this same iTVF, you might be able to do non-correlated subqueries that might be more readable / manageable:

   FROM SOPxxxxx sod
   WHERE sod.SOPTYPE = (SELECT sdt.Value FROM dbo.Enum('Sales Doc Type', 'Order') sdt)
   

   And the IN list is at least looks better:

   FROM dbo.POP10110 pod
   WHERE pod.POLNESTA IN (
    SELECT pls_r.Value
    FROM dbo.Enum('PO Line State', 'Released') pls_r
    UNION ALL
    SELECT pls_co.Value
    FROM dbo.Enum('PO Line State', 'Change Order') pls_co
    ) 
   

   Or perhaps (not sure what the query optimizer will prefer):

   FROM dbo.POP10110 pod
   WHERE pod.POLNESTA IN (
    (SELECT pls_r.Value FROM dbo.Enum('PO Line State', 'Released') pls_r),
    (SELECT pls_co.Value FROM dbo.Enum('PO Line State', 'Change Order') pls_co)
    ) 
   

3. If SQLCLR is an option, then SQLCLR scalar UDFs that do not do any data access and are marked as [SqlFunction(IsDeterministic = true)] are constant folded into the execution plan (the "Query Processing Architecture Guide" documentation currently states that SQLCLR functions cannot be folded, but that is provably wrong, so I suspect that the documentation was merely not updated when SQL Server 2012 added that ability; I have submitted a documentation update to fix that: Fix "Constant Folding" info in "Query Processing Architecture Guide").

   The trick here is to read the data from the table without doing so in the SQLCLR scalar UDF so as to not prevent it from being foldable. In order to accomplish this, do the following:

   1. Create a static readonly dictionary at the class level.
   2. Create a static constructor for the class that uses a regular, external SqlConnection to connect to the local instance. This will select from the table and populate the static dictionary. This constructor will ensure that the dictionary is always populated, even if the assembly gets unloaded due to memory pressure, DBCC FREESYSTEMCACHE, etc. Unfortunately, the internal "context" connection cannot be used here since there is no Sql Context when the static constructor is executed (but would be great if there was!).
   3. Mark the assembly as WITH PERMISSION_SET = EXTERNAL_ACCESS. No need to specify UNSAFE because the dictionary is "readonly". And even though it is "readonly", it can still have items added and/or removed from it. This should not pose any problems as it's a single use collection that is always the same for all users.
   4. Decorate the static method that is exposed to T-SQL to do the lookup with:

      [SqlFunction(IsDeterministic = true)]
      

      No need to specify DataAccess or SystemDataAccess properties as those are set to None by default.
      

   This method will allow you to implement the Enum() approach that you had started doing. And, since you are working with a 3rd party app, you can keep things "clean" by installing the assembly into a separate database (and same would go for report procs, etc, I suppose). I have tested and verified that it still does the constant folding.
   

4. OR (this is really option 3b): Given that this is a 3rd party app, IF it is safe to assume that the lookup / enum values won't be changing much, if ever, then you don't really need to read them from a table. You can simple have the entire dictionary hard-coded in the .NET code. In this approach, you could then keep the assembly marked as PERMISSION_SET = SAFE as there would be no need for that external SqlConnection back to the current instance.

Answer 2

Within SSMS you can enable SQLCMD Mode and do things along the lines of the following:

-- Enable SQLCMD Mode (ALT+Q+M)
:SetVar cSomeNumber 42
SELECT $(cSomeNumber)

If you save the scripts to a file, these will operate the same when when you use with... wait for it... SQLCMD.exe

The ":SetVar" and "$(cSomeNumber)" are processed before the sql is sent to the engine, however. This means that this cannot be used in a different client (e.g. ADO.Net) and if you create any DBOs it will record the interperted value. So this:

-- Enable SQL Command Mode (ALT+Q+M)
:SetVar cSomeNumber 42
CREATE View Test AS SELECT $(cSomeNumber) as SomeNumber

will yield this:

-- Enable SQL Command Mode (ALT+Q+M)
CREATE View [dbo].[Test] AS SELECT 42 as SomeNumber

Which limits the over all utility.

• Andriy M: Do I understand correctly that your idea of using SQLCMD variables to document those obscure constant values also involves choosing a more or less self-descriptive name for each constant (like e.g. :SetVar sdtOrder 2 or :SetVar polsChangeOrder 3, using the example given in the question)? Since the variables are assigned explicitly in the script, you are basically suggesting using them as named constants, correct?
• Graham: Yes, if you are saving the associated queries into script files (.sql). You could even extend the variable to include a comment: :SETVAR cTowels "42 /* Towels */"