CODEWITHSUNDEEP
sqlsql-serverperformance
Mehdi Esmaeili
Mehdi Esmaeili

Reputation: 876

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

What is the difference between varchar and varchar(50) in SQL Server ?

SELECT CAST(customer_id AS varchar) AS Expr1 FROM tbl_customer

VS

SELECT CAST(customer_id AS varchar(50)) AS Expr1 FROM tbl_customer

Upvotes: 3

Views: 5357

Answers (2)

Kritner
Kritner

Reputation: 13765

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

Upvotes: 8

Krishnraj Rana
Krishnraj Rana

Reputation: 6656

As per MSDN if no length is specified for varchar it will default to 30 when using CAST. If you have not specified length then SQL Server quietly truncates the value and can lead to unexpected bug.

Upvotes: 1

Related Questions