Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
henrikop
Constraint Violating Yak Guru
280 Posts |
 Posted - 2004-02-12 : 10:30:03
|
| I have a log table with 2 million records. A description column is now 200 varchar and is filled with ID's or short descriptions. But now I want to put some error descriptions in the log as well. These are longer than 200 chars, so now I want to change the column to a text field. How will this affect the size of my database?e.g.:Description = '0025653' --> 7 chars time 2 = 14 Bytes (with varchar)Description = '0025652' --> 16 bytes (with text)Is this correct?Henri~~~Success is the ability to go from one failure to another with no loss of enthusiasm |
|
|
derrickleggett
Pointy Haired Yak DBA
4184 Posts |
Posted - 2004-02-12 : 11:01:27
|
| It would be better to seperate your entities and have an error table. You could use a big varchar for that. Do you really need more than VARCHAR(8000)???? That's AWFUL big.MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
 |
|
|
AndrewMurphy
Master Smack Fu Yak Hacker
2916 Posts |
Posted - 2004-02-12 : 11:02:32
|
| Your maths is a bit astray....BOL has a section on "Estimating the Size of a Table"...which can be used to derive some of your information....In your instance....The bit of 7 chars time 2 = 14 bytes ...applies to nVarchar...double-byte/unicode varchars...And the text field...will be 16bytes (pointer to the data) + the data itself...7 bytes...Examine the section in BOL....it should clarify things...At a rought guess, I'd suspect your datasize will explode by upto a factor of 3....in the case where all existing data has 7 chars of information....you will now incur an extra 16 bytes to save the exact same 7 chars....Text fields should only be used to save really, really long pieces of information.....and as attested here before by others...should be avoided if at all possible.Going to varchar(6000/7000/8000 etc)...(remember the max is 8000)...should be sufficient 99.9999999% of your needs... |
|
|
|
henrikop
Constraint Violating Yak Guru
280 Posts |
Posted - 2004-02-12 : 11:30:20
|
| Ok, clear :-) This helped me.Henri~~~Success is the ability to go from one failure to another with no loss of enthusiasm |
|
|
|
|
|
|
What takes more space varchar of text?