String data types

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);