Services
Technology Web App Development Mobile App Development Windows App Development Database Development UI/UX Development Cloud Development SEO Digital Marketing Guest Posting Content Writing Artificial Intelligence Data Science ERP Development CRM Development CMS Development LMS Development Business Listing Blockchain Technology Startup Promotion
Products
Data Converter Cleaner TUC - Unit Converter Import Export Survey Manager Tweet Controller
Developer
Article Blog Forum Interview Beginner News Startup Quiz
Users
Career
Apply Job Internship
Pricing
Login Sign Up Get a Quote

interview

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

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 .

Message

1 Answers

Anubhav Sharma
Anubhav Sharma 21 Aug 2025
Report Abuse

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