CODEWITHSUNDEEP
sql-server
Bruno
Bruno

Reputation: 4507

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

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

Is varchar(max) reliable?

Upvotes: 56

Views: 139081

Answers (8)

Great Efue
Great Efue

Reputation: 110

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.

Upvotes: 1

user9018039
user9018039

Reputation: 1

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.

Upvotes: 0

Mark Stock
Mark Stock

Reputation: 1733

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)

Upvotes: 3

Instantsoup
Instantsoup

Reputation: 15265

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.

Upvotes: 6

Huuuze
Huuuze

Reputation: 16357

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

Upvotes: 3

Stephen Wrighton
Stephen Wrighton

Reputation: 37819

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.

Upvotes: 20

Paul Whelan
Paul Whelan

Reputation: 16809

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.

Upvotes: 3

John Rudy
John Rudy

Reputation: 37850

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.

Upvotes: 80

Related Questions