none
Is it a documentation problem?

    Question

  • I am reviewing some code in our application right now and I found that we're testing in a loop if the column is an identity. I know that only 1 identity column can exist per table. So, I wanted to double check that and I opened BOL to verify. And here http://technet.microsoft.com/en-us/library/ms186775.aspx I see someone added a user's remark, but this fact is not documented.

    So, is it a documentation omission or there can be more than 1 identity column in a table? And if the former, why it is not explicitly stated in BOL?

    Thanks in advance.


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles


    Wednesday, October 16, 2013 6:52 PM

Answers

  • I see it is not documented in that location.  However it is on the CREATE TABLE statment:

    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. Identity columns are typically used with PRIMARY KEY constraints to serve as the unique row identifier for the table. The IDENTITY property can be assigned to tinyint, smallint, int, bigint, decimal(p,0), or numeric(p,0) columns. Only one identity column can be created per table. Bound defaults and DEFAULT constraints cannot be used with an identity column. Both the seed and increment or neither must be specified. If neither is specified, the default is (1,1).

    http://technet.microsoft.com/en-us/library/ms174979.aspx

    Wednesday, October 16, 2013 7:01 PM
  • I agree this is an omission in that document. I test following Code in SQL Server 2005, 2008, 2008 r2 and 2012 right now the error message always is:

    Msg 2744, Level 16, State 2, Line 1
    Multiple identity columns specified for table 'TestIdentity'. Only one identity column per table is allowed.

    CREATE TABLE TestIdentity
        (
          Id1 INT IDENTITY ,
          Id2 INT IDENTITY
        ) ;



    Saeid Hasani, sqldevelop.wordpress.com

    Download Books Online for SQL Server 2012

    Wednesday, October 16, 2013 7:05 PM
  • I will update the topic IDENTITY (Property) (Transact-SQL).

    Rick Byham, Microsoft, SQL Server Books Online, Implies no warranty

    Friday, October 18, 2013 7:03 PM
  • The topic has now been updated.

    Rick Byham, Microsoft, SQL Server Books Online, Implies no warranty

    Tuesday, October 29, 2013 10:02 PM

All replies

  • I see it is not documented in that location.  However it is on the CREATE TABLE statment:

    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. Identity columns are typically used with PRIMARY KEY constraints to serve as the unique row identifier for the table. The IDENTITY property can be assigned to tinyint, smallint, int, bigint, decimal(p,0), or numeric(p,0) columns. Only one identity column can be created per table. Bound defaults and DEFAULT constraints cannot be used with an identity column. Both the seed and increment or neither must be specified. If neither is specified, the default is (1,1).

    http://technet.microsoft.com/en-us/library/ms174979.aspx

    Wednesday, October 16, 2013 7:01 PM
  • I agree this is an omission in that document. I test following Code in SQL Server 2005, 2008, 2008 r2 and 2012 right now the error message always is:

    Msg 2744, Level 16, State 2, Line 1
    Multiple identity columns specified for table 'TestIdentity'. Only one identity column per table is allowed.

    CREATE TABLE TestIdentity
        (
          Id1 INT IDENTITY ,
          Id2 INT IDENTITY
        ) ;



    Saeid Hasani, sqldevelop.wordpress.com

    Download Books Online for SQL Server 2012

    Wednesday, October 16, 2013 7:05 PM
  • Thanks, I believe it's important to add this info to that document as well, because this is what I found by searching.

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Wednesday, October 16, 2013 8:06 PM
  • I will update the topic IDENTITY (Property) (Transact-SQL).

    Rick Byham, Microsoft, SQL Server Books Online, Implies no warranty

    Friday, October 18, 2013 7:03 PM
  • Hi,

    I tested on SQL 2008 R2 SP2 : it doesn't allow 2 or more identity columns per table.


    Sebastian Sajaroff Senior DBA Pharmacies Jean Coutu

    Friday, October 18, 2013 7:15 PM
  • Thanks a lot.

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Friday, October 18, 2013 8:27 PM
  • The topic has now been updated.

    Rick Byham, Microsoft, SQL Server Books Online, Implies no warranty

    Tuesday, October 29, 2013 10:02 PM
  • For clarity, would you update the SQL 2008/2008 R2 documentation also.  I wouldn't want someone to think that is a functionality change in SQL 2012.

    Wednesday, November 06, 2013 4:48 PM
  • I did update the topic in both SQL Server 2008 R2 and SQL Server 2014. The change will appear online when those document sets are republished, which will take a while. We aren't publishing updates to SQL Server 2008 any more.

    Rick Byham, Microsoft, SQL Server Books Online, Implies no warranty

    Wednesday, November 06, 2013 5:16 PM