SQL Server Performance Forum – Threads Archive

Substring Vs more text column

Hi all
any one have statistic about using substrings on a text datatype column Vs using more text datatype column?
what i mean is :
It is more performnt a select like:
select substring(field,1,4000), substring(field,4000,8000),substring(field,8000,12000) from table1 where field is text type of 12000 char or is better a select like select field1,field2,filed3 where fieldx are text type of 4000 char each one?
any idea about to skip page size limits without using text data type?
Have good luck
Antonio

---

Antonio, given what you have there, bear in mind that without using text or image, you hhave a max row size of 8060 (as I recall) so you should not have 3 varchar 4000 columns. SQL will allow you toc reate the table, but you may have data insertion problems. Error message will be : Warning: The table ‘vC’ has been created but its maximum row size (12027) 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." Looks like you’ll have to go text, if you really have that much data. Panic, Chaos, Disorder … my work here is done –unknown

---

any ideas about performance?

---

If you’re running SQL Server releases earlier than SQL Server 2000, you have two choices for storing and retrieving arbitrarily long character strings. If you choose the varchar data type, you might have to break the data across several rows to avoid hitting the 8000-character limit in SQL Server 7.0 or the 256-character limit in SQL Server 6.5. This storage method requires two I/Os for a retrieval—one I/O for the pointer and at least one other I/O for the first text page. A double I/O slows down the scans that filter on the value of the text column. SQL Server 2000 introduced the text in row concept, which helps you avoid the text data type’s double I/O (and the potential seek/rotate latency) by letting you specify the part of the text data that you want to store in the data row. Using this feature is a good idea when you’re storing small text files that fit on one page or when you’re looking at the head page of a text file to help you decide whether to retrieve the whole file. Refer to books online for more informaiton.
Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS” with no rights for the sake of knowledge sharing.

---

If you are going to store that much data in a column I would go with Text, here are my reasons….First of all SQL Server does not store the data itself in the same row with the rest of the columns but only stores a pointer to the first page of the data which are stored in separate pages while this is ineffectient you could use the system stored procedure sp_tableoption to store text date below 8000 directly in your row. One of the biggest advantage you have with using Text data type is that its not Logged imagine what performance impact it would have to log a 8000 char field everytime a change was made?? You could use different stategies for handling text data for example use the Textimage_on clause which will let you store this data on a seperate file group. While using Text/image data is difficult to work with it also is advantages in some points.

---

… also read thru books online for Text and image data types topic. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS” with no rights for the sake of knowledge sharing.

---

]]>