SQLServerCentral / Discuss Content Posted by Simon Sabin / Article Discussions / Article Discussions by Author / Enums in SQL Server / Latest Posts

RE: Enums in SQL Server

Tom Wilson — Tue, 28 Aug 2007 21:41:00 GMT

In the SQL 2005 world, I've wondered (but not implemented) code using the SQLCMD metacommand :setvar and :r. One could use these like #define and #include in C++.

File1.sql#setvar enumMale M

#setvar enumFemale F

File2.sql:

:r file1.sql

select Name from Person where Sex = '$(enumMale)'

The above is contrived and simplistic, but you get the idea - rather than going for enum, just go for symbolic substitution.

RE: Enums in SQL Server

TheSQLGuru — Tue, 28 Aug 2007 17:43:00 GMT

It is obviously physically impossible to process many if not most 'natural keys' as efficiently as surrogate ones, therefore your pining about this 'evil' is to no avail. At least you ACKNOWLEDGE the need for surrogate keys - something Celko in his ivory tower still doesn't do. Espousing a 23 character key when a tinyint will suffice as a surrogate is simply ridiculous.

RE: Enums in SQL Server

Merrill Aldrich — Tue, 28 Aug 2007 17:16:00 GMT

This, like many articles, seems written by a person who hasn't read much of the existing thought on the topic, and is therefore blindly bumping into old issues that have already been covered in much more detail elsewhere. Read Celko, read Date, read about all the reasons for the surrogate key optimization, then come back and write something if there's more to say than has been said already. FWIW: surrogate keys are both evil, and necessary for scalability. Unless/until the database vendors come up with an internal engine optimization that avoids this problem (the issue of the width of keys / the cost to compare them / the cost of On Update Cascade), an issue which seems still to be a blind spot for them, we will have surrogate keys with all the extra joins, logic problems and headaches they create.

RE: Enums in SQL Server

Michael Valentine Jones — Tue, 28 Aug 2007 14:15:00 GMT

I don’t see any point to the idea of ENUMS; basically, it solves a problem that doesn’t exist.

There is nothing wrong with any of the techniques in the code below, other than the author not liking to write it.

I will say that you should never use a hard coded ID value; that defeats the whole purpose of a surrogate key by hard-coding the meaning of a particular ID.

--Method 1

select

Order.OrderId,

Order.OrderDate,

Order.PaymentTypeId

from

Order

join

PaymentType

on Order.PaymentTypeId = PaymentType.PaymentTypeId

where

PaymentTypeDescription = 'Invoice'

--Method 2

select

Order.OrderId,

Order.OrderDate,

Order.PaymentTypeId

from

Order

where

Order.PaymentTypeId in (

select

PaymentType.PaymentTypeId

from

PaymentType

where

PaymentTypeDescription = 'Invoice'

)

--Method 3

declare @PaymentTypeId int

select

@PaymentTypeId = PaymentType.PaymentTypeId

from

PaymentType

where

PaymentTypeDescription = 'Invoice'

select

Order.OrderId,

Order.OrderDate,

Order.PaymentTypeId

from

Order

where

Order.PaymentTypeId = @PaymentTypeId

RE: Enums in SQL Server

cs_troyk — Tue, 28 Aug 2007 13:39:00 GMT

Although an admirable effort, the author misses the mark.

I would urge readers to, instead, push the folks at Microsoft to implement proper support for relational domains. This is the superior and theoretically sound solution.

TroyK

RE: Enums in SQL Server

Ian Ornstein — Tue, 28 Aug 2007 12:26:00 GMT

Please fix the print style sheet so the text is not truncated.

RE: Enums in SQL Server

TheSQLGuru — Tue, 28 Aug 2007 12:13:00 GMT

1) >>Comments are not necessarily free. They can be costly at maintenance time. There are many occasions when the code is changed and the comments are not...especially when the changes are not done by the original developer. An enum forces the code to use a value from a limited set and still allows for good readability.2) >>An enum does cost more at compile time but is translated to an integer value at runtime. I would think that if an enum was used in a stored procedure that is compiled once, the performance penalty of translating a character string to an integer value at compile time would be negligable.3) >>An enumeration also acts as another integrity test through type checking. This, IMHO, is more valuable than the small performance gains from using any type of integer.**********************for Item 1 above, comments are most useful when they are made by OTHER THAN the original developer. In any case, if developers don't adhere to good commenting then they need to be upgraded.for Item 2 above, which particular method of enumeration are you espousing here. There have been several given in this thread and on the original web posting.for Item 3 above, your comment is invalid in so much as you can type the wrong value in just like you can type the wrong value in when you do the lookup to get it.

RE: Enums in SQL Server

Yelena Varshal — Tue, 28 Aug 2007 10:12:00 GMT

Isn't it called Dictionary Objects?

http://www.microsoft.com/technet/scriptcenter/scripts/misc/diction/default.mspx?mfr=true

Dictionary Objects

RE: Enums in SQL Server

