Post reply

Varchar default length

  • October 31, 2007 at 1:08 am

    #62183

    Hi Experts,

    I am very confused about the default length for varchar datatype. I was read an article in our sqlservercentral.com regarding varchar and char default size. It says the default length for varchar and char is 30. But i have answered for some questions in 'QOD(Question of the Day)' yesterday.

    The same question is asked in the QOD part. I answered 30 , but it says, 'you are wrong', The default value is 1. How ?

    I dont know which one is correct. Can anybody explain me with proper example ?

    Regards:cool:

    Karthik

    karthik

  • October 31, 2007 at 1:53 am

    #746939

    The 'default' length of varchar depends on where it is used.

    In management studio, when creating a table the default length is 50.

    If you declare a variable of type varchar without specifying a length, the default length is 1

    If you cast to varchar without specifying a length, the default length is 30.

    See example

    DECLARE @test1 VARCHAR

    SET @test1 = '1234567890123456789012345678901234567890'

    SELECT @test1, LEN(@Test1)

    DECLARE @Test2 VARCHAR(100)

    SET @Test2 = '1234567890123456789012345678901234567890'

    SET @TEst2 = CAST(@Test2 AS VARCHAR)

    SELECT @Test2, LEN(@Test2)

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • October 31, 2007 at 1:59 am

    #746940

    Very good reply, thanks.

  • October 31, 2007 at 3:14 am

    #746959

    This behaviour is one of the reasons why you should never rely on default length. Errors caused by incorrect use of default length are often hard to find and truncation in variables happens silently, without any infomessages.

  • October 31, 2007 at 3:56 am

    #746970

    Please find this examples.

    The following example shows the default value of n is 1 for the char and varchar data types when they are used in variable declaration.

    DECLARE @myVariable AS varchar

    DECLARE @myNextVariable AS char

    SET @myVariable = 'abc'

    SET @myNextVariable = 'abc'

    --The following returns 1

    SELECT DATALENGTH(@myVariable), DATALENGTH(@myNextVariable);

    GO

    The following example shows that the default value of n is 30 when the char or varchar data types are used with the CAST and CONVERT functions.

    DECLARE @myVariable AS varchar(40)

    SET @myVariable = 'This string is longer than thirty characters'

    SELECT CAST(@myVariable AS varchar)

    SELECT DATALENGTH(CAST(@myVariable AS varchar)) AS 'VarcharDefaultLength';

    SELECT CONVERT(char, @myVariable)

    SELECT DATALENGTH(CONVERT(char, @myVariable)) AS 'VarcharDefaultLength';

Viewing 5 posts - 1 through 4 (of 4 total)

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