Log in  ::  Register  ::  Not logged in

 Recent PostsRecent Posts Popular TopicsPopular Topics
 Home Search Members Calendar Who's On

 Predict the outcome Rate Topic Display Mode Topic Options
Author
 Message
 Posted Thursday, July 15, 2010 7:04 AM
 Valued Member Group: General Forum Members Last Login: Wednesday, February 29, 2012 6:22 AM 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
Post #953096
 Posted Thursday, July 15, 2010 7:06 AM
 Ten Centuries Group: General Forum Members Last Login: Friday, August 16, 2013 7:07 AM Points: 1,116, Visits: 137
 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.
Post #953098
 Posted Thursday, July 15, 2010 7:27 AM
 SSCrazy Group: General Forum Members Last Login: Today @ 9:37 AM Points: 2,780, Visits: 2,517
 Not being able to use an increment of 0 makes sense, so an error makes sense.
Post #953118
 Posted Thursday, July 15, 2010 9:24 AM
 SSCommitted Group: General Forum Members Last Login: Tuesday, December 03, 2013 5:28 PM Points: 1,676, Visits: 1,728
 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
Post #953239
 Posted Thursday, July 15, 2010 9:39 AM
 Ten Centuries Group: General Forum Members Last Login: Monday, December 02, 2013 1:39 PM Points: 1,418, Visits: 283
 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.
Post #953251
 Posted Thursday, July 15, 2010 9:51 AM
 SSC Eights! Group: General Forum Members Last Login: Friday, February 04, 2011 7:20 AM Points: 977, Visits: 1,499
 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? Tom GarthVertical 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
Post #953260
 Posted Thursday, July 15, 2010 9:58 AM
 Grasshopper Group: General Forum Members Last Login: Monday, December 02, 2013 5:33 AM Points: 21, Visits: 239
 correct answer is : Create Table MyTable( mainkey int not null identity (0,1))
Post #953269
 Posted Thursday, July 15, 2010 10:22 AM
 Ten Centuries Group: General Forum Members Last Login: Monday, December 02, 2013 1:39 PM Points: 1,418, Visits: 283
 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?I'm guessing that the engine first checks for an accurate construct, regardless of what the seed value is.
Post #953301
 Posted Thursday, July 15, 2010 10:51 AM
 SSCrazy Group: General Forum Members Last Login: Thursday, November 07, 2013 10:01 AM Points: 2,163, Visits: 2,181
 Nice question, took a little looking at it to figure out what was going on. Thanks!
Post #953318
 Posted Thursday, July 15, 2010 1:06 PM
 Old Hand Group: General Forum Members Last Login: Tuesday, October 29, 2013 2:16 PM Points: 311, Visits: 278
 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
Post #953431

 Permissions