SQL column ntext, nvarchar

We are designing a table and we need a column to handle 2000 to 5000 characters.  What is the best datatype for this column?  ntext, nvarchar(2000), nvarchar(5000).  We are using SQL 2005 and SQL 2008.
tampsystemsAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
Lee SavidgeConnect With a Mentor Commented:
That bit is up to you to be honest. Personally I would always limit the amount of text that a user can send to a database.

Read here for info on nvarchar(max) vs ntext

http://www.west-wind.com/weblog/posts/207.aspx

Lee
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
nvarchar(max) will do.
0
 
Lee SavidgeCommented:
I'd use nvarchar(max).

Lee
0
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
tampsystemsAuthor Commented:
that would allow for 4000 characters correct?
0
 
Lee SavidgeCommented:
It allows for 2gb
0
 
tampsystemsAuthor Commented:
so if we set a maxlength of 5000 characters i the UI we will be safe, and we would not have any perfomance issues?
0
 
Lee SavidgeCommented:
No, just set the column type to be nvarchar(max). You wouldn't be able to do nvarchar(5000) as this will exceed the row size limit.

Lee
0
 
tampsystemsAuthor Commented:
ok, but i should still limit the UI to restrict the entry of more then 5000 charaters, right?
0
 
tampsystemsAuthor Commented:
Can we fit 10,000 characters wth nvachar(max)?
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
yes, no problem. the "max" stands for 2GB of data, so 1 000 000 000 characters for Nvarchar
0
 
Guy Hengel [angelIII / a3]Connect With a Mentor Billing EngineerCommented:
actually, to be exact (2^31) -1
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.