Why Is 30 the Default Length for Varchar When Using Cast

Why is 30 the default length for VARCHAR when using CAST?

Why don't you specify the varchar length? ie:

SELECT CAST('the quick brown fox jumped over the lazy dog' AS VARCHAR(45))

As far as why 30, that's the default length in SQL Server for that type.

From char and varchar (Transact-SQL):

When n is not specified in a data definition or variable declaration statement, the default length is 1. When n is not specified when using the CAST and CONVERT functions, the default length is 30.

Why does casting text as varchar without specifying a length truncate the text at 30 characters?

Why is 30 the default length for VARCHAR when using CAST?

difference between varchar and varchar(50) in CAST expression?

Varchar by default is created with a length of 30 if no length is provided.

See:

declare @test varchar(100)
select @test = '1234567890123456789012345678901234567890'

SELECT CAST(@test AS varchar) as resultDefaultLength, 
    CAST(@test AS varchar(50)) as           resultSpecifiedLength

Output:

resultDefaultLength resultSpecifiedLength
123456789012345678901234567890  1234567890123456789012345678901234567890

As long as your customer_ids aren't longer than 30 characters, there will be no difference.

EDIT:

as jpw pointed out:

This is true in the context of CAST/CONVERT, but otherwise the default
length is 1 char. Docs: When n is not specified in a data definition
or variable declaration statement, the default length is 1. When n is
not specified when using the CAST and CONVERT functions, the default
length is 30.

e.g. When declaring a varchar (and potentially other things?), if no length is given, it will have a length of 1. Taking the example from above and modifying slightly:

declare @test varchar
select @test = '1234567890123456789012345678901234567890'

SELECT CAST(@test AS varchar) as resultDefaultLength, 
    CAST(@test AS varchar(50)) as           resultSpecifiedLength

Output:

resultDefaultLength resultSpecifiedLength
1   1

varChar and Char behave different on cast

VarChar always adjusts to the length of the string passed. hence the output is 4.

CHAR has default length of 30 and hence the output is 30.

varchar(max) is truncating the string when set in a variable

The SQL function PRINT will only show a maximum of 8000 char, the variable still has the full content. Try running this:

PRINT LEN(@sql) 

You should see a much larger number.

What is the effect of omitting size in nvarchar declaration

If you omit the size, it defaults to 30. See this:

http://msdn.microsoft.com/en-us/library/ms186939.aspx

To see this in action, try executing the following statements:

--outputs: 12345678901234567890.098765432 
select cast (12345678901234567890.098765432 as nvarchar)

--throws "Arithmetic overflow error converting expression to data type nvarchar."
select cast (12345678901234567890.0987654321 as nvarchar) 

--outputs: 12345678901234567890.0987654321 
select cast (12345678901234567890.0987654321 as nvarchar(31))

Per @krul's comment; 30 is the default length for CAST; however the default length for a data definition or variable declaration is 1.

NB: There's also an STR function which converts numeric fields to strings, for which the default length is 10.

--outputs: 1234567890
select str(1234567890)

--outputs: **********
select str(12345678901)

--outputs: 12345678901
select str(12345678901,11)

---

Related Topics