locked
Primary Key field size RRS feed

  • Question

  • Say a Primary key was defined as a varchar(20). What happens when a record is encountered and the value exceeds the defined value? Will SQL server pop up with a key violation?
    Friday, June 13, 2014 7:33 PM

Answers

  • There should be a warning about 'string or binary data truncated'. If you're running with SET ANSI_WARNINGS OFF (you should not), then you'll get an error if that truncated value already exists in the table, otherwise the value will be inserted OK.

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


    My blog


    My TechNet articles

    • Proposed as answer by SQLZealotsEditor Friday, June 13, 2014 7:47 PM
    • Marked as answer by tracycai Wednesday, June 25, 2014 1:30 AM
    Friday, June 13, 2014 7:37 PM
    Answerer

All replies

  • There should be a warning about 'string or binary data truncated'. If you're running with SET ANSI_WARNINGS OFF (you should not), then you'll get an error if that truncated value already exists in the table, otherwise the value will be inserted OK.

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


    My blog


    My TechNet articles

    • Proposed as answer by SQLZealotsEditor Friday, June 13, 2014 7:47 PM
    • Marked as answer by tracycai Wednesday, June 25, 2014 1:30 AM
    Friday, June 13, 2014 7:37 PM
    Answerer
  • Try the below:

    create table #outertable (Iid varchar(20) primary key,[Server] varchar(50),DbName varchar(50)) Insert into #outertable Values('200000000000000000000','s2','d2') Drop table #outertable

    /*

    Msg 8152, Level 16, State 14, Line 2
    String or binary data would be truncated.
    The statement has been terminated.

    */


    Friday, June 13, 2014 7:48 PM
    Answerer
  • There will be more than a warning:

    declare @test table (id varchar(10) primary key);
    insert @test (id) values ('test');
    insert @test (id) values ('123456789012345678901');
    select * from @test;

    Friday, June 13, 2014 7:50 PM
  • What do you mean?

    I only got

    (1 row(s) affected)
    Msg 8152, Level 16, State 14, Line 3
    String or binary data would be truncated.
    The statement has been terminated.

    (1 row(s) affected)

    Also, it worked fine with this setting (although we should not use it): 

    SET ANSI_WARNINGS OFF
    
    declare @test table (id varchar(10) primary key); 
    insert @test (id) values ('test'); 
    insert @test (id) values ('123456789012345678901'); 
    select * from @test;


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


    My blog


    My TechNet articles


    Friday, June 13, 2014 7:54 PM
    Answerer
  • Hello Naomi,

    Actually its an error message (cancelling the operation, rollback and returned the error message) and more than a warning right?

    Friday, June 13, 2014 8:08 PM
    Answerer
  • Exactly - the row that "overflows" does not get inserted.  A misnomer of a connection setting.
    Friday, June 13, 2014 8:12 PM
  • Thanks for your example!
    Tuesday, July 1, 2014 1:56 AM