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 |
|
rikwaldron
Starting Member
1 Post |
 Posted - 2003-07-31 : 09:33:57
|
| HiI am struggling to size/predict growth for a new table. The table has a column of data type text, which (as far as I can make out) will store a pointer on the data page and the actual text on a text/image page. The text may be very large so setting 'text in row' to ON and sizing the table by the regular (and well documented) calculations is not an option.So the question - can you size the text/image page? If so where is it stored and how? Excuse me if these questions seem stupid - I have searched BOL etc. and can't seem to find the answer easily. My only other thought was to simply size examples of the text as stored (plain text) via notepad/textpad and just use this raw size as an indication of the space that will be used per text item.Any ideas greatly appreciated.Rik |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2003-07-31 : 19:00:28
|
| If you can find a representative sample of the kinds of text or files you'll actually be storing, then you can guesstimate an approximate size. Make sure to round file sizes up to the nearest multiple of 8,192. Yes...even if a file is only 100 bytes, putting it into a text column will consume 8,192 bytes of space if you don't use text in row. For that reason you might want to reconsider using text in row if you know you'll have a lot of small values to store. Anything larger than the size you set will be stored as text normally would, but anything smaller will be stored in the data page directly. If most of your data will be under 4,000 bytes in size you will see a huge improvement in storage efficiency. You can't lose with text in row. |
 |
|
|
|
|
|
Database Sizing - Text Data Types