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.

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.

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.

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.

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.

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.