Click here to monitor SSC
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
SSChasing Mays
SSChasing Mays (612 reputation)SSChasing Mays (612 reputation)SSChasing Mays (612 reputation)SSChasing Mays (612 reputation)SSChasing Mays (612 reputation)SSChasing Mays (612 reputation)SSChasing Mays (612 reputation)SSChasing Mays (612 reputation)

Group: General Forum Members
Points: 612 Visits: 107
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
Forum Newbie
Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

Group: General Forum Members
Points: 5 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 Veteran
SSC Veteran (254 reputation)SSC Veteran (254 reputation)SSC Veteran (254 reputation)SSC Veteran (254 reputation)SSC Veteran (254 reputation)SSC Veteran (254 reputation)SSC Veteran (254 reputation)SSC Veteran (254 reputation)

Group: General Forum Members
Points: 254 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
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: 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
SSCertifiable
SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)

Group: General Forum Members
Points: 7383 Visits: 8407
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 at GMail
Vic Kirkpatrick-173212
Vic Kirkpatrick-173212
Mr or Mrs. 500
Mr or Mrs. 500 (511 reputation)Mr or Mrs. 500 (511 reputation)Mr or Mrs. 500 (511 reputation)Mr or Mrs. 500 (511 reputation)Mr or Mrs. 500 (511 reputation)Mr or Mrs. 500 (511 reputation)Mr or Mrs. 500 (511 reputation)Mr or Mrs. 500 (511 reputation)

Group: General Forum Members
Points: 511 Visits: 76
Just use a comment next to the int based column. It's not really too hard.
Bill Wehnert
Bill Wehnert
SSC-Enthusiastic
SSC-Enthusiastic (177 reputation)SSC-Enthusiastic (177 reputation)SSC-Enthusiastic (177 reputation)SSC-Enthusiastic (177 reputation)SSC-Enthusiastic (177 reputation)SSC-Enthusiastic (177 reputation)SSC-Enthusiastic (177 reputation)SSC-Enthusiastic (177 reputation)

Group: General Forum Members
Points: 177 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
SSCertifiable
SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)

Group: General Forum Members
Points: 7383 Visits: 8407
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 at GMail
henrik staun poulsen
henrik staun poulsen
SSCommitted
SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)

Group: General Forum Members
Points: 1880 Visits: 1212

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 Veteran
SSC Veteran (254 reputation)SSC Veteran (254 reputation)SSC Veteran (254 reputation)SSC Veteran (254 reputation)SSC Veteran (254 reputation)SSC Veteran (254 reputation)SSC Veteran (254 reputation)SSC Veteran (254 reputation)

Group: General Forum Members
Points: 254 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