Allow literals and read only table data to be represented as enums - by Simon Sabin

Status : 

 


237
9
Sign in
to vote
ID 254293 Comments
Status Active Workarounds
Type Suggestion Repros 80
Opened 1/27/2007 3:56:51 PM
Access Restriction Public

Description

It would be really useful to represent system values to be used in code as constants rather than having to hard code integer values. 

This means that lookup tables can use nice tinyints as the surrogate PKs but when referenced in code the code can use the textual representation.

Essentially data driven enums.

I am happy for the restriction that this only applies to read only data.

an example where this wouldbe useful is the system message types/contracts in service broker. Currently you have to have strings like 'http://schemas.Microsoft.com/SQL/ServiceBroker/DialogTimer' in your code.  which means you are comparing against 59 byte string rather than a 4 byte integer.
Sign in to post a comment.
Posted by ErikEckhardt on 9/15/2010 at 6:04 PM
Wow! This is just like one of my own ideas. I would like to add table-data-driven enums, where the value has to be looked up in a table bit it is embedded in the cached execution plan. Until the plan expires, the value will not be looked up in the table. I don't care if the value in the table changes, I'll handle that problem myself by stopping use of the constant in question long enough for the plan to expire before I make any update.

Given table QueueStatus with columns QueueStatusID int, Descr nvarchar(100):

SELECT * FROM Queue WHERE QueueStatusID IN 1, 2 -- yuck
SELECT * FROM Queue WHERE QueueStatusID IN (SELECT QueueStatusID FROM QueueStatus WHERE Descr IN 'Queued', 'Processing') -- yuckier

CREATE ENUM QueueStatusEnum REFERENCES dbo.QueueStatus BIND Descr TO QueueStatusID
SELECT * FROM Queue WHERE QueueStatusID IN QueueStatusEnum(Queued, Processing) -- wonderful
Posted by CheetahAfoot on 9/3/2010 at 1:34 AM
I want the enum to make Result/ErrorCodes enum, and return it's value from SP as result (return Result.Success;) and I want be able to quick change result number in one place and get it worked over all code. This feature in a waiting state since 2007. Almost 4 years.
Posted by Bertie on 8/30/2007 at 12:23 PM
I like it.
For my part, the most practical (and hopefully possible) way to do this is to implement a new Type, which would have multiple dimensions - a scalar value, a text value, and some kind of global identification of itself within the scope of SQL Types.
Then, of course the implication is that SQL Server will begin to take the shape and logic of an Object Oriented data management system.
Hurrah, anyone?
Posted by TommCatt on 8/29/2007 at 10:46 AM
Possible format:
create enum datatype Payment (
Visa [= 1],
MasterCard [= 5],
... [= n]
[,default = Visa]
)

This would allow specific values with the optional "[= n]" qualifiers or the default of sequential numbering.

Creating a table with a Payment column implicitly creates a check constraint in the form of "value between 1 and n" (for sequential) or "value in (1, 5, ..., n) for non-sequential values. Also a default value if one is defined in the definition.

Being able to write WHERE clauses like "where PaymentType = Payment.MasterCard" would be a great boon for self-documenting code.
Posted by Bill Parrott on 8/28/2007 at 9:27 AM
While MySQL has had an enumerated column type for some time, I have never really liked the way it has been implemented. The data that is put in the enumerated column is not easily viewable (or changeable). In object oriented languages, a common practice is to create a class that contains the enumerated type data and then use that class in defining other classes.

What I would like to see is a user data type that ties back to a table (for easy readability and maintainability). This user type could also encapsulate enumerated functionality for the column that it is used in. It could also provide a method for supplying drop down values as part of its core functionality. The ability to directly reference the class in a query would be a huge bonus.
Posted by Lee Keel on 8/28/2007 at 6:53 AM
I do agree that this would be a great idea, but I think the problem is a lot larger than just pk column and description column. What if you want to restrict values for enumeration based on value from another column.

For example: I have an automobile table. This table has Type column that has possible enumeration values of: truck, car, motorcycle. I have another column that is Make and it should be filtered based on type:

