Default character limit for VARCHAR DataType

We use many time even mostly VARCHAR DataType in SQL Server. But some time we get issue or confusion with the default limit or character size of VARCHAR datatype, and that comes to the logical error with your queries or in your compile code (Stored Procedure) when execute it and gives the wrong output.

We will see how it works when we do not define size with VARCHAR datatype and give us the unexpected output.

See the below two scenarios how VARCHAR datatype works with query:

Scenario 1:

1. VARCHAR datatype With Variable

--Varchar used with variable

DECLARE @a VARCHAR

SET @a = 'Paresh'

SELECT @a AS Value

Output:

Now here in this scenario we can see it gives output as only 1 character because when we use VARCHAR DataType with variable and not define any size it takes only single character size default.

Scenario 2:

2. VARCHAR datatype with Convert and Cast function

--Varchar used with CONVERT or CAST Fucntion

SELECT CONVERT(VARCHAR,'aaaaa bbbbbb cccccc dddddd eeeee ffffff ggggg hhhhhh') AS Value

SELECT CAST('aaaaa bbbbbb cccccc dddddd eeeee ffffff ggggg hhhhhh' AS VARCHAR) AS Value

Output:

Here we can see that when VARCHAR DataType use with Convert or Cast function then it takes the 30 character as default size for it and give the output with only 30 characters.

  

So here we need to keep in mind that when we use VARCHAR Datatype then MUST give the size whatever it requires as per the use.