Predict the outcome

• 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[/url]

"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

Create Table MyTable

(

mainkey int not null identity (0,1)

)

• 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. 😉

• Nice question, took a little looking at it to figure out what was going on. Thanks!

• 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

• Thanks for the question. I think this serves as a good reminder to people that an increment must be some value other than 0.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

• Good question. I was thrown by the 'does not have permission' option, and thought that maybe if the user did not have permission to create tabbles then this is the message that would be given, so chose the 'cannot tell' answer.

Of course when I tried it, even for a user without permission, it failed with the syntax error rather than the security error 🙂

• I really, really knew the answer to this, but it seemed so simple and obvious I thought there must be a catch and didn't answer it.

I think I am suffering a pavlovian reaction to QOTD.

---------------------------------------------------------------------

• Good question.

I think the question demonstrates a sense of humour.

Some of the comments though seem to attribute mystical properties to the identity property.

Tom

Viewing 9 posts - 16 through 23 (of 23 total)