Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Predict the outcome


Predict the outcome

Author
Message
David Marble
David Marble
Valued Member
Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)

Group: General Forum Members
Points: 58 Visits: 76
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
Ray Hastie
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1264 Visits: 142
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
Daniel Bowlin
SSCrazy
SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)

Group: General Forum Members
Points: 2950 Visits: 2629
Not being able to use an increment of 0 makes sense, so an error makes sense.
Oleg Netchaev
Oleg Netchaev
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1693 Visits: 1807
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
Chris Stewart-397033
SSCommitted
SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)

Group: General Forum Members
Points: 1932 Visits: 404
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.
Tom Garth
Tom Garth
Ten Centuries
Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)

Group: General Forum Members
Points: 1013 Visits: 1499
Chris Stewart-397033 (7/15/2010)
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.


But what about that first inserted row, in this case with the value of -1?:-D

Tom Garth
Vertical Solutions

"There are three kinds of men. The one that learns by reading. The few who learn by observation. The rest of them have to pee on the electric fence for themselves." -- Will Rogers

b.narasimhakumar
b.narasimhakumar
Grasshopper
Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)

Group: General Forum Members
Points: 21 Visits: 246
correct answer is :

Create Table MyTable
(
mainkey int not null identity (0,1)
)
Chris Stewart-397033
Chris Stewart-397033
SSCommitted
SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)

Group: General Forum Members
Points: 1932 Visits: 404
Tom Garth (7/15/2010)
Chris Stewart-397033 (7/15/2010)
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.


But what about that first inserted row, in this case with the value of -1?:-D


I'm guessing that the engine first checks for an accurate construct, regardless of what the seed value is. ;-)
UMG Developer
UMG Developer
SSCrazy
SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)

Group: General Forum Members
Points: 2280 Visits: 2204
Nice question, took a little looking at it to figure out what was going on. Thanks!
gchornenkyy
gchornenkyy
SSC-Addicted
SSC-Addicted (419 reputation)SSC-Addicted (419 reputation)SSC-Addicted (419 reputation)SSC-Addicted (419 reputation)SSC-Addicted (419 reputation)SSC-Addicted (419 reputation)SSC-Addicted (419 reputation)SSC-Addicted (419 reputation)

Group: General Forum Members
Points: 419 Visits: 373
dbowlin (7/15/2010)
Not being able to use an increment of 0 makes sense, so an error makes sense.


What about situations when you need table with the only row :-)
Logically thinking system must allow you insert at least 1 row...

BTW: identity() is just a function to generate some numbers according rules you need, if you need to manage those generated numbers then table constrains must be used
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