What is the default length of CHAR, NCHAR, VARCHAR and NVARCHAR when we don’t specify their length in their length argument? If we omit the size of CHAR, NCHAR, VARCHAR and NVARCHAR, how they behave? In this post “CHAR, NCHAR, VARCHAR and NVARCHAR default length”, we are going to discuss the default length of CHAR, NCHAR, VARCHAR and NVARCHAR data types, if length is not specified in their argument.
I would strongly suggest to define the length of CHAR, NCHAR, VARCHAR and NVARCHAR data types as per the need. In this post, I just explain the behavior of CHAR, NCHAR, VARCHAR and NVARCHAR data types, if length is omitted, and not encouraging to omit the size of these data types.
We can define these data types as below:
char [ ( n ) ]
nchar [ ( n ) ]
varchar [ ( n | max ) ]
nvarchar [ ( n | max ) ]
CHAR, NCHAR, VARCHAR and NVARCHAR default length, if “n” is not specified
In case we omit the argument “n“, default length of these data types varies depending on whether they used in variable declaration and data definition or inside a CAST and CONVERT function.
When “n” is not specified, in variable and column declaration, it defaults to 1, but inside a CAST and CONVERT functions, it defaults to 30. This is true for above all data types i.e. CHAR, NCHAR, VARCHAR and NVARCHAR. See below table:
Default length demo script – Variable declaration
DECLARE @CHARWithoutN CHAR = 'ABC' DECLARE @NCHARWithoutN NCHAR = 'ABC' DECLARE @VARCHARWithoutN VARCHAR = 'ABC' DECLARE @NVARCHARWithoutN NVARCHAR = 'ABC' PRINT 'Value of @CHARWithoutN is: ' + @CHARWithoutN PRINT 'Value of @NCHARWithoutN is: ' + @NCHARWithoutN PRINT 'Value of @VARCHARWithoutN is: ' + @VARCHARWithoutN PRINT 'Value of @NVARCHARWithoutN is: ' + @NVARCHARWithoutN
Output:
Value of @CHARWithoutN is: A
Value of @NCHARWithoutN is: A
Value of @VARCHARWithoutN is: A
Value of @NVARCHARWithoutN is: A
In above all cases, there are only 1 character in the output
Default length demo script – Column declaration
CREATE TABLE #Tbl
(
CHARWithoutN CHAR,
NCHARWithoutN NCHAR,
VARCHARWithoutN VARCHAR,
NVARCHARWithoutN NVARCHAR
)
--Inserting only 1 character in all columns
INSERT INTO #Tbl(CHARWithoutN, NCHARWithoutN, VARCHARWithoutN, NVARCHARWithoutN)
VALUES('A', 'A', 'A', 'A')
--Inserted successfully
--Inserting more than 1 character in below insert
INSERT INTO #Tbl(CHARWithoutN, NCHARWithoutN, VARCHARWithoutN, NVARCHARWithoutN)
VALUES('ABC', 'ABC', 'ABC', 'ABC')
--Above insert statement fails as it has more than 1 character to insert in these columns
--Msg 8152, Level 16, State 14, Line 26
--String or binary data would be truncated.
--The statement has been terminated.
SELECT 'CHARWithoutN is: ' + CHARWithoutN + ', NCHARWithoutN is: ' + NCHARWithoutN + ', VARCHARWithoutN is: ' + VARCHARWithoutN + ', NVARCHARWithoutN is: ' + NVARCHARWithoutN FROM #Tbl
Output:
CHARWithoutN is: A, NCHARWithoutN is: A, VARCHARWithoutN is: A, NVARCHARWithoutN is: A
Default length demo script – CAST function
PRINT 'In case of CAST, For CHAR, Value is: ' + CAST('ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789' AS CHAR)
PRINT 'In case of CAST, For NCHAR, Value is: ' + CAST('ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789' AS NCHAR)
PRINT 'In case of CAST, For VARCHAR, Value is: ' + CAST('ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789' AS VARCHAR)
PRINT 'In case of CAST, For NVARCHAR, Value is: ' + CAST('ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789' AS NVARCHAR)
Output:
In case of CAST, For CHAR, Value is: ABCDEFGHIJKLMNOPQRSTUVWXYZ0123
In case of CAST, For NCHAR, Value is: ABCDEFGHIJKLMNOPQRSTUVWXYZ0123
In case of CAST, For VARCHAR, Value is: ABCDEFGHIJKLMNOPQRSTUVWXYZ0123
In case of CAST, For NVARCHAR, Value is: ABCDEFGHIJKLMNOPQRSTUVWXYZ0123
In above all cases, there are only 30 characters in output.
Default length demo script – CONVERT function
PRINT 'In case of CONVERT, For CHAR, Value is: ' + CONVERT(CHAR, 'ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789') PRINT 'In case of CONVERT, For NCHAR, Value is: ' + CONVERT(NCHAR, 'ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789') PRINT 'In case of CONVERT, For VARCHAR, Value is: ' + CONVERT(VARCHAR, 'ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789') PRINT 'In case of CONVERT, For NVARCHAR, Value is: ' + CONVERT(NVARCHAR, 'ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789')
Output:
In case of CONVERT, For CHAR, Value is: ABCDEFGHIJKLMNOPQRSTUVWXYZ0123
In case of CONVERT, For NCHAR, Value is: ABCDEFGHIJKLMNOPQRSTUVWXYZ0123
In case of CONVERT, For VARCHAR, Value is: ABCDEFGHIJKLMNOPQRSTUVWXYZ0123
In case of CONVERT, For NVARCHAR, Value is: ABCDEFGHIJKLMNOPQRSTUVWXYZ0123
In above all cases, there are only 30 characters in output.
References:
For char and varchar: https://msdn.microsoft.com/en-us/library/ms176089.aspx (See remarks section).
For nchar and nvarchar: https://msdn.microsoft.com/en-us/library/ms186939.aspx (See remarks section).
Thank you for your reading. Hope, you have enjoyed this reading. Your feedback is valuable for us, don’t forget to give your feedback in comment section. Also, please rate and share this post.
