Identity Data Types

  • He has a point, the first link from the answer states:

    http://msdn.microsoft.com/en-AU/library/ms189838.aspx

    Valid data types for an identity column are any data types of the integer data type category, except for the bit data type, or decimal data type.

    The 2nd link states:

    http://msdn.microsoft.com/en-AU/library/ms174979.aspx

    The IDENTITY property can be assigned to tinyint, smallint, int, bigint, decimal(p,0), or numeric(p,0) columns.

  • So I have a point and I don't have a point. Microsoft ohhhhh!!!!

    Julien

  • I was between option 4 & 5, then figured out that there is nothing wrong with tinyint & smallint data types to be set as identity columns, thanx for the question.

  • Nice question, thanks for sharing

  • I worked at a company where all the identity columns were decimal(18,0), and I'd already made small tables in other circumstances that were tinyint, so easy one for me. if numeric (the only one I'd not used) had been present and not present with decimal I might have thought twice

  • Andrew G (1/13/2015)


    He has a point, the first link from the answer states:

    http://msdn.microsoft.com/en-AU/library/ms189838.aspx

    Valid data types for an identity column are any data types of the integer data type category, except for the bit data type, or decimal data type.

    No he doesnt.

    You have highlighted a section of that text in what looks like a deliberate attempt to conceal what the text actually says, but is probably just the result of inability to read and understand plain English. The commas clearly indicate that "except for the bit data type" is the whole exception. Besides, decimal data type can't be part of an exception to a statement about integer data types because decimal data type isn't an integer data type.

    Tom

  • TomThomson (1/14/2015)


    Andrew G (1/13/2015)


    He has a point, the first link from the answer states:

    http://msdn.microsoft.com/en-AU/library/ms189838.aspx

    Valid data types for an identity column are any data types of the integer data type category, except for the bit data type, or decimal data type.

    No he doesnt.

    You have highlighted a section of that text in what looks like a deliberate attempt to conceal what the text actually says, but is probably just the result of inability to read and understand plain English. The commas clearly indicate that "except for the bit data type" is the whole exception. Besides, decimal data type can't be part of an exception to a statement about integer data types because decimal data type isn't an integer data type.

    Yes he does.

    I understand the msdn explanation (which is wrong), but the point is that it's easily misinterpreted by any reasonable person.

    You have deliberately attempted to conceal what I was saying. The phrase "data types of the integer data type category", key word, "category", a phrase which you obviously left out so you could insult people on their English comprehension. There is no integer data type category http://msdn.microsoft.com/en-us/library/ms187752.aspx only exact numerics, of which decimal, bit and int are members. So the explanation is both technically wrong and reasonably misinterpreted.

  • Thank you Andrew. I lived and worked for 25 years in the belief, (Thanks to various Microsoft literatures like TECHNET, SQL BOL, etc...), that integer types on various SQL Server editions are TINYINT, SMALLINT, INT and later BIGINT. Yes I freely admitted that it can be a misinterpretation if one don't dig deep enough and finds out that NUMERIC and DECIMAL (n, 0) can be treated as INT types and used as IDENTITY column too.

    Thanks again

    Julien.

  • Thanks for the question.

  • +2 - great review question, thanks!

    Andre Ranieri

  • I have tried creating tables with all the mentioned datatypes and got the correct answer.

    eg.,

    create table tab1

    (

    col_1 numeric(12,2) identity

    )

    and got the below error for numeric or decimal datatypes with scale >0

    Msg 2749, Level 16, State 2, Line 1

    Identity column 'col_1' must be of data type int, bigint, smallint, tinyint, or decimal or numeric with a scale of 0,

    and constrained to be nonnullable.

    So the error message indicates the correctness of (answer) options for including numeric and decimal

    Thanks for the nice question steve

  • thanks for the nice question

  • good review question!

Viewing 13 posts - 16 through 28 (of 28 total)

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