how to store huge data

Question

Post reply

how to store huge data

Rahuul

Mr or Mrs. 500

Points: 581
More actions
August 3, 2004 at 4:34 am

#85915

Hi,

I need to design tables, which has 60 fields. Around 15 fields which are of text type, varies from 500 characters to 15000 characters. Since SQL 2000 has a limitation of 8 KB per row, I could not make 15 fields in a table. I can split tables but the no. of joins would increase. This is the solution I thought:

I can have one table where in I can store these data in terms of rows with an identifier and an order. So long data can be splitted in rows and while retriving we can concatenate all rows and display.

Table A

ProjectID             varchar(25)

Type                   varchar(25)

ProjectManager     varchar(50)

xxxxx

xxxxx

Table B

ProjectID                varchar(25)

ComponentType       varchar(20)

ConcatenateOrder     int

Description              varchar(7500) or varchar(1000)

Relationship: TableA.ProjectID = Tableb.ProjectID

Type: One to Many

Now, my question is: if I store a value which is say 14000 character and if my Table.Description field is varchar(7500), it would create two rows. But suppose the value is just 1000 character (since it varies from 1000 to 15000),

Is it not a wastage of memory? (eventhough we use varchar)

Second scenario: If i keep Description field as varchar(1000) and my data is 14000 character, it would create 14 rows. And when I want to see the field as a whole, I need to concatenate all 14 fields and see.

Which way should I go? Or can u suggest some other way of doing it.

Thanks for reading

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Lukas Botha Old Hand Points: 376 More actions · Answer 1

I would use text, id you are going to store 1000 rows with only one character in then you would waste the space, but for what you described test would be the sollution.

Text takes a whole page (8K), if it fills a page it will extend to another until your 15000 characters are stored. This would also resolve your problem of addind 14 fields to produce a single output.

Lukas Botha

Steve Jones - SSC Editor SSC Guru Points: 728159 More actions · Answer 2

I agree that text would be better. If you want to try and save space, you could set the text_in_row for this table to 1000 or 1500 and get all those rows in the table and larger rows would be placed in a text extent.

Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com