Avoid Using Text and Ntext Data Types in Microsoft SQL Server

Provided by Molly Pell, Senior Systems Analyst

The text data type has several inherent problems, including:

1. You cannot directly write or update text data using the INSERT or UPDATE statements. Instead, you have to use special statements like READTEXT, WRITETEXT and UPDATETEXT.
2. There are several known issues associated with replicating tables that contain text columns.
3. Because of the way SQL Server stores text columns, it can take more time to retrieve text/ntext values.
4. Text data types have extra overhead that can negatively impact performance.

If you don't have to store more than 8KB of text, consider using char(8000) or varchar(8000) data types instead of text and ntext.

This tip and more found by Total SQL Analyzer PRO.