53

What is the best way to store a large amount of text in a table in SQL server?

Is varchar(max) reliable?

GEOCHET
  • 21,119
  • 15
  • 74
  • 98
Bruno
  • 4,337
  • 12
  • 42
  • 55

8 Answers8

77

In SQL 2005 and higher, VARCHAR(MAX) is indeed the preferred method. The TEXT type is still available, but primarily for backward compatibility with SQL 2000 and lower.

John Rudy
  • 37,282
  • 14
  • 64
  • 100
  • How do I write the query for large text? `INSERT INTO VALUES(id, other fields, '');`? Or insert the other fields in first then update the entry for the large text field? – Zhang Jul 01 '23 at 01:15
20

I like using VARCHAR(MAX) (or actually NVARCHAR) because it works like a standard VARCHAR field. Since it's introduction, I use it rather than TEXT fields whenever possible.

Stephen Wrighton
  • 36,783
  • 6
  • 67
  • 86
6

Varchar(max) is available only in SQL 2005 or later. This will store up to 2GB and can be treated as a regular varchar. Before SQL 2005, use the "text" type.

Instantsoup
  • 14,825
  • 5
  • 34
  • 41
3

According to the text found here, varbinary(max) is the way to go. You'll be able to store approximately 2GB of data.

Huuuze
  • 15,528
  • 25
  • 72
  • 91
3

Split the text into chunks that your database can actually handle. And, put the split up text in another table. Use the id from the text_chunk table as text_chunk_id in your original table. You might want another column in your table to keep text that fits within your largest text data type.

CREATE TABLE text_chunk (
     id NUMBER,
     chunk_sequence NUMBER,
     text BIGTEXT)
Mark Stock
  • 1,713
  • 2
  • 13
  • 23
  • 7
    This seems very heavy weight; some context as to when this technique is required or appropriate vs nvarchar, and what the costs/benefits are would improve the quality of the asnwer. – SAJ14SAJ Apr 26 '13 at 12:49
  • I guess when a text files goes above 2Go you need to split it ? – Jorgu Jul 22 '21 at 19:50
2

In a BLOB

BLOBs are very large variable binary or character data, typically documents (.txt, .doc) and pictures (.jpeg, .gif, .bmp), which can be stored in a database. In SQL Server, BLOBs can be text, ntext, or image data type, you can use the text type

text

Variable-length non-Unicode data, stored in the code page of the server, with a maximum length of 231 - 1 (2,147,483,647) characters.

Paul Whelan
  • 16,574
  • 12
  • 50
  • 83
1

Depending on your situation, a design alternative to consider is saving them as .txt file to server and save the file path to your database.

Jeff
  • 8,020
  • 34
  • 99
  • 157
Great Efue
  • 90
  • 5
0

Use nvarchar(max) to store the whole chat conversation thread in a single record. Each individual text message (or block) is identified in the content text by inserting markers.

Example:

{{UserId: Date and time}}<Chat Text>.

On display time UI should be intelligent enough to understand this markers and display it correctly. This way one record should suffice for a single conversation as long as size limit is not reached.

Stephen Rauch
  • 47,830
  • 31
  • 106
  • 135
user9018039
  • 1
  • 1