Over 4,500 characters...but my usual data type (nvarchar) is too small at 4,000...what do I use instead?

I usually use nvarchar to store text in my databases...however, I know need to store at least 4,500......and I add another 1,200 new chars per new month (stats collection and storage).

How can I store such large amount of data?  Which data type supports this, I suppose I will need 15,000... 10 months worth of growth...but still I need more...  I'll probably run this for at least another year...

...so how else can I store 15,000 to 30,000 characters?



TiA
LVL 3
XanderPAsked:
Who is Participating?
 
lluthienCommented:
you can either use the TEXT datatype..

or use multiple records
0
 
XanderPAuthor Commented:
I've tried Text..but it sets Length as 16...and I can't store data in it???
0
 
lluthienCommented:
text is an okay datatype if you just want to dump your data,

but if you want to add information to this data, like "when was it inserted" or sth like that,

you might want to consider inserting more than one record.

cheers
0
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

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.

 
lluthienCommented:
dont know the limit of text field exactly, but it is huge, the 16 is a 16 bit pointer to some data area i presume.

what do you mean by " i cant store data in it" ?

how did you try?
0
 
lluthienCommented:
try using an insert statement for it instead of using the enterprise manager, it should work
0
 
pcsentinelCommented:
since you use nvarchar you should probably use ntext which can contain up to 1,073,741,823 characters

The Length of 16 is as others have said mereley the length of the pointer in the record that points to the actual text area.

Data can be entered directly from any sql query

regards
0
 
arbertCommented:
"since you use nvarchar you should probably use ntext "

Unless you don't know what nvarchar and ntext are and you selected it by default--waste of space unless you need it.

Text might be a good option, but it can be difficult to update later.  You might think about breaking it down into multiple records and then piecing them together for display....

Brett
0
 
Anthony PerkinsCommented:
As arbert has alluded if you do not need to support unicode (double byte characters) than use varchar instead of nvarchar.  This will give you a mximum of 8000 characters.
0
 
doobdaveCommented:
Just an addition:

If you DO decide to use text or ntext data types, you will need to read up on the following functions:
READTEXT
WRITETEXT
UPDATETEXT
TEXTPTR
and probably DATALENGTH
Look them up in Books on line, they are used to read, write and update text column data.

It is a little tricky to work with, so you shuold avoid using this adta type unless you really have to.

As the others mentioned you can double the amount of data you can store by changing frmo nvarchar to varchar (one is unicode, the other is ansi) if you do not need to store any 'special' characters.

David
0
 
Anthony PerkinsCommented:
Split the points.
0
 
lluthienCommented:
i agree
0
 
XanderPAuthor Commented:
I didn't use this question's answers.  I choose to condense the amount of data that I wanted to store instead.
0
 
Anthony PerkinsCommented:
XanderP,

>>I didn't use this question's answers.<<
Than do yourself a favor and follow the EE Guidelines. See here:
http://www.experts-exchange.com/Databases/Microsoft_SQL_Server/help.jsp#hi70
Then a moderator can determine what is fair.

Otherwise, the points should be split.
0
 
XanderPAuthor Commented:
Fair enough...tho I think the points should be split since I only decided to rework my code based on this thread's advice!   I didn't use the advice to chage my datatype tho :)

anyway, thanks for the help guys :)    I will review the answers and split accordingly :)
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.