Predict the outcome

Question

Predict the outcome

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

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

Tom Garth SSCertifiable Points: 6193 More actions · Answer 1

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

b.narasimhakumar SSC-Addicted Points: 409 More actions · Answer 2

correct answer is :

Create Table MyTable

(

mainkey int not null identity (0,1)

)

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

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 SSChampion Points: 13482 More actions · Answer 4

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

gchornenkyy Say Hey Kid Points: 687 More actions · Answer 5

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

SQLRNNR SSC Guru Points: 281334 More actions · Answer 6

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

Toreador SSChampion Points: 11365 More actions · Answer 7

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 🙂

george sibbald SSC Guru Points: 104210 More actions · Answer 8

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.

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

TomThomson SSC Guru Points: 104773 More actions · Answer 9

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