James Rochez — Tue, 28 Aug 2007 09:28:00 GMT

Comments are not necessarily free. They can be costly at maintenance time. There are many occasions when the code is changed and the comments are not...especially when the changes are not done by the original developer. An enum forces the code to use a value from a limited set and still allows for good readability.An enum does cost more at compile time but is translated to an integer value at runtime. I would think that if an enum was used in a stored procedure that is compiled once, the performance penalty of translating a character string to an integer value at compile time would be negligable.An enumeration also acts as another integrity test through type checking. This, IMHO, is more valuable than the small performance gains from using any type of integer.

RE: Enums in SQL Server

TheSQLGuru — Tue, 28 Aug 2007 08:31:00 GMT

IIRC, this is one of the worst things you can do with a UDF. It essentially equates to RBAR (Row By Agonizing Row) processing under the covers - as in a lookup for each row streaming through the resultset.

RE: Enums in SQL Server

Jeswanth Jaishanker — Tue, 28 Aug 2007 08:24:00 GMT

the stress should be on performance and related topics than readability. for readability use comments in code. something like

SELECT fld1,fld2 FROM Orders where PaymentType = 1 -- VISA

Regards

Jeswanth

RE: Enums in SQL Server

henrik staun poulsen — Tue, 28 Aug 2007 08:04:00 GMT

I have a small "Attribute-Value" table, and a UDF to go with it.

Then I can do SELECT * FROM Orders where PaymentType = dbo.fn_SystemInfo('VISA'). Unfortunately this results in a convertion to/from varchar which may not be efficient. But it is easy on the eye to read the code.

Your suggestion would help a lot.

Best regards

Henrik Staun Poulsen

RE: Enums in SQL Server

TheSQLGuru — Tue, 28 Aug 2007 07:41:00 GMT

Again a bad example from a performance perspective.1) Using a bigint vice tinyint in this case requires 7 extra bytes of storage and 7 extra bytes to chew through on row accessed. And yes, those 7 bytes DO make a difference!2) Prohibiting improper enum usage would require under-the-covers system validation, also slowing performance for every query that references an enum. Once again a developer 'convenience' equals reduced throughput. Consider also what happens if you change an enum or change which enum a table references. Microsoft has for many years gotten a bad name in the server business (especially database) for sub-optimal performance. A lot of that came because they made things REALLY, REALLY EASY for developers. RAD this, point-and-click program that. Like parameter refresh, etc. But easy for developers quite often turns out to be a performance DOG when any reasonable amount of data is thrown at the application. I guess I shouldn't complain too vigorously however, since I make a comfortable living fixing other people's bad designs, bad data structures and bad code!! :-))

RE: Enums in SQL Server

Bill Wehnert — Tue, 28 Aug 2007 07:28:00 GMT

This is what I posted on the MS forum for SQL Server in response to this column:

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.UPSI 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.

RE: Enums in SQL Server

Vic Kirkpatrick-173212 — Tue, 28 Aug 2007 06:58:00 GMT

Just use a comment next to the int based column. It's not really too hard.

RE: Enums in SQL Server

TheSQLGuru — Tue, 28 Aug 2007 06:46:00 GMT

I will go on record as strongly disagreeing with the stuff espoused by the Enums article (and even more strongly with using CLR to implement them). Comments cost nothing to implement. Using a (var)CHAR ANYTHING is going to be less efficient (significantly as the length increases) than using a tiny/small/int as a surrogate key. I have a client that uses varchar(20)s for a lot of CLUSTERED PKs and their performance is horrid. Indexes are very inefficient too.

RE: Enums in SQL Server

jonespm — Mon, 27 Aug 2007 23:32:00 GMT

Yep - we have been down this path and have actually achieved it using real .Net enums (as used in our app) within SQL Serevr 2005.

We use an excellent, i.e. this is the best thing since sliced bread and cold beer!, product called StrataFrame and the use of enums within SQL Server 2005 was put to their (again excellent) support team and they came back with the following:

http://forum.strataframe.net/Topic10664-14-1.aspx

It took a little bit of messing around but, in the end, it works like a charm.

Cheers, Peter

RE: Enums in SQL Server

Jeswanth Jaishanker — Mon, 27 Aug 2007 23:13:00 GMT

Hi,

i feel instead of bringing one more join to sql its better to declare some variables inside the stored procedure and use it instead of bringing the enum concept forcibly in sql server.

not sure how many will agree to this. because i feel we should not sacrifice performance to readability.

Jeswanth

RE: Enums in SQL Server

David Tittle — Mon, 27 Aug 2007 21:38:00 GMT

Your idea is an excellent one and should be added. However, it is also one that you can accomplish right now using Sql.CLR and .Net (either VB or C#).I regularly use my C# enums exposed in a CLR class as PK/ID columms for tables.Good day,David

Enums in SQL Server

Simon Sabin — Thu, 09 Aug 2007 11:48:00 GMT

Comments posted here are about the content posted at http://www.sqlservercentral.com/columnists/ssabin/3174.asp