?
Solved

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

Posted on 2005-05-04
15
Medium Priority
?
606 Views
Last Modified: 2012-06-27
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
0
Comment
Question by:XanderP
  • 5
  • 3
  • 3
  • +3
14 Comments
 
LVL 11

Accepted Solution

by:
lluthien earned 100 total points
ID: 13925231
you can either use the TEXT datatype..

or use multiple records
0
 
LVL 3

Author Comment

by:XanderP
ID: 13925240
I've tried Text..but it sets Length as 16...and I can't store data in it???
0
 
LVL 11

Expert Comment

by:lluthien
ID: 13925241
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
NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

 
LVL 11

Expert Comment

by:lluthien
ID: 13925250
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
 
LVL 11

Expert Comment

by:lluthien
ID: 13925275
try using an insert statement for it instead of using the enterprise manager, it should work
0
 
LVL 11

Assisted Solution

by:pcsentinel
pcsentinel earned 100 total points
ID: 13925448
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
 
LVL 34

Assisted Solution

by:arbert
arbert earned 100 total points
ID: 13927051
"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
 
LVL 75

Assisted Solution

by:Anthony Perkins
Anthony Perkins earned 100 total points
ID: 13928388
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
 
LVL 8

Assisted Solution

by:doobdave
doobdave earned 100 total points
ID: 13934411
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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 14102128
Split the points.
0
 
LVL 11

Expert Comment

by:lluthien
ID: 14103241
i agree
0
 
LVL 3

Author Comment

by:XanderP
ID: 14129925
I didn't use this question's answers.  I choose to condense the amount of data that I wanted to store instead.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 14130209
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
 
LVL 3

Author Comment

by:XanderP
ID: 14140345
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

Featured Post

Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

Question has a verified solution.

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

Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Suggested Courses

840 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