I am trying to complete an assignment for my CIS class and I am required to include a data type called ENUM. I know that this stands for enumerated lists, but I am not sure when would be an appropriate time to use ENUM. A couple of the examples given in the book I am using were like a list of the continents. Could I use this to describe a list of departments for a workplace?
SQL – Uses of ENUM Data Type
enum
Related Solutions
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):
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.
It's possible that an inline TVF might do the trick. You just might need to restructure those predicates in the
WHERE
clause to beINNER JOIN
s: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 aLEFT JOIN
on each option and then aWHERE
predicate to ensure that both of those did not result in aNULL
: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) )
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:
- Create a static readonly dictionary at the class level.
- 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!). - Mark the assembly as
WITH PERMISSION_SET = EXTERNAL_ACCESS
. No need to specifyUNSAFE
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. - Decorate the static method that is exposed to T-SQL to do the lookup with:
No need to specify[SqlFunction(IsDeterministic = true)]
DataAccess
orSystemDataAccess
properties as those are set toNone
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.
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 externalSqlConnection
back to the current instance.
You have forgotten to show us the hint you got:
ERROR: unsafe use of new value "DONE_BY_PASSED" of enum type activity_state
HINT: New enum values must be committed before they can be used.
You also forgot to mention that you ran the ALTER TYPE
and the ALTER TABLE
statements in the same transaction.
Like the hint says, you have to commit the ALTER TYPE
before you can use the new enum value.
Best Answer
ENUM datatype can be seen as a "human-readable" numeric value, commonly used for convenience and data storage efficiency.
Let's take your example with a table listing the countries and their respective continents.
Using VARCHAR
Using SMALLINT
For better storage efficiency you'd better use SMALLINT (2 bytes) instead of VARCHAR (generally 1 byte + the Length of the string) for the continent field:
Using ENUM
This is where ENUM makes sense:
With ENUM you get same storage efficiency (or even better) than SMALLINT, but the ease of use of a VARCHAR data type.