none
SQL table with a primary key with 17 fields

Odpovědi

  • Can you create a PK on single column (lets say an IDENTITY) and UNIQUE constraint on those columns combination?

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Blog: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance

    17. dubna 2013 9:39
    Moderátor
  • There's no way around the SQL constraint (and I must say, I'm very suspicious of your table design). So use an artificial key, or drop one of the fields. Either way you're going to have to do the re-gens.


    Rebecca M. Riordan

    17. dubna 2013 11:01
  • Yikes... 16 is still the limit in 2012. Are there any multi-part keys that you could combine perhaps? Like if the key included year and month, combine the two?

    You could use is to put the combined columns in a computed column and make the PK use the combined column instead of the other.  Or you coudl you produce a logical key from all of the PK columns, doing some concatenation.

    I agree with Rebecca that this design scares me a bit, but I understand the problem is what it is.

    If you can post the DDL we might be able to suggest something.

    Of course, the alternative is to not have a phyically implemented PK, put an index on 16 of the columns and enforce uniqueness in a trigger... But that is clearly not helping your time crunch :)


    Louis

    Without good requirements, my advice is only guesses. Please don't hold it against me if my answer answers my interpretation of your questions.

    17. dubna 2013 19:41
    Moderátor
  • How about splitting the table into 2 tables like Parent, Child tables where Parent1 table stores 8 keys and Parent2 table stores 8 keys and both tables having a sequence key and then the Child table references those sequence keys in both parent tables. The child table stores all the non-key columns.

    OR

    How about creating a Hash key (Ex : MD5 - combination of all the 17 keys) and create an index on it ?


    Narsimha

    17. dubna 2013 22:32

Všechny reakce

  • Can you create a PK on single column (lets say an IDENTITY) and UNIQUE constraint on those columns combination?

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Blog: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance

    17. dubna 2013 9:39
    Moderátor
  • We can, but we are using CA Gen, a CASE tool and this would involve a lot of re-gens and we don't currently have the time as we have a huge time constraint.

    17. dubna 2013 9:48
  • There's no way around the SQL constraint (and I must say, I'm very suspicious of your table design). So use an artificial key, or drop one of the fields. Either way you're going to have to do the re-gens.


    Rebecca M. Riordan

    17. dubna 2013 11:01
  • Yikes... 16 is still the limit in 2012. Are there any multi-part keys that you could combine perhaps? Like if the key included year and month, combine the two?

    You could use is to put the combined columns in a computed column and make the PK use the combined column instead of the other.  Or you coudl you produce a logical key from all of the PK columns, doing some concatenation.

    I agree with Rebecca that this design scares me a bit, but I understand the problem is what it is.

    If you can post the DDL we might be able to suggest something.

    Of course, the alternative is to not have a phyically implemented PK, put an index on 16 of the columns and enforce uniqueness in a trigger... But that is clearly not helping your time crunch :)


    Louis

    Without good requirements, my advice is only guesses. Please don't hold it against me if my answer answers my interpretation of your questions.

    17. dubna 2013 19:41
    Moderátor
  • How about splitting the table into 2 tables like Parent, Child tables where Parent1 table stores 8 keys and Parent2 table stores 8 keys and both tables having a sequence key and then the Child table references those sequence keys in both parent tables. The child table stores all the non-key columns.

    OR

    How about creating a Hash key (Ex : MD5 - combination of all the 17 keys) and create an index on it ?


    Narsimha

    17. dubna 2013 22:32
  • Thank you everyone for your replies!

    After some testing and checking we found the ultimate answer here:

    Using Included Columns to Avoid Size Limits

    http://msdn.microsoft.com/en-us/library/ms190806(v=sql.105).aspx 

    If your reply meant the same thing, but in different wording, then I apologise for not accepting your answer, but please let me know.

    • Označen jako odpověď EDWZA 24. dubna 2013 8:33
    • Zrušeno označení jako odpověď Kalman TothModerator 25. dubna 2013 15:05
    24. dubna 2013 8:33
  • We need a table with a primary key with 17 fields, but SQL says the maximum is 16. 

    That is an awful idea. How do you JOIN to a table with 17 column PRIMARY KEY?

    Use instead INT IDENTITY SURROGATE PRIMARY KEY.

    INCLUDE column in an index is not the same as a KEY column.

    Post CREATE TABLE for quick assistance.  Thanks.


    Kalman Toth Database & OLAP Architect sqlusa.com
    New Book / Kindle: Pass SQL Exam 70-461 & Job Interview: Programming SQL Server 2012

    25. dubna 2013 15:09
    Moderátor
  • Nope, those two are different.

    The initial requests was to add 17 columns part of primary key and the above URL says about including columns so that the INDEX size can be reduced, not just columns.

    Moreover, if the table doesn't have a Clustered Index, adding a Primary key will create a clustered index which doesnt require/acceept the INCLUDE feature.



    Thanks
    Sarat

    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    26. dubna 2013 7:05
  • > adding a Primary key will create a clustered index which doesnt require/acceept the INCLUDE feature.

    PRIMARY KEY can create unique CI or unique NCI.


    Kalman Toth Database & OLAP Architect sqlusa.com
    New Book / Kindle: Pass SQL Exam 70-461 & Job Interview: Programming SQL Server 2012

    26. dubna 2013 9:23
    Moderátor
  • > adding a Primary key will create a clustered index which doesnt require/acceept the INCLUDE feature.

    PRIMARY KEY can create unique CI or unique NCI.


    Kalman Toth Database & OLAP Architect sqlusa.com
    New Book / Kindle: Pass SQL Exam 70-461 & Job Interview: Programming SQL Server 2012

    The statement is preceded with a condition "Moreover, if the table doesn't have a Clustered Index," . Hope this is correct( unless it is specified explicitly to go for NCI) , correct me if am wrong.



    Thanks
    Sarat

    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.


    26. dubna 2013 9:27