Home / server / Questions / 57450
Accepted
Dave Forgac
Asked: 2009-08-23 08:49:52 +0800 CST

Is is possible to change the ntext size limit in SQL Server 2005?

  • 772

Is it possible to change the size limit on ntext fields in SQL Server 2005 or must one convert the field to varchar(max)?

sql-server

3 Answers

  1. Best Answer

    The size limit on the CLOB/BLOB fields are fixed (n/text, and image) to 2,147,483,647 bytes. Those datatypes are deprecated as of SQL 2008 and probably won't be included in the next version.

    Their updated versions are the varchar(max), nvarchar(max), and varbinary(max) datatypes and they have the same limits. Note that the 'n' prefix in nvarchar and ntext means that Unicode characters are stored in the field, so the real translation from ntext should be to nvarchar(max).

    Hope that helps!

    Joe

    • 4

  2. You must convert to varchar(max). The limit is hard coded and cannot be controlled in any other way outside of a feature request to Microsoft.

    However I highly doubt it will be allowed since it is has been flagged for removal in future version of SQL Server as per MSDN here.

    • 2

  3. To add to what's already been said:

    • text/ntext/image won't be removed until at least SQL11 + 1 (i.e. they are definitely still there in the next major version of SQL Server - it was my team that deprecated them)
    • the limit for any of the LOB types will not be raised in the foreseeable future
    • checkout the FILESTREAM feature, which allows LOB values larger than 2GB to be stored in the filesystem, but rooted in the database and kept completely transactionally synchronized. I wrote a whitepaper on it for MS - see SQL Server 2008 FILESTREAM whitepaper published on MSDN.

    Hope this helps

    • 2