Solved

SQL Server 2000 largest datatype

Posted on 2007-11-21
3
618 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

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

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

Update (December 2011): Since this article was published, the things have changed for good for Android native developers. The Sequoyah Project (http://www.eclipse.org/sequoyah/) automates most of the tasks discussed in this article. You can even fin…
Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

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