Solved

SQL Server 2000 largest datatype

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

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

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

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
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.

707 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