CHAR, NCHAR, VARCHAR and NVARCHAR default length


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:

CHAR, NCHAR, VARCHAR and NVARCHAR default length, if not specified

CHAR, NCHAR, VARCHAR and NVARCHAR default length, if not specified

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.

Rate This
[Total: 1    Average: 4/5]

Gopal Krishna Ranjan

About Gopal Krishna Ranjan

I am Gopal Krishna Ranjan, having 6+ years of industry experience in Software development using Microsoft technologies. I have a head down experience in Database development, performance tuning in SQL Server, T-SQL optimization, BI (Business Intelligence) project implementation, reporting in SSRS, using SSIS for ETL, implementing multi dimensional and tabular data-warehouse in SSAS, querying cubes using MDX and DAX, Windows and Web Applications development with C#.

Leave a comment

Your email address will not be published. Required fields are marked *

*