Thanks for the feedback. There are a few alternatives to enable this, we'll consider it for future release.

- Christian Kleinerman

Ageed, this would make dev work much simpler, not to mention maintaining the system afterwards.

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.

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.

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.

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.

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.

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

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?

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.

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

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.

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.

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?

-Add an "enum" column to the bridge table.
-LEFT(<column>,12) = '/DialogTimer'.
-An "Enums" UDF containing all desired enums . . . <column> = fnEnums('ServiceBroker','DialogTimer').
-SQL 2005 runs .NET code now so it already has enums.

CREATE TABLE enum(scope sysname, name sysname, value int, description nvarchar(250),
CONSTRAINT PK_enum PRIMARY KEY(scope,name),
CONSTRAINT UX_enumvalue UNIQUE (scope,value)
)

CREATE FUNCTION enumNameFromValue(@Scope sysname,@Value int) RETURNS sysname AS
BEGIN
    DECLARE @Result sysname
    SELECT @Result = name from enum where scope=@Scope and value=@Value
    RETURN @Result
END

in my (very brief) testing on a specific table where this might help me:
SELECT Job.*, enumNameFromValue('JobStatus', Job.JobStatusID)
FROM Job

has a slightly lower cost than

SELECT Job.*, enum.name
FROM Job INNER JOIN enum ON enum.value = Job.JobStatusID AND enum.Scope='JobStatus'

which also has the same cost as a query joined to a non-generic lookup table (query costs were 32%, 34%, 34% respectively)

This would suggest that contrary to what I had thought, there would be a performance benefit to an integrated 'enum' concept,
assuming that an optimised implementation would be even better.

haven't tested reverse case (getting a number from the name), since I can't actually think of a test case that's non-trivial.

2007-01-27 par sms