SQL Server TEXT Data Type

TEXT is a data type in SQL Server used to store text data with a maximum length of 2GB.

Syntax

The following syntax can be used to specify the TEXT data type when creating or altering a table structure:

column_name TEXT [NULL | NOT NULL]

Use cases

Some use cases for the TEXT data type include:

Storing large amounts of text data, such as long articles or logs.
Storing binary data, such as image or audio files. Binary data can be converted to text data encoded in base64 and then stored in a TEXT field.

It should be noted that the TEXT data type is deprecated and not recommended for use in new database applications. SQL Server recommends using the VARCHAR(MAX) or NVARCHAR(MAX) data types as replacements for the TEXT data type.

Examples

Here are two examples of using the TEXT data type.

Example 1

Create a table called products with two fields, id and description, where description is of TEXT data type:

CREATE TABLE products (
  id INT PRIMARY KEY,
  description TEXT
);

Insert a record with a long article in the description field:

INSERT INTO products (id, description)
VALUES (1, 'This is a long article with many paragraphs and sentences.');

Select and return the description field from the products table:

SELECT description
FROM products
WHERE id = 1;

The result should be:

This is a long article with many paragraphs and sentences.

Example 2

Create a table called images with two fields, id and data, where data is of TEXT data type:

CREATE TABLE images (
  id INT PRIMARY KEY,
  data TEXT
);

Convert an image file to base64-encoded text data and insert it into the images table:

INSERT INTO images (id, data)
VALUES (1, 'iVBORw0KGgoAAAANSUhEUgAAAAEAAAABCAYAAAAfFcSJAAAADUlEQVR42mP8z/C/PwAI6gMz5PK5mAAAAABJRU5ErkJggg==');

Select and return the data field from the images table:

SELECT data
FROM images
WHERE id = 1;