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?

[Webinar] Streamline your web hosting managementRegister Today

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: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone 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
All Courses

From novice to tech pro — start learning today.