char and varchar (Transact-SQL)
Are character data types of either fixed length or variable length.
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.
Objects that use char or varchar are assigned the default collation of the database, unless a specific collation is assigned using the COLLATE clause. The collation controls the code page that is used to store the character data.
If you have sites that support multiple languages, consider using the Unicode nchar or nvarchar data types to minimize character conversion issues. If you use char or varchar, we recommend the following:
Use char when the sizes of the column data entries are consistent.
Use varchar when the sizes of the column data entries vary considerably.
Use varchar(max) when the sizes of the column data entries vary considerably, and the size might exceed 8,000 bytes.
If SET ANSI_PADDING is OFF when either CREATE TABLE or ALTER TABLE is executed, a char column that is defined as NULL is handled as varchar.
When the collation code page uses double-byte characters, the storage size is still n bytes. Depending on the character string, the storage size of n bytes can be less than n characters.
A. Showing the default value of n when used in variable declaration.
The following example shows the default value of n is 1 for the char and varchar data types when they are used in variable declaration.
DECLARE @myVariable AS varchar DECLARE @myNextVariable AS char SET @myVariable = 'abc' SET @myNextVariable = 'abc' --The following returns 1 SELECT DATALENGTH(@myVariable), DATALENGTH(@myNextVariable); GO
B. Showing the default value of n when varchar is used with CAST and CONVERT.
The following example shows that the default value of n is 30 when the char or varchar data types are used with the CAST and CONVERT functions.
DECLARE @myVariable AS varchar(40) SET @myVariable = 'This string is longer than thirty characters' SELECT CAST(@myVariable AS varchar) SELECT DATALENGTH(CAST(@myVariable AS varchar)) AS 'VarcharDefaultLength'; SELECT CONVERT(char, @myVariable) SELECT DATALENGTH(CONVERT(char, @myVariable)) AS 'VarcharDefaultLength';