October 18, 2005 at 3:01 pm
We will be storing an XML response which has 70,000+ characters in single column in a table that has numerous other columns. Although none of the other columns are even close to this size I wanted to get some opinons about keeping this XML response in the same table or breaking it out to it's own table. This is a new table and is in it's test phase but I want to plan for a lot of future growth for this table. Any suggestions? Thanks for the posts!
October 18, 2005 at 5:02 pm
So long as you don't use select * on your table you should be ok - the text data itself is actually stored separately and a pointer to it is stored in the row (under usual circumstances - slightly different if "text in row" is on). I have often stored the BLOBs in a separate table in a 1:1 relationship, but I haven't seen any great performance difference either way.
October 19, 2005 at 6:11 am
Thanks Ian. I don't have the text in row option turned on but I'm thinking about it (BOL said it could be read and written to as fast as a nvarchar,varchar, or varbinary.) Not sure if this will be a performance gain but I guess it's worth a shot.
Anyone know what the commands are for finding out how much storage space is being taken up by these entries? I've looked around and I only found DataLength which just gives me character count and not byte size. Thanks again.
October 19, 2005 at 7:23 pm
You won't get 70kb of text in row though - row lengths are 8192 max bytes (from memory - don't quote me). And then with text in row you get other things such as text pointers behaving differently, etc - I've never tried it in production only because I was more familiar with the traditional method of storing BLOBs and I always wanted either none of the BLOB, or all of the BLOB, never just the first x bytes at the beginning.
The Datalength function returns the number of bytes. The len function returns the number of characters (so returns half number of bytes for nchar/nvarchar/ntext columns).
Cheers
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply