| Author |
Topic |
|
coolerbob
Aged Yak Warrior
841 Posts |
 Posted - 2005-04-29 : 04:49:17
|
| I have a table with 5 columns.One of them is a text column. Often the data is larger than 8000 characters. So I cant just replace it with a varchar8000.Which of the following two is a better solution? Or is there a better 3rd option?Replace it with numbered varchar columns Col1, Col2, Col3 (would it be best to go for a small size or a big size on varchar? 500? 1000? 8000?). But then I have a limit created by the amount of columns. not normalized.ORreplace it with a notes link table? PK_ID, FK_ID, Note, PositionAgain in this situation, what is a good size: 250? 500? 1000? 8000?I remember Robvolk saying that 500 is preferred and no more than 1000. But what if I know that that will often result in having to concatenate tens of records together before returning it to the GUI? Isnt it better to concatenate as few as possible? |
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2005-04-29 : 05:14:25
|
you have to take into account that a single row can't hold more than 8060 bytes of data.since text and image columns don't count towards this quota i have to say you'd be better of leaving the text column.sure, text columns are more difficult to work with than varchars but it's not that bad.and in my experience if you have a text column usually it holds a whole lot of text that doesn't change much.Go with the flow & have fun! Else fight the flow  |
 |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2005-04-29 : 05:15:52
|
| If you really want to get rid of the text then it would have to be the second option.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
coolerbob
Aged Yak Warrior
841 Posts |
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2005-04-29 : 05:56:13
|
emmm.... don't quite see your point...i think i'm talking about a different thing here.i didn't mean you should use text columns all over the place.  as i have no idea what you db and tables are like i can't comment on that.you said that often the data in the column is more than 8000 chars long.so i assumed that there will be a majority of this. if it isn't splitting it up is good because of the reasons said in the other thread.and as far as i have seen text columns are only used for i.e. articles over 8000 chars. and that doesnt change much.then again we don't have much "higly paid consultants" that use text columns all over the place over here so what in my post is bothering you?Go with the flow & have fun! Else fight the flow |
|
|
|
coolerbob
Aged Yak Warrior
841 Posts |
Posted - 2005-04-29 : 06:13:58
|
| I'm trying to see where the line should be drawn. How far should one go to avoid text columns.As that thread shows, allowing text columns in a DB can open the way to a massive DB. I really dont want that.I saw that in that old DB. I want my one to be stable and maintainable. |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2005-04-29 : 06:39:53
|
| Depends on your priorities.Maintaining a sequence of character fields can take a lot of effort and be slow if there are a lot of them.Space is not usually a big concern.You might also consider holding the text in files on disk and just holding the path in the table.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2005-04-29 : 07:04:59
|
| well you had text columns used where they weren't necessary.personally i don't think splitting columns in your case is neccesary,because you say most of the data is over 8k.Go with the flow & have fun! Else fight the flow |
|
|
|
coolerbob
Aged Yak Warrior
841 Posts |
Posted - 2005-04-29 : 07:08:03
|
| So if most of the data isnt over 8k (more like 1k) and there are millions of records, then you would split it? |
|
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2005-04-29 : 07:15:45
|
| yes.Go with the flow & have fun! Else fight the flow |
|
|
|
coolerbob
Aged Yak Warrior
841 Posts |
Posted - 2005-05-09 : 05:57:07
|
| Has anyone done any testing to show the performance difference between text on the same table or varchar8000 on vertical partition? |
|
|
|
byrmol
Shed Building SQL Farmer
1591 Posts |
Posted - 2005-05-09 : 06:30:29
|
| Look up the "Text in Row" option in BOL.Quite frankly I am amazed you would consider any option but to use a TEXT data type. It's like splitting a bigint domain into multiple ints' because a couple of values are greater than 2^32.. DavidMA front-end is something that tries to violate a back-end. |
|
|
|
coolerbob
Aged Yak Warrior
841 Posts |
Posted - 2005-05-09 : 07:12:53
|
| Ok point taken.Do you leave the text in row size to the default of 256? |
|
|
|
byrmol
Shed Building SQL Farmer
1591 Posts |
Posted - 2005-05-09 : 07:24:42
|
| No. Find the average size of TEXT data and add about 25%.. you said 1K..maybe 1536 round abouts..DavidMA front-end is something that tries to violate a back-end. |
|
|
|
|
TEXT Column replacement solution