[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 720
  • Last Modified:

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.
0
tampsystems
Asked:
tampsystems
  • 4
  • 4
  • 3
2 Solutions
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
nvarchar(max) will do.
0
 
Lee SavidgeCommented:
I'd use nvarchar(max).

Lee
0
 
tampsystemsAuthor Commented:
that would allow for 4000 characters correct?
0
Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

 
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
 
Lee SavidgeCommented:
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
 
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]Billing EngineerCommented:
actually, to be exact (2^31) -1
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 4
  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now