What problems can an NVARCHAR(3000) cause

Question

I have an already large table that my clients are asking for me to extend the length of the notes field. The notes field is already an NVARCHAR(1000) and I am being asked to expand it to 3000. The long term solution is to move notes out of the table and create a notes table that uses an NVARCHAR(max) field that is only joined in when necessary. My question is about the short term. Knowing that this field will be moved out in the future what problems could I have if I just increase the field to an NVARCHAR(3000) for now?

Answer 1

You probably already know this, but just make sure that the increased length doesn't drive your total record length over 8000. I pretty sure that still applies for 2005/2008.

Answer 2

You should be fine with nvarchar(3000) for the interim solution. You can go up to a maximum of nvarchar(4000). And as posted earlier by km.srd.myopenid.com, make sure that the entire length of your row doesn't exceed 8000 (remember that nvarchar is 2x the size of a regular varchar - which is why you can only have nvarchar(4000), but you can have varchar(8000)).

Answer 3

why not use a "text" field?

Answer 4

I would suggest changing the column to NTEXT. You will have virtually no limit on the amount of data and the data is not stored with the rest of the row data. This helps keep you from hitting the maximum row size limit.

The only drawback is that you can only perform "LIKE" searches on that column and you cannot index it. However, if it's a notes field, my guess is that you are not doing any searching on it at all.

Answer 5

text and ntext are deprecated in favor of varchar(max) and nvarchar(max). So nvarchar(3000) should be fine.

Answer 6

You may also experience more slowness as your data pages may get split up to accomodate the larger field. You can create a structure that allows a record of more than 8060 bytes by doing this but be aware if you try to add a data record that actually contains more than that you will have a problem.