Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Enums in SQL Server Expand / Collapse
Author
Message
Posted Thursday, August 9, 2007 11:48 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Thursday, March 13, 2014 10:40 AM
Points: 572, 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
Post #389401
Posted Monday, August 27, 2007 9:38 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, February 27, 2008 8:37 AM
Points: 1, 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
Post #394189
Posted Monday, August 27, 2007 11:13 PM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, November 18, 2014 4:21 AM
Points: 229, Visits: 170

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



--------------------------------
Post #394199
Posted Monday, August 27, 2007 11:32 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, February 18, 2013 11:59 PM
Points: 53, 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

 




Post #394202
Posted Tuesday, August 28, 2007 6:46 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: 2 days ago @ 6:31 PM
Points: 4,435, Visits: 6,336
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
Post #394265
Posted Tuesday, August 28, 2007 6:58 AM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Thursday, November 13, 2008 9:13 AM
Points: 499, Visits: 76
Just use a comment next to the int based column. It's not really too hard.
Post #394269
Posted Tuesday, August 28, 2007 7:28 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, May 12, 2014 1:59 PM
Points: 155, Visits: 66

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
Post #394281
Posted Tuesday, August 28, 2007 7:41 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: 2 days ago @ 6:31 PM
Points: 4,435, Visits: 6,336
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
Post #394293
Posted Tuesday, August 28, 2007 8:04 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: 2 days ago @ 2:55 AM
Points: 1,410, Visits: 1,016

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




Post #394308
Posted Tuesday, August 28, 2007 8:24 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, November 18, 2014 4:21 AM
Points: 229, Visits: 170

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



--------------------------------
Post #394318
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse