Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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

share|improve this question

3 Answers 3

up vote 4 down vote accepted

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

share|improve this answer
    
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? –  Dave Forgac Aug 22 '09 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). –  Dave Forgac Aug 22 '09 at 22:12
    
The 4000 characters might be a setting in your SQL client: stackoverflow.com/questions/3760802/… –  Nicholas Tolley Cottrell Dec 28 '12 at 17:20

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.

share|improve this answer

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

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.