[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

SQL column  ntext, nvarchar

Posted on 2010-09-08
11
Medium Priority
?
713 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 
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 1000 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 1000 total points
ID: 33633977
actually, to be exact (2^31) -1
0

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Question has a verified solution.

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

INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Video by: ITPro.TV
In this episode Don builds upon the troubleshooting techniques by demonstrating how to properly monitor a vSphere deployment to detect problems before they occur. He begins the show using tools found within the vSphere suite as ends the show demonst…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

649 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