August 3, 2004 at 4:34 am
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
August 3, 2004 at 7:42 am
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
August 3, 2004 at 10:49 am
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.
August 4, 2004 at 6:46 am
In that sanerio, I'll put the Description into a seperate table with one to many relationship with the option to move into a second row or third.
August 5, 2004 at 1:40 am
thanks for your replies. As you said, I can go either with TEXT datatype
or
putting description field in a different table with one to many relation.
which one would give better performance without wasting much space?
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply