1

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

3 Answers 3

4

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

3
  • I'm trying to help someone else with their server (I try to stay away from SQL server in general). They are reporting that data larger than 4000 characters is being truncated when inserted into a ntext field. Any idea why? Aug 22, 2009 at 18:17
  • It looks like they're using the text-in-row which is limited to 8000 bytes: msdn.microsoft.com/en-us/library/ms173530.aspx Working on convincing them to convert to nvarchar(max). Aug 22, 2009 at 22:12
  • The 4000 characters might be a setting in your SQL client: stackoverflow.com/questions/3760802/… Dec 28, 2012 at 17:20
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

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

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .