Primary key selection
Kev
We have a database with very few primary keys defined. In order to implement
transactional replication with updateable subscribers we need to define
primary keys on all tables. I need some advise as to whether it's better to
use natural primary keys or introduce artificial identity columns for
replication purposes. Or indeed whether it actually makes a huge difference
either way.
Thanks,
Kev.
--CELKO--
Go over to an article I just did at the INTELLIGENT ENTERPRISE
website. In particular, pay attention to validation and
verification.
sloan
That's kind of a "busy" website/homepage.
Any clues to the path to follow.
"--CELKO--" <jcel...@earthlink.net> wrote in message
news:1187703400.0...@r34g2000hsd.googlegroups.com...
Kev
Steve Dassin
woman that is all dressed up. And nowhere to go :-)
Now INTELLIGENT ENTERPRISE should hire me to write for them.
I can write and think at the same time!
"--CELKO--" <jcel...@earthlink.net> wrote in message
news:1187703400.0...@r34g2000hsd.googlegroups.com...
--CELKO--
It is a on-line magazine, not a home page. The article should be on
the home page, or a local search away. It is a few thousand words
about keys, their definition and that designing a database is hard
work and research.
--CELKO--
That makes no sense -- hey, how about "Normal forms are like a gold
fish that is holding public office on a wet Tuesday, next to a snail!"
or some other non-sequiter.
The problem with a PRIMARY KEY is that it is derived from a sequential
file system sort key, which has to be unique for PHYSICAL reasons. It
took Dr. Codd a little time to realize that a key is a key, and none
are more equal than others.
>> Now INTELLIGENT ENTERPRISE should hire me to write for them. I can write and think at the same time! <<
You do know you can submit material to paying on-line magazines, don't
you? Of course, you be edited, have to meet deadlines and make some
kind of sense. That means you will have to think and write
coherently, which something you have not done in many months of
posting here.
Steve Dassin
once) and spoon feed some basic thoughts as cogently as I can muster.
> The problem with a PRIMARY KEY is that it is derived from a sequential
> file system sort key, which has to be unique for PHYSICAL reasons. It
> took Dr. Codd a little time to realize that a key is a key, and none
> are more equal than others.
This is quite good. But do you realize that 99% of sql server users
see this and think 'so what'. The only things that matter are topics
covered in BOL. Every problem must be solved within these topics.
Just as you think every problem can be logically placed in the
framework of the sql standards and client-server thinking. The result
of this is you passing a 1000 parameters to a stored procedure
and Tony doing his dynamic sql dance. I just need a few bright
people with their common sense intact to show how both of you
are cowboy coders :-)
You will not find a more cogent blog than mine. Perhaps you will
actually read a few of the articles and that way we can play
on a more level field.
>> The whole idea of primary keys in SQL is rather like a beautiful
>> woman that is all dressed up. And nowhere to go <<
This is a metaphor for the idea that sql gives very little meaning
to the idea of any 'key'. The practical uses of a key should be
far greater than what's offered in sql.
--CELKO--
Good! Your metaphors suck like <<insert awkward metaphor here>>
{{ did I just do a meta-metaphor? }}
>> This is quite good. But do you realize that 99% of SQL Server users see this and think 'so what'. The only things that matter are topics covered in BOL. Every problem must be solved within these topics. <<
I agree with you on that -- whcih is why I get so much hate email :)
>> Just as you think every problem can be logically placed in the
framework of the SQL Standards and client-server thinking. The result
of this is you passing a 1000 parameters to a stored procedure and
Tony doing his dynamic SQL dance. I just need a few bright people with
their common sense intact to show how both of you are cowboy
coders :-) <<
In my defense, I said that T-SQL can handle over 2K parameters (hell,
DB2 can do 32K parameters if you pick the right setting!). Everyone
misses the "Law of Five" I keep quoting from freshman Software
Engineering. Long parameter lists are easy to generate, to maintain,
yadda, yadda, yadda.
I get funny looks constantly from client for recommending that they do
NOT use SQL for their problems. Text, geo-spatial, social networks,
statistics, etc. all require different tools than SQL; this is just a
data maintenance and retrieval language.
>> You will not find a more cogent blog than mine. Perhaps you will actually read a few of the articles and that way we can play
on a more level field. <<
I can find quite a few more cogent ones, and I do read the articles
there. But keep being the devil. Eventually, good ideas win out or
synthesize into what is there.
Steve Dassin
> I get funny looks constantly from client for recommending that they do
> NOT use SQL for their problems. Text, geo-spatial, social networks,
> statistics, etc. all require different tools than SQL;
>.
> this is just a data maintenance and retrieval language.
In todays IT world this is actually a profound statement! Sql is
not treated as a mere retrieval language but a retrieval language
of anything and everything! It is the seduction to attempt to
achieve univeral retrieval that is the problem. And then people
make a name for themselves by inventing justification(s) for these
seductions. (Of course you and I have never been seduced:)
Does sql reward those who can avoid such temptation?
> Your metaphors suck...
And here I thought I give good metaphor. I would put it to a vote
but just where will the voters come from? :)
> I can find quite a few more cogent ones, and I do read the articles
> there. But keep being the devil. Eventually, good ideas win out or
> synthesize into what is there.
Just when I think I've presented a thought too far, I get this.
And to think there are those that cannot find an example of
a courteous Celko :)