char and varchar (Transact-SQL)
Are character data types of either fixed length or variable length.
- char [ ( n ) ]
Fixed-length, non-Unicode character data with a length of n bytes. n must be a value from 1 through 8,000. The storage size is n bytes. The SQL-2003 synonym for char is character.
- varchar [ ( n | max ) ]
Variable-length, non-Unicode character data. n can be a value from 1 through 8,000. max indicates that the maximum storage size is 2^31-1 bytes. The storage size is the actual length of data entered + 2 bytes. The data entered can be 0 characters in length. The SQL-2003 synonyms for varchar are char varying or character varying.
Remarks
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.
Examples
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';
See Also
Reference
nchar and nvarchar (Transact-SQL)
CAST and CONVERT (Transact-SQL)
COLLATE (Transact-SQL)
Data Types (Transact-SQL)
sp_dbcmptlevel (Transact-SQL)
Other Resources
Selecting Collations
Data Type Conversion (Database Engine)
Using char and varchar Data
Using Unicode Data
Estimating the Size of a Database