SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Design


Design

Author
Message
jeff.mason
jeff.mason
UDP Broadcaster
UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)

Group: General Forum Members
Points: 1457 Visits: 2137
Agreed Robert, but I have seen cases where people assumed that the IDENTITY was guarding the data for uniqueness and so inserted dupes by mistake, assuming that it would be prevented by the IDENTITY. You have to have the unique constraint to protect against clueless data loaders. Not saying it's smart but it IS real life unfortunately.
UMG Developer
UMG Developer
SSCrazy
SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)

Group: General Forum Members
Points: 2672 Visits: 2204
Robert Frasca (9/27/2010)
Well, I suppose you could do that but that defeats the purpose of using the identity attribute.


No, not if your purpose is just to get a sequential set of numbers in a column by default. I don't think they should have named it identity, I think they should have done something more like they did in Access and call it an AutoNumber field, since that is what it really is. It becomes an identity once you set the primary key or unique index on it. Of course that wouldn't follow the SQL "standard" but neither does Microsoft's implementation of IDENTITY. (However it appears that this is one SQL feature that is commonly implemented differently, if at all.)
Hugo Kornelis
Hugo Kornelis
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10916 Visits: 11980
UMG Developer (9/27/2010)
(However it appears that this is one SQL feature that is commonly implemented differently, if at all.)

That's probably because all products had already implemented their own syntax for this when the standardisation committee finally decided to forget relational purity and add this commonly needed feature to the standard.


Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
TomThomson
TomThomson
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14240 Visits: 12197
The question resulted in an interesting discussion, so I guess it must be a good question. But I like neither the question nor the answer (despite having got it right by assuming that the answer would not only be be as stupid as the rules suggested in BoL but would also so interpret the rules as to maximise the stupidness)
And transactional replication certainly doesn't create any extra columns by magic (at least in didn't in SQLS 2000), and neither does snapshot replication; merge replication may do it (I've never used it, so never read up on it).

Tom

jeff.mason
jeff.mason
UDP Broadcaster
UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)

Group: General Forum Members
Points: 1457 Visits: 2137
Tom.Thomson (9/27/2010)
And transactional replication certainly doesn't create any extra columns by magic (at least in didn't in SQLS 2000), and neither does snapshot replication; merge replication may do it (I've never used it, so never read up on it).


It specifically happens on transactional replication with updateable subscriptions and on merge replication. It is how the system deals with conflicts when two separate sources could in theory get updated at nearly the same time in the same location in the data.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search