String data types#
Stores strings of letters, numbers, and symbols.
Character data can be stored as fixed-length or variable-length strings. Fixed-length strings are right-extended with spaces on output; variable-length strings are not extended.
Character strings:
char
varchar
text
Unicode character strings:
nchar
nvarchar
ntext
Binary strings:
binary
varbinary
image
binary and varbinary#
Binary data types of either fixed length or variable length.
Arguments:
binary [ ( n ) ] Fixed-length binary data with a length of n bytes, where n is a value from 1 through 8,000. The storage size is n bytes.
varbinary [ ( n | max) ] Variable-length binary 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 the data entered + 2 bytes. The data that is entered can be 0 bytes in length.
Example:
SELECT CAST( 123456 AS BINARY(4) );
char and varchar#
Are string data types of either fixed length or variable length.
Example:
DECLARE @myVariable AS varchar = 'xyz';
SELECT @myVariable as aVarchar;
nchar and nvarchar#
Character data types that are either fixed-length, nchar, or variable-length, nvarchar, Unicode data and use the UNICODE UCS-2 character set.
Arguments:
nchar [ ( n ) ] Fixed-length Unicode string data. n defines the string length and must be a value from 1 through 4,000. The storage size is two times n bytes. When the collation code page uses double-byte characters, the storage size is still n bytes. Depending on the string, the storage size of n bytes can be less than the value specified for n.
nvarchar [ ( n | max ) ] Variable-length Unicode string data. n defines the string length and can be a value from 1 through 4,000. max indicates that the maximum storage size is 2^31-1 bytes (2 GB). The storage size, in bytes, is two times the actual length of data entered + 2 bytes.
Example:
CREATE TABLE dbo.MyTable
(
MyNCharColumn nchar(15)
, MyNVarCharColumn nvarchar(20)
);
INSERT INTO dbo.MyTable VALUES (N'Test data', N'More test data');
SELECT MyNCharColumn, MyNVarCharColumn
FROM dbo.MyTable;
ntext, text, and image#
Fixed and variable-length data types for storing large non-Unicode and Unicode character and binary data. Unicode data uses the UNICODE UCS-2 character set.
Arguments:
ntext Variable-length Unicode data with a maximum string length of 2^30 - 1 (1,073,741,823) characters. Storage size, in bytes, is two times the string length that is entered.
text Variable-length non-Unicode data in the code page of the server and with a maximum string length of 2^31-1 (2,147,483,647). When the server code page uses double-byte characters, the storage is still 2,147,483,647 bytes. Depending on the character string, the storage size may be less than 2,147,483,647 bytes.
image Variable-length binary data from 0 through 2^31-1 (2,147,483,647) bytes.
Example:
SELECT cast('Lorem ipsum dolor sit amet...' as ntext);