Type                    Possible Enum Values
Motorcycle         Kawaski, Honda, etc
Truck                 Dodge, Chevy, Ford, etc
Car                     Porsche, BMW, Lamborghini, etc

I know this example may not the best in the world, but I have seen many cases in my years of application design where the valid values for one column needs to be filtered based on another column. I do like the idea of read-only tables, but also agree with 'Bill from Racine' that these values would have to be somehow restricted to comparing only values of their own enumeration.

Best Regards,
Lee
Posted by Bill from Racine on 8/28/2007 at 6:24 AM
I think enums would be a great feature to add to SQL. I would think they would work best like a custom datatype.

Something like this:

exec sp_addenum 'Visa', 0, 'PaymentType'
exce sp_addenum 'MasterCard', 1, 'PaymentType'
exec sp_addenum 'Amex', 5, 'PaymentType'

Then you would use it in a table as:

create table Orders (
OrderID bigint not null identity(1,1),
CustomerID bigint not null,
PaymentMethod PaymentType not null
)

The underlying datatype should be a bigint - allowable to be compared to other integer values so that they could be passed in from other sources.

Then in queries like suggested:

select * from Orders where PaymentMethod in (PaymentType.Visa, PaymentType.MasterCard)

The question would be is something like this allowed?

select * from Orders where PaymentMethod = Shipping.UPS

I think it should be prohibited. Either a constant (PaymentMethod = 1) or a valid enum type (PaymentMethod = PaymentType.Visa) or a variable (PaymentMethod = @PaymentType) should be accepted.

This would hopefully at least prevent people from writing the PaymentMethod = Shipping.UPS code.
Posted by hecUngravity on 8/28/2007 at 4:43 AM
I read once a book on mysql and i think the allowed something similar to enums. I found it somewhat nice. No i use a lot of enums in my software, i have missed that feature on sqlserver quite a lot. The question is, how to sincronize them?
Posted by ashishmgupta on 8/28/2007 at 2:10 AM
This makes perfect sense. We have guids for status statuses like Completed,InProcess etc for records in the database. So,for this we either have a master lookup table for the status or we need to hardcode status id to get the records.Enums or similar in the database would definitely be a good feature.

Thanks,
Ashish
Posted by Krayol on 8/28/2007 at 1:22 AM
We use a short piece of .net reflection code to autogenerate enum lookup tables when changed in code. While this helps reporting, this suggestion would be a much better solution.
Posted by DataDrill on 8/28/2007 at 12:22 AM
In my view, the enum would be an extension of an integer data type woth the scope of a database. The enum could be used across any table in place of an integer. I could use the same operations as an integer. The difference would be that when an enumerated token was encountered (ie BookType = Fiction), SQL Server would convert the token to its integer representation. So BookType= Fiction, BookType=1, Fiction=1 would all return true. Naming for multple enums in a database would follow current "." naming convention.
Posted by Mark Duregon on 8/27/2007 at 11:10 PM
Additionally a third column for documentation. This column could be easily used by code generation tools to document the code created. Additionally if intellisense were to finally make it to the SQL Management tools, this could be used to provide information in that context as well.
Posted by jaypatel on 8/27/2007 at 9:35 PM
Some type of Enum capability would certainly make development much easier. Having such a feature may also allow developers to find issues at development time rather than runtime.
Posted by Chris Vann on 8/27/2007 at 9:05 PM
I agree with the call for enums, but I do not believe it should be implemented as a secondary lookup table table, or at least, the lookup table should be handled by the system, and should not require the developer or DBA to create it up front.. I think any implementation should be functionally equivalent to the MySQL column data type. It works and is widely adopted in the MySQL crowd, and would provide better portability to/from other systems.
Posted by rgrus on 8/9/2007 at 10:55 AM
Ageed, this would make dev work much simpler, not to mention maintaining the system afterwards.
Posted by Microsoft on 1/29/2007 at 11:51 AM
Thanks for the feedback. There are a few alternatives to enable this, we'll consider it for future release.

- Christian Kleinerman