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.
> "--CELKO--" <jcelko...@earthlink.net> wrote in message > news:1187703400.003288.198590@r34g2000hsd.googlegroups.com... > >>> I need some advise as to whether it's better to use natural primary keys > >>> or introduce artificial identity columns for replication purposes. <<
> > Go over to an article I just did at the INTELLIGENT ENTERPRISE > > website. In particular, pay attention to validation and > > verification.
>> That's kind of a "busy" website/homepage. Any clues to the path to follow. <<
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.
>> The whole idea of primary keys in SQL is rather like a beautiful woman that is all dressed up. And nowhere to go <<
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.
I do so love the metaphor. But I will relent (for at least this 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.
>> I do so love the metaphor. But I will relent (for at least this once) and spoon feed some basic thoughts as cogently as I can muster. <<
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.
> 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 :)
