Solved

SQL Server 2000 largest datatype

Posted on 2007-11-21
3
615 Views
Last Modified: 2013-11-06
I have created a VB.Net, Sql Server 2000 application.
I am trying to store some text in a table column. The column is expected to have 50 characters appended everyday. At this rate, the row may reach the maximium limit in a couple of months(approximately). How can I design the table so that it can accomodate the huge data in the column?
What datatype can I use?

Thanks in advance.

0
Comment
Question by:vdesai_8
3 Comments
 
LVL 18

Accepted Solution

by:
Yveau earned 350 total points
ID: 20331258
the varchar goes up to 8000 chars, so that would keep you out of the clear for 16 days ...
How about a little different design ? How about a table with an ID field, a Datetime and a varchar(100). So each day will get it's own record and when you need them all together, you select them into one large string.

SQL 2005 can have the varchar(max) which goes up to 2GB of data ... but that is no good for you at this point.

I wouldn't go for the image or text data types, but they do give you the opportunity to go beyond 8000 chars.

So, quick (text data type) or nice (new design) ... it's up to you.

Hope this helps ...
0
 
LVL 29

Assisted Solution

by:Göran Andersson
Göran Andersson earned 150 total points
ID: 20331558
The text data type has a capacity of 2 GB (the same as varchar(max) in SQL Server 2005).

However, storing all the data in the same field is not a good idea. Over time your application will get slower and slower, and one day you will run out of memory.

Yveaus suggestion to store each value in a separate record is a better solution.

Alternatively, if you don't want the values in separate records, at least set a size limit on the data that you store in each record, for example a few kB, and when you reach the limit you start on a new record. That way the application will not stop working after some time.
0
 

Author Closing Comment

by:vdesai_8
ID: 31410481
Thanks!
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

708 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now