Why is Text and Ntext slow on MSSQL 2005

  • The other day, I got a complaint from a customer that whenever they had notes, things slowed down.
  • Upon investigation, I was using ntext data types for notes. This was a carry over from SQL Server 2000.
  • After changing the datatype from ntext to nvarchar the problem disappeared! Why? I don't know! I only know that text and ntext were deprecated on MSSQL 2005, and where there only for compatibility purposes to be replaced by varchar and nvarchar and will not be supported by SQL Server 2008.

Applies to:

  • Microsoft SQL Server 2005

Difference between Text and Ntext

  1. Text can be up to 8000 bytes long.
  2. ntext can be up to 4000 bytes long.
  3. ntext stands for Unicode text. Each character take 2 bytes, that why ntext only can be up to 4k bytes long.

Difference between varchar and Nvarchar

  1. varchar can be up to 2Gb long with the definition of varchar(max).
  2. nvarchar can be up to 1Gb long with the definition of nvarchar(max).
  3. nvarchar stands for Unicode varchar. Each character take 2 bytes, that why nvarchar(max) only can be up to 1Gb bytes long.

Optimization

  1. Converting from ntext to nvarchar improved the user experience since they were not limited to 4k notes anymore.
  2. Significant speed improvements from converting from ntext to nvarchar.
  • The speed improvements could also be due to the dropping of the table and reloading of the table.
  • I did not measure the fragmentation before doing the data conversion. See: How fragmented is the data with MSSQL 2005