interview

home / developersection / interviews / what is the difference between text and varchar(max) in sql server?

Ask Question
Anubhav Kumar

Student

The Anubhav portal was launched in March 2015 at the behest of the Hon'ble Prime Minister for retiring government officials to leave a record of their experiences while in Govt service .

Follow
Can you answer this question?
Answer

1 Answers

Anubhav Kumar

Anubhav Kumar

21-Aug-2025

Note

  • TEXT → Deprecated, used for very large text (up to 2GB).
  • VARCHAR(MAX) → Modern replacement, supports up to 2^31-1 characters, can be used like VARCHAR.

Difference between TEXT and VARCHAR(MAX) in SQL Server

Deprecation

  • TEXT, NTEXT, and IMAGE types are deprecated (Microsoft recommends avoiding them).
  • VARCHAR(MAX) (and NVARCHAR(MAX), VARBINARY(MAX)) are the modern replacements.

Storage

  • TEXT stores data outside the row (in LOB storage) with only a 16-byte pointer in the row.
  • VARCHAR(MAX) can store data in-row (up to 8,000 bytes) and moves to LOB storage only if data exceeds that.

Size Limit

  • Both can store up to 2 GB of data (2^31-1 characters for VARCHAR(MAX)).

Functionality

  • TEXT has limited support: cannot be used with string functions (REPLACE, SUBSTRING, LEN, etc.) directly without special workarounds.
  • VARCHAR(MAX) behaves like a regular VARCHAR and supports all string functions.

Performance

  • VARCHAR(MAX) is generally faster and integrates better with modern SQL Server features.
  • TEXT is slower due to pointer lookups and restricted operations.

Best Practice: 

Always use VARCHAR(MAX) (or NVARCHAR(MAX) for Unicode) instead of TEXT.

advertising
advertising

Liked By

We use cookies to ensure you have the best browsing experience on our website. By using our site, you acknowledge that you have read and understood our Cookie Policy & Privacy Policy