Predict the outcome

Question

Post reply

Predict the outcome

bazzkar

SSC Veteran

Points: 270
More actions
July 14, 2010 at 9:32 pm

#221341

Comments posted to this topic are about the item Predict the outcome

Viewing 15 posts - 1 through 15 (of 23 total)

You must be logged in to reply to this topic. Login to reply

ziangij SSCrazy Eights Points: 8066 More actions · Answer 1

ziangij

SSCrazy Eights

Points: 8066

July 15, 2010 at 1:06 am

#1193274

thanks, nice simple one... 🙂

Open Minded SSCommitted Points: 1872 More actions · Answer 2

1% of 186 or 2 people answered in relation to user permission. I bet these same people were wary of syntax errors and gotchas.

cengland0 SSCertifiable Points: 6102 More actions · Answer 3

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.

Rune Bivrin SSCrazy Eights Points: 8511 More actions · Answer 4

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.

Duncan Pryde SSCertifiable Points: 7956 More actions · Answer 5

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

ronmoses@gmail.com SSCarpal Tunnel Points: 4480 More actions · Answer 6

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

Bob Razumich SSCrazy Points: 2140 More actions · Answer 7

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.

OCTom SSChampion Points: 11785 More actions · Answer 8

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? :w00t:

Terri Walls Grasshopper Points: 19 More actions · Answer 9

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.

David Marble SSC Enthusiast Points: 152 More actions · Answer 10

Not being able to use 0, or a number larger than allowed for the datatype as an increment for an identity field makes sense, i do however find it odd that you can't use a negative number, just curious if anyone else might have a thought as to why negatives aren't ok...

Oops just realized the negatives were only prevented because of the datatype...nevermind lol

Ray Hastie SSCommitted Points: 1521 More actions · Answer 11

The other issue with this question is that the category is incorrect. No where in the statement does it indicate that it is a primary key. it is an identity column but that does not necessarily equate to a PK.

Daniel Bowlin SSC-Dedicated Points: 34566 More actions · Answer 12

Not being able to use an increment of 0 makes sense, so an error makes sense.

Oleg Netchaev SSCertifiable Points: 5276 More actions · Answer 13

This is a very good question, thank you.

I believe that BOL page clearly states that "0 cannot be used", it just does not state it in these words. Here is the quote from BOL:

increment
Is the incremental value that is added to the identity value of the previous row
that was loaded.

The incremental value by its definition specifies "regular degrees or additions" when gradually increasing some value. Perhaps decreasing can be understood as negative increasing, so in this context they are the same thing. The bottom line is that the incremental value cannot be equal to zero by definition as it would fail to satisfy gradually increasing some value part.

What I really like about this question is the presence of the user does not have permission option in the answer choices. This is because whether the database user has or does not have permissions to create table, the error message will still be related to invalid increment, because the engine is going to check for invalid values in the table definition script before checking any permissions.

Oleg

Chris Stewart-397033 SSCrazy Points: 2040 More actions · Answer 14

Also in BOL under the CREATE TABLE entry is the following (partial) definition of IDENTITY (emphasis mine):

IDENTITY

Indicates that the new column is an identity column. When a new row is added to the table, the Database Engine provides a unique, incremental value for the column. ... The IDENTITY property can be assigned to tinyint, smallint, int, bigint, decimal(p,0), or numeric(p,0) columns.

increment

Is the incremental value added to the identity value of the previous row loaded.

Since the engine creates a unique, incremental value, it must be a whole number value which fits in the bounds of the data type, and is other than zero.