Memory Issue: Text or Varchar?

[ChopinFan]

Among other fields, there are seven in my table that could potentially contain 8,000 characters each and I am concerned about the memory size that any given row will take. I don't want to go over the memory limit. I understand that the Text datatype actually uses multiple data pages in the database and only appears to be saved in the table, but I have also heard that it still packs on the memory. I really don't want to break those seven fields out into another table unless I have to.

I guess there are two questions: First, how can I find the maximum memory currently used by a row? Second, which uses more table/row memory: Text or Varchar(8000)?

Thanks!

 

[gmmastros]

SQL Server 2000 has a limitation of 8060 bytes per row. If you try to store more than that, you will get an error.

Text field are not stored in the row. Instead, a 16 byte pointer to the actual location is stored. This allows you to store more than 8060 bytes per record.

There is a downside to text fields. Many of the string manipulation functions built in to sql server do NOT work with text fields.

With SQL Server 2005, you can use varchar(max) which is similar to text fields without the 8060 bytes per row problem.

If you want to know the largest size of the data in a record, you could do this...

Select Max(DataLength(Col1) + DataLength(col2) + DataLength(col3))
From   Table

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[ChopinFan]

If I'm understanding correctly, using text fields is the way to go. Am I to understand that if the total datalength yielded by the query you give is less than 8060, there's no problem?

 

[gmmastros]

Well, if you have a couple varchar(8000) fields in the table, then you have a problem just waiting to happen.

Take a look at this code.

Create Table #Temp (id int, Col1 varchar(8000), col2 varchar(8000))

Insert into #Temp(id, col1) Values(1, replicate('A', 8000))

Update #Temp Set Col2 = Replicate('B', 8000)

Drop Table #Temp

You will get this message:

[tt][blue]
Warning: The table '#Temp' has been created but its maximum row size (16029) exceeds the maximum number of bytes per row (8060). INSERT or UPDATE of a row in this table will fail if the resulting row length exceeds 8060 bytes.

(1 row(s) affected)

Warning: The table '#Temp' has been created but its maximum row size (16029) exceeds the maximum number of bytes per row (8060). INSERT or UPDATE of a row in this table will fail if the resulting row length exceeds 8060 bytes.
Server: Msg 511, Level 16, State 1, Line 5
Cannot create a row of size 16017 which is greater than the allowable maximum of 8060.
The statement has been terminated.
[/blue][/tt]

So, you NEED to do something about this. What you actually do depends on your data, so it's a little difficult to say. If it were me, I would probably create additional tables to store the data. Each table would have a 1 to 1 relationship with the main table. It would have an ID column (foreign key) and a data column (varchar 8000). The reason I would do this is because I find it handy to use the string functions within SQL Server.

To see what I mean, open query analyzer. Make sure the 'object browser' is visible. If it's not, press F8 on your keyboard. In the object browser, you will see each of your databases. Below the list of database, you will see common objects. Take a look at the 'string functions' (they work on varchar fields). Then, take a look at 'text and image functions' (they work on text fields). If you think it would be handy to use the string functions on this data, then you'll want to keep them in varchar fields.

Really, though, it's up to you. Good luck.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[harebrain]

And if you're running SQL Server 7, you won't even get any errors: you'll just lose data.

 

[ChopinFan]

Thanks for the good info. I am runnig SQL2K, but I don't have any need to manipulate or search through the text in any way, so it sounds like Text is the only viable option. It sounds like each text field takes up only 16 of the 8060 bytes available per row (only storing a pointer) while a varchar(8000) takes 8000. Please let me know if I have understood you correctly.

 

[gmmastros]

You understand correctly.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[ChopinFan]

Thanks for your valuable help!