May 1, 2008 at 6:24 am
I have an int column, which effectively represents an enumeration and so I'd like to be able to create some constants so that rather than litter my store-procedures with magic numbers I can use a constant - just in case we change the values of the enums or add some more etc
The best way I have found to abstract the value to date is to create Uder-Defined Functions returning a fixed int value, for example [dbo].[UserType_Eployee]() and [dbo].[UserType_Cstomer](), where employee is 1 and customer is 2 (contrived example, but you hopefully get what I mean).
In SQL I can then write something like:
SELECT
...
FROM
[dbo.].[User] AS U
WHERE
U.[Type] = [dbo].[UserType_Employee]();
This works, but I'd rather avoid the overhead of a function call - I'd like to be able to define some constants, a bit like @@Identity etc
Is this possible, and what would be the likely cost of calling a function that returns a fixed int value vs using a magic int in the SQL?
Thanks
May 1, 2008 at 6:49 am
If I understand the question, why don't you just maintain this data in a table? Even if it's only one or two rows, you can join against it when you need it and the cost will be very minimal. Maintaining it will be very easy and won't require the least bit of recoding. I'd do that before I'd do the function, although a table valued function of this size would work well enough, so you could do that.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
May 1, 2008 at 6:53 am
I agree with Grant. Store them in a table and join to it.
UDF in Where will slow your code down. Even if it's by a small amount, it's still slowed down more than a join to a Constants table.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
May 1, 2008 at 7:33 am
Are you prescribing a single table for all by database wide constants, or a table per type/enum?
I have given a simple example below of what I believe you are suggesting (assuming a single Constants table) - which makes for a considerably larger piece of SQL which IMO is more complex...
I am looking for a performant solution, but one that doesn't overly sacrifice simplicity for a few miliseconds.
If the example below is in line with your suggestion, then couple of aspects I potentially dislike are:
1) The values rely on data rather than constants - creating an empty database won't work without loading seed data for all the enums. I can live with this if performance increase is measurable.
2) Now I have littlered my SQL with magic strings - which could have typos in them - whereas a UDF would prevent compilation of the SP if I got the name wrong...so I lose something akin to 'type safety'.
Does anyone know of the likely performance cost/gain for using INNER JOIN to Constants table vs a call to a 'constant' and therefore deterministic UDF?
[Get all the 'Commercial, Group' members]
...
FROM
[dbo].[TableA] AS A
INNER JOIN
[dbo].[TableB] AS B
ON
A.[Id] = B.[OwnerId]
AND
B.[MemberType] = [Constant].[MemberType_Group]()
WHERE
A.[CategoryType] = [Constant].[CategoryType_Commercial]()
---
Possible replacement:
[Get all the 'Commercial, Group' members]
...
FROM
[dbo].[TableA] AS A
INNER JOIN
[dbo].[Constants] AS C1
ON
A.[CategoryType] = C1.[Value]
AND
C1.[Enumeration] = 'CategoryType'
AND
C1.[Enumerand] = 'Commercial'
INNER JOIN
[dbo].[TableB] AS B
ON
A.[Id] = B.[OwnerId]
INNER JOIN
[dbo].[Constants] AS C2
ON
B.[MemberType] = C2.[Value]
AND
C2.[Enumeration] = 'MemberType'
AND
C2.[Enumerand] = 'Group'
Thanks
May 1, 2008 at 7:44 am
It depends on the UDF, but frequently they have no statistics on them, so the optimizer assumes one row. Since, in this case, it really will be one row, I don't think it will be that problematic, but testing will give you the answer. I've been burned too many times with poorly constructed UDF's to put too much faith in them. However, I have seen them work well too.
An enum is nothing but a lookup table. I don't like the idea of a universal enum table (also nick-named MUD tables, there's an article about it somewhere here). Working with a table that holds some standard constants that are applicable across the system is different though. I think this provides for simple queries, what I think of as simple queries anyway since I do joins to tables all the time, but you can also put foreign key constraints against the enums so that only the values that approve are in place. If you go the UDF route, you'll also have to maintain coded constraints against the columns to ensure that the values don't violate the integrity of the UDF. Honestly, that doesn't sound as simple as a table.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
May 1, 2008 at 8:00 am
Hi Grant
To me this sounds like you are advocating a table per 'enum' else I can't see how the FK constraint would work?
I have a preference for less tables - but I can go with multiple tiny table instead of UDFs, as I can keep them tidy using a Schema (actually o=all my Constant UDFs are in a Schema called Constant already).
So, you are suggesting in my fabricated example two tables:
[Constant].[CategoryType] (Enumerand nvarchar(50) , Value int)
[Constant].[MemberType] (Enumerand nvarchar(50) , Value int)
etc
Where the data in CategoryType might, for example be:
'Commercial' 1
'Charity' 2
'Personal' 3
etc
This would allow for a FK between TableA.CategoryType and CategoryType.Value
---
FYI, my 'Constant' UDFs typically look like this:
CREATE FUNCTION [Constant].[CategoryType_Commercial]
(
)
RETURNS int
WITH SCHEMABINDING
AS
BEGIN
-- Return a fixed value of 1 (equivalent to Enumeration/Enumerand [MemberType/User] value
RETURN 1;
END
Cheers
May 1, 2008 at 8:15 am
Absolutely. Even down to the schema, although we were lazy and just left the enums in the 'dbo' schema, but yes. That's the way to go. You're maintaining a bit of structure instead of a bit of code, but it works better with the mechanisms within the database tuning and storage engine and manipulating lookups down the road is much easier if it's simply data, which is all an enum is anyway.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
May 1, 2008 at 8:18 am
Separate table per constant type is the way I go on this.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
May 1, 2008 at 8:29 am
There are several discussions on this already on the web. Here's one...
http://www.sqlservercentral.com/articles/T-SQL/61244/
The article is poor, but the discussion is useful.
Ryan Randall
Solutions are easy. Understanding the problem, now, that's the hard part.
May 1, 2008 at 8:32 am
I'm also reminded of this article...
http://www.sqlservercentral.com/articles/Advanced/lookuptablemadness/1464/
Ryan Randall
Solutions are easy. Understanding the problem, now, that's the hard part.
May 1, 2008 at 8:36 am
RyanRandall (5/1/2008)
I'm also reminded of this article...http://www.sqlservercentral.com/articles/Advanced/lookuptablemadness/1464/
Yeah, that was the one I meant when I said "MUD" but it's supposed to be "MUCK."
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
May 1, 2008 at 8:50 am
Grant Fritchey (5/1/2008)
RyanRandall (5/1/2008)
I'm also reminded of this article...http://www.sqlservercentral.com/articles/Advanced/lookuptablemadness/1464/
Yeah, that was the one I meant when I said "MUD" but it's supposed to be "MUCK."
:hehe:
Where there's muck, there's mud... 😀
Ryan Randall
Solutions are easy. Understanding the problem, now, that's the hard part.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply