Solved

SQL column  ntext, nvarchar

Posted on 2010-09-08
11
708 Views
Last Modified: 2012-05-10
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
Comment
Question by:tampsystems
  • 4
  • 4
  • 3
11 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 33627471
nvarchar(max) will do.
0
 
LVL 25

Expert Comment

by:Lee Savidge
ID: 33627475
I'd use nvarchar(max).

Lee
0
 

Author Comment

by:tampsystems
ID: 33627521
that would allow for 4000 characters correct?
0
Edgartown IT Case Study

Learn about Edgartown's quest to ensure the safety and security of the entire town's employee and citizen data. Read the case study!

 
LVL 25

Expert Comment

by:Lee Savidge
ID: 33627535
It allows for 2gb
0
 

Author Comment

by:tampsystems
ID: 33627549
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
 
LVL 25

Expert Comment

by:Lee Savidge
ID: 33627561
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
 

Author Comment

by:tampsystems
ID: 33627577
ok, but i should still limit the UI to restrict the entry of more then 5000 charaters, right?
0
 
LVL 25

Accepted Solution

by:
Lee Savidge earned 250 total points
ID: 33627601
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
 

Author Comment

by:tampsystems
ID: 33628422
Can we fit 10,000 characters wth nvachar(max)?
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 33633972
yes, no problem. the "max" stands for 2GB of data, so 1 000 000 000 characters for Nvarchar
0
 
LVL 143

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 250 total points
ID: 33633977
actually, to be exact (2^31) -1
0

Featured Post

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
database level memory cache..? 8 37
Job - date manual 1 42
SQL - Curser to do an insert based on a select 2 26
T-SQL: I need to add an index on a field 5 26
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…
Are you ready to implement Active Directory best practices without reading 300+ pages? You're in luck. In this webinar hosted by Skyport Systems, you gain insight into Microsoft's latest comprehensive guide, with tips on the best and easiest way…

749 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question