July 15, 2010 at 2:30 pm
Steve Jones - Editor (7/15/2010)
Jeff Moden (7/15/2010)
...And, no... I'm not trying to be a smart guy here or challenge anyone's answer... I've never run into such a problem and I'd really like to kinow where such problem would occur. Thanks.
Ditto. Only slightly wracked my brain, but I haven't seen a place where the sequence works by identity doesn't. I have had slowness with very high volume inserts, but not sure a sequence fixes that. Have run out of identities, same thing.
Client side wanting the generation works better with GUID.
I could see that complex PKs, alphanumerics might want a sequence instead, but straight number generation? What am I missing?
Agreed. That's actually the point I'm trying to make and, together with the "I don't have the time to do it right" argument, the reason why I'd consider the OP's approach for the given scenario as an"OUCH" philosophy. There are scenarios where a sequence table is a valid approach. But I, personally, don't consider the given scenario to be valid. It's a weak argument due to personal opinion but I haven't seen a stronger argument against it (related to the given scenario, not the available options in general). Yet.
July 15, 2010 at 10:30 pm
Jeff Moden (7/15/2010)
Can you good folks give me an example of when IDENTITY will not work?
When you need a sequence across several tables
When you need to pre-allocate a range of sequence values to a process
When you need to guarantee that multi-row inserts will obtain a contiguous range
When you absolutely cannot have gaps
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
July 16, 2010 at 1:14 am
Paul White NZ (7/15/2010)
Jeff Moden (7/15/2010)
Can you good folks give me an example of when IDENTITY will not work?When you need a sequence across several tables
When you need to pre-allocate a range of sequence values to a process
When you need to guarantee that multi-row inserts will obtain a contiguous range
When you absolutely cannot have gaps
I would dare to add:
When you have to guarantee uniqueness of keys between eterogeneous systems.
And my favourite one:
When some genius coded it that way 10 years ago and switching to identity would mean looking for long lasting trouble.
-- Gianluca Sartori
July 16, 2010 at 1:42 am
Gianluca Sartori (7/16/2010)
When some genius coded it that way 10 years ago and switching to identity would mean looking for long lasting trouble.
That would probably be #1 😀
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
July 16, 2010 at 2:17 am
Paul White NZ (7/15/2010)
Jeff Moden (7/15/2010)
Can you good folks give me an example of when IDENTITY will not work?When you need a sequence across several tables
When you need to pre-allocate a range of sequence values to a process
When you need to guarantee that multi-row inserts will obtain a contiguous range
When you absolutely cannot have gaps
Loosely speaking, when the values are of importance to both the database and the business (or application).
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
July 16, 2010 at 5:07 am
Grant Fritchey (7/15/2010)
I think we've shot past denial and are moving in the direction of outright insanity on this. I'm still reading it because it's like watching a car crash.
Rather than outright insanity I suspect it's stupid offensive trolling. I had a try at injecting some sense but after watching the OP being offensive to several good people I decided to respond to some of that and finally I've given up on the thread - in a way it's funny, in a way it's tragic, but mostly it's just boring - I don't enjoy seeing good people being insulted for trying to help.
Tom
July 16, 2010 at 5:15 am
Tom.Thomson (7/16/2010)
Grant Fritchey (7/15/2010)
I think we've shot past denial and are moving in the direction of outright insanity on this. I'm still reading it because it's like watching a car crash.Rather than outright insanity I suspect it's stupid offensive trolling. I had a try at injecting some sense but after watching the OP being offensive to several good people I decided to respond to some of that and finally I've given up on the thread - in a way it's funny, in a way it's tragic, but mostly it's just boring - I don't enjoy seeing good people being insulted for trying to help.
I'm just impressed in the absolute certitude in the face of apparently overwhelming negation. I'm right the universe is wrong. They laughed at me at the academy. It's alive... alive!
"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
July 16, 2010 at 6:45 am
Grant Fritchey (7/16/2010)
I'm just impressed in the absolute certitude in the face of apparently overwhelming negation. I'm right the universe is wrong. They laughed at me at the academy. It's alive... alive!
Damn it. I resisted so far, now I have to go look at that thread!
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
July 16, 2010 at 6:46 am
Like I said some pages ago, that guy reminds me of one of the developers in my company and I had the same argument with him. He still would not understand. I have a strange feeling that he is one and the same person... 🙂
-Roy
July 16, 2010 at 6:47 am
This
http://www.sqlservercentral.com/Forums/Topic953765-146-1.aspx
could be an interesting one to watch
July 16, 2010 at 6:56 am
Jeff Moden (7/15/2010)
[I've never run into such a problem with IDENTITY even when "dual mastering" w/bidirectional replication was the order of the day. Can you good folks give me an example of when IDENTITY will not work?And, no... I'm not trying to be a smart guy here or challenge anyone's answer... I've never run into such a problem and I'd really like to kinow where such problem would occur. Thanks.
My favourite example is a menu system which is required to be completely data driven, to function in multiple languages, and to be highly customisable/extremely flexible (different for different customers, for different locations within a particular customer, and so on). There are many things which come in different flavours according to language, country, location, customer, and other qualifiers (client hardware capability, for example). So each table tends to have a primary key like (flavourID,thingID) - the simplest example is the table of text fragments (paragraphs, sentences, interjections,...) with primary key (languageID,TextID). The languageID can be generated as an identity column, provided one doesn't mind different orthographies for a given language counting as different languages (eg for Japanese we may need plain Kanji, Hiragana, Katakana, Romaji, and Kanji-with-Furigana which would count as 5 languages instead of as 1, but for display purposes they are indeed five languages so that's OK: the tategaki/yokogaki distinction, and the left-right/righ-left distinction in yokogaki, can be treated as layout rather than language issues). The TextID is a different matter - the application wants to say something like "give me all the information about that menu page (a page may be multiple screens, of course) in this language, we don't know what the set of languages is or how many there are (new languages can be added at any time, and this has to be cheap and easy) so a table per language isn't viable. The TextID could be generated by using a TextID table with just a single identity column. Since there may be many thousands of text IDs, this wastes space in the DB, and in backups, so it is better to do it with a sequence table. Even for a single table, I prefer a single row sequence table rather than the single-column identity table solution even though the single column table is merely untidy rather than catastrophic. When many tables represent flavoured objects, using that approach would be downright silly, and a (many rowed) sequence table is to be preferred.
This isn't an imaginary example. It's a real one. At Neos changing to do things this way gave us flexibility, reliability, and improved performance.
Some people may argue that it would be better to assign a single column identity to each languageID-textID combination, and of course that can be generated as as identity column. However, it isn't useful for anything: it expresses no information that isn't already present in (languageID,textID), it doesn't eliminate the use of (languageID,textID) as the means of representing a required text fragment, and it will put extra joins into many of the most frequent queries if it's forced into use in areas where it's not appropriate. Much the same applies to many other tables in the menu system. And of course some extremist normalisation nuts insist that compound primary keys are a denormalisation (I wish they would read the literature).
Tom
July 16, 2010 at 6:57 am
edit: was identical to the message above - got duplicated somehow. Pity there's no delete.
Tom
July 16, 2010 at 7:08 am
Tom.Thomson (7/16/2010)
... (eg for Japanese we may need plain Kanji, Hiragana, Katakana, Romaji, and Kanji-with-Furigana which would count as 5 languages instead of as 1, but for display purposes they are indeed five languages so that's OK: the tategaki/yokogaki distinction, and the left-right/righ-left distinction in yokogaki, can be treated as layout rather than language issues)...
Am I the only one here that doesn't have a clue of what you're talking about?
Today I was proved to be both stupid and ignorant. 😛
-- Gianluca Sartori
July 16, 2010 at 7:14 am
Dave Ballantyne (7/16/2010)
Thishttp://www.sqlservercentral.com/Forums/Topic953765-146-1.aspx
could be an interesting one to watch
I wonder if they exposed their server to the internet when the sa account had a blank password too?
Makes you wonder......
Greg E
July 16, 2010 at 7:55 am
Gianluca Sartori (7/16/2010)
Tom.Thomson (7/16/2010)
... (eg for Japanese we may need plain Kanji, Hiragana, Katakana, Romaji, and Kanji-with-Furigana which would count as 5 languages instead of as 1, but for display purposes they are indeed five languages so that's OK: the tategaki/yokogaki distinction, and the left-right/righ-left distinction in yokogaki, can be treated as layout rather than language issues)...Am I the only one here that doesn't have a clue of what you're talking about?
Kanji is Japanese written with Chinese characters; Hiragana is a syllabary that used to be used for woman's literature and by women for writin letters and so on, but is now in more general use. Katagana is another syllabary, often used for transliterating foreign words. Romaji is the official method of writing Japanese using the Roman alphabet (with some extra diacritics, like that macron on the o) which has been taught in all schools for about half a century. Kanji-with-Furigama is Japanese written with Chinese characters each accompanied by a Hiragana string that shows how it is pronounced, and is currently the most common form of Japanese in newspapers and magazines. Tategaki is writing in columns from top to bottom, first column on right and last column on left. Yokogaki is writing in rows, either left to right (like English) or right to left (like Arabic), first row at top and last row at bottom - these two versions of Yokogaki have different Japanese names, which I have forgotten Hurrah! freeing up some brain space :hehe:).
Working on systems that have to work in multiple languages has made me learn many facts that in almost every other context are completely useless. 😉
Today I was proved to be both stupid and ignorant. :-P[/quote]
Well, no. Doobya said you were both stupid and ignorant which for me (if I didn't know already) would prove you are not.
Tom
Viewing 15 posts - 16,426 through 16,440 (of 66,712 total)
You must be logged in to reply to this topic. Login to reply