August 28, 2007 at 1:39 pm
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
August 28, 2007 at 2:15 pm
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'
or
--Method 2
select
Order.OrderId,
Order.OrderDate,
Order.PaymentTypeId
from
Order
where
Order.PaymentTypeId in (
select
PaymentType.PaymentTypeId
from
PaymentType
where
PaymentTypeDescription = 'Invoice'
)
or
--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
August 28, 2007 at 5:16 pm
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.
August 28, 2007 at 5:43 pm
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.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
August 28, 2007 at 9:41 pm
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.
Viewing 5 posts - 16 through 19 (of 19 total)
You must be logged in to reply to this topic. Login to reply