SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Enums in SQL Server


Enums in SQL Server

Author
Message
Simon Sabin
Simon Sabin
Hall of Fame
Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)

Group: General Forum Members
Points: 3004 Visits: 108
Comments posted here are about the content posted at http://www.sqlservercentral.com/columnists/ssabin/3174.asp


Simon Sabin
SQL Server MVP

http://sqlblogcasts.com/blogs/simons
David Tittle
David Tittle
Valued Member
Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)

Group: General Forum Members
Points: 55 Visits: 4
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
Jeswanth Jaishanker
Jeswanth Jaishanker
SSC Eights!
SSC Eights! (812 reputation)SSC Eights! (812 reputation)SSC Eights! (812 reputation)SSC Eights! (812 reputation)SSC Eights! (812 reputation)SSC Eights! (812 reputation)SSC Eights! (812 reputation)SSC Eights! (812 reputation)

Group: General Forum Members
Points: 812 Visits: 172

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



--------------------------------
jonespm
jonespm
SSC-Addicted
SSC-Addicted (475 reputation)SSC-Addicted (475 reputation)SSC-Addicted (475 reputation)SSC-Addicted (475 reputation)SSC-Addicted (475 reputation)SSC-Addicted (475 reputation)SSC-Addicted (475 reputation)SSC-Addicted (475 reputation)

Group: General Forum Members
Points: 475 Visits: 74

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





TheSQLGuru
TheSQLGuru
SSC Guru
SSC Guru (57K reputation)SSC Guru (57K reputation)SSC Guru (57K reputation)SSC Guru (57K reputation)SSC Guru (57K reputation)SSC Guru (57K reputation)SSC Guru (57K reputation)SSC Guru (57K reputation)

Group: General Forum Members
Points: 57803 Visits: 8824
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.

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Vic Kirkpatrick-173212
Vic Kirkpatrick-173212
SSC Eights!
SSC Eights! (981 reputation)SSC Eights! (981 reputation)SSC Eights! (981 reputation)SSC Eights! (981 reputation)SSC Eights! (981 reputation)SSC Eights! (981 reputation)SSC Eights! (981 reputation)SSC Eights! (981 reputation)

Group: General Forum Members
Points: 981 Visits: 76
Just use a comment next to the int based column. It's not really too hard.
Bill Wehnert
Bill Wehnert
Mr or Mrs. 500
Mr or Mrs. 500 (581 reputation)Mr or Mrs. 500 (581 reputation)Mr or Mrs. 500 (581 reputation)Mr or Mrs. 500 (581 reputation)Mr or Mrs. 500 (581 reputation)Mr or Mrs. 500 (581 reputation)Mr or Mrs. 500 (581 reputation)Mr or Mrs. 500 (581 reputation)

Group: General Forum Members
Points: 581 Visits: 67

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



Ad maiorem Dei gloriam
TheSQLGuru
TheSQLGuru
SSC Guru
SSC Guru (57K reputation)SSC Guru (57K reputation)SSC Guru (57K reputation)SSC Guru (57K reputation)SSC Guru (57K reputation)SSC Guru (57K reputation)SSC Guru (57K reputation)SSC Guru (57K reputation)

Group: General Forum Members
Points: 57803 Visits: 8824
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!! :-))

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
henrik staun poulsen
henrik staun poulsen
Hall of Fame
Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)

Group: General Forum Members
Points: 3280 Visits: 1245

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





Jeswanth Jaishanker
Jeswanth Jaishanker
SSC Eights!
SSC Eights! (812 reputation)SSC Eights! (812 reputation)SSC Eights! (812 reputation)SSC Eights! (812 reputation)SSC Eights! (812 reputation)SSC Eights! (812 reputation)SSC Eights! (812 reputation)SSC Eights! (812 reputation)

Group: General Forum Members
Points: 812 Visits: 172

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



--------------------------------
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum







































































































































































SQLServerCentral


Search