Deprecated SQL Types and Replacements

Deprecated and older SQL Server data types

1. TEXT → VARCHAR(MAX)

TEXT stores large non-Unicode text but is deprecated.

-- Old
Description TEXT

-- Recommended
Description VARCHAR(MAX)

VARCHAR(MAX) supports up to approximately 2 GB and works with normal string functions such as LEN, REPLACE and SUBSTRING.

2. NTEXT → NVARCHAR(MAX)

NTEXT stores large Unicode text but is deprecated.

-- Old
ArticleContent NTEXT

-- Recommended
ArticleContent NVARCHAR(MAX)

Use NVARCHAR(MAX) for multilingual text such as Tamil, Hindi or Arabic.

INSERT INTO Articles(ArticleContent)
VALUES (N'தமிழ் கட்டுரை');

3. IMAGE → VARBINARY(MAX)

Despite its name, IMAGE stores any large binary content—not only pictures. It is deprecated.

-- Old
FileContent IMAGE

-- Recommended
FileContent VARBINARY(MAX)

Use VARBINARY(MAX) for:

• Images

• PDF documents

• Audio files

• Other binary content

For very large files, storing them outside the database or using SQL Server FILESTREAM may be more appropriate.

4. TIMESTAMP → ROWVERSION

In SQL Server, TIMESTAMP does not store a date or time. It is a deprecated synonym for ROWVERSION.

-- Old
Version TIMESTAMP

-- Recommended
Version ROWVERSION

ROWVERSION is an automatically generated 8-byte binary value that changes whenever the row is inserted or updated.

CREATE TABLE Products
(
    ProductId INT PRIMARY KEY,
    Price     DECIMAL(10,2),
    Version   ROWVERSION
);

It is commonly used for optimistic concurrency:

UPDATE Products
SET Price = 500
WHERE ProductId = 1
  AND Version = @OriginalVersion;

Use DATETIME2 if you need the actual date and time. TIMESTAMP is a deprecated synonym for ROWVERSION.

5. DATETIME → Prefer DATETIME2

DATETIME is not officially deprecated, but Microsoft recommends avoiding it for new development.

-- Older choice
CreatedAt DATETIME

-- Recommended
CreatedAt DATETIME2(3)

DATETIME2 provides:

• Wider range: year 0001 to 9999

• Better fractional-second precision

• Configurable precision

• Equal or lower storage depending on precision

• Better alignment with SQL standards

CreatedAt DATETIME2(3) DEFAULT SYSDATETIME()

Use DATETIMEOFFSET instead when the UTC offset must also be preserved. Microsoft recommends modern date/time types for new work.

Migration example

ALTER TABLE Articles
ALTER COLUMN Content NVARCHAR(MAX);

ALTER TABLE Documents
ALTER COLUMN FileContent VARBINARY(MAX);

ALTER TABLE Orders
ALTER COLUMN CreatedAt DATETIME2(3);

Before changing an existing production column:

• Check application and ORM mappings.

• Check stored procedures, views and functions.

• Test data conversion.

• Review indexes and dependencies.

• Plan for locking, transaction-log growth and downtime on large tables.

Key interview points

• TEXT, NTEXT and IMAGE are deprecated.

• Replace them with VARCHAR(MAX), NVARCHAR(MAX) and VARBINARY(MAX).

• TIMESTAMP is not a date/time value; use ROWVERSION.

• DATETIME is not deprecated, but DATETIME2 is preferred for new development.

• Deprecated types may still work, but they should not be used in new designs.

• Do not automatically use MAX; choose a specific length when the data has a known limit.