Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 123»»»

Predict the outcome Expand / Collapse
Author
Message
Posted Wednesday, July 14, 2010 9:32 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, September 03, 2010 4:19 AM
Points: 158, Visits: 72
Comments posted to this topic are about the item Predict the outcome
Post #952829
Posted Thursday, July 15, 2010 1:06 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Wednesday, April 16, 2014 2:13 AM
Points: 1,865, Visits: 368
thanks, nice simple one...
Post #952887
Posted Thursday, July 15, 2010 1:07 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, February 25, 2014 7:04 PM
Points: 176, Visits: 563
1% of 186 or 2 people answered in relation to user permission. I bet these same people were wary of syntax errors and gotchas.
Post #952888
Posted Thursday, July 15, 2010 3:37 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, January 24, 2013 9:59 PM
Points: 1,354, Visits: 1,299
The URL in the explanation didn't say you cannot use a zero -- at least not that I found.

So why can't you use it? If you have IDENTITY INSERTS on, can't you duplicate values on an Identity column? I never tried that but I think you can unless you also create a constraint to make it unique. So I thought if you have an increment of 0, you'd get the same number in the identity column for every new row.
Post #952941
Posted Thursday, July 15, 2010 4:19 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Wednesday, April 16, 2014 12:51 AM
Points: 2,925, Visits: 877
I couldn't find any mention of the restriction on increment either. My gut reaction was to answer "error" given that an zero increment makes no sense, but as I couldn't see that in the documentation I figured it may actually be valid.

I should have followed my first thought...



Just because you're right doesn't mean everybody else is wrong.
Post #952967
Posted Thursday, July 15, 2010 4:54 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Thursday, April 03, 2014 10:34 AM
Points: 3,352, Visits: 1,478
Rune Bivrin (7/15/2010)
I couldn't find any mention of the restriction on increment either.
.


I agree, there doesn't seem to be any mention in the documentation about it. And it's not just zero increments - you can't have a seed or increment which has a higher value than the maximum for the data type, or lower than the minimum (hence you can't have negative increments with tinyint identity columns). You can't have fractional values for either seed or increment, etc., etc.

All semi-obvious, but as yet I can't find any official documentation.

Duncan
Post #952992
Posted Thursday, July 15, 2010 5:46 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Monday, April 14, 2014 10:58 AM
Points: 819, Visits: 832
Good question. In retrospect it seems obvious, and I think that's why I didn't go with the obvious answer. Like others I researched the question as far as possible without actually running the script, and I couldn't find any solid documentation on it either. I went with "can't predict" figuring there might be an option set that could affect the outcome.

-----
a haiku...

NULL is not zero
NULL is not an empty string
NULL is the unknown
Post #953026
Posted Thursday, July 15, 2010 6:23 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Saturday, April 12, 2014 12:29 AM
Points: 419, Visits: 966
For all who look at the data in the URL, I agree that there is no explicit statement that one cannot have a 0 increment. I suppose the following line,

"If an identity column exists for a table with frequent deletions, gaps can occur between identity values. If this is a concern, do not use the IDENTITY property. "

infers that issue, if you add to the sentence "because the identity property may try to make a duplicate after you fill in a gap without proper coding to look for duplicates" etc.

If one does try to run the command, the actual error message that I got was "Identity column 'mainkey' contains invalid INCREMENT." So it's an "undocumented feature."

And no, I honestly didn't execute the command before I answered the question.
Post #953063
Posted Thursday, July 15, 2010 6:45 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: 2 days ago @ 2:11 PM
Points: 2,411, Visits: 3,440
It makes sense to me that an identity increment can't be zero. An identity column is supposed to be a unique index on a row. If you had zero for the increment, all rows would have the same value; the starting value. That would make no sense at all.

Do we really need to have Microsoft tell us the perfectly obvious?
Post #953077
Posted Thursday, July 15, 2010 6:55 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, November 30, 2012 9:37 AM
Points: 1, Visits: 85
BTW, the answer ,"Explanation: We cannot use 0 as the incremental seed of the primary key. " Is not accurate. The create statement does not mention primary key. It is an invalid increment of an identity column, nothing to do with the primary key or lack thereof.
Post #953092
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse