Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Handling The Text Data Type


Handling The Text Data Type

Author
Message
Robert W Marda
Robert W Marda
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1096 Visits: 82
Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/rmarda/handlingthetextdatatype.asp



Robert W. Marda
SQL Programmer
Ipreo

winash
winash
UDP Broadcaster
UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)

Group: General Forum Members
Points: 1480 Visits: 1883
Excellent article on the TEXT datatype...since I have a few Text fields in my database I was badly in need of an article like this for quick reference...

I especially liked the last tip about how to view data in the text field beyond the first 8192 characters...(what I used to go through to find that data... )

does the fact that a text field will only store a pointer to the data in the page with the other columns result in any problems when a select statement is issued...

I had some problems getting data displayed in the front end when my select statement was something like :
Select IntField1,TextField1,IntField2,VarcharField1

the fields selected after the Text field would not be displayed...

but when the select order was changed to :
Select IntField1,IntField2,VarcharField1,TextField1
or if I used :
Select IntField1,Convert(Varchar(8000),TextField1),IntField2,VarcharField1
then I had no such problems....



Robert W Marda
Robert W Marda
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1096 Visits: 82
The main problem I have noticed with text fields in a SELECT query is that you can't do a SELECT DISTINCT without casting all text fields as varchar (or some other datatype that doesn't need pointers).

I have not noticed the problem you mention.

Robert W. Marda
SQL Programmer
bigdough.com
The world’s leading capital markets contact database and software platform.



Robert W. Marda
SQL Programmer
Ipreo

winash
winash
UDP Broadcaster
UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)

Group: General Forum Members
Points: 1480 Visits: 1883
I found the following in SQL 2000 BOL :

"SQL Server 2000 supports a new text in row table option that specifies that small text, ntext, and image values be placed directly in the data row instead of in a separate page. This reduces the amount of space used to store small text, ntext, and image data values, and reduces the amount of disk I/O needed to process these values.

When text in row is ON, SQL Server 2000 stores small text, ntext, and image values in the data row. Only text, ntext, or image values that cannot fit in the row are stored in a separate collection of pages.

Each table has only one collection of pages to hold text, ntext, and image data. The sysindexes row that has indid = 255 is the anchor for the collection. The text, ntext, and image data for all the rows in the table is interleaved in this collection of text and image pages."

Does this new feature make access to text data type data any faster??I don't have access to a server with SQL Server 2000 here(BOL is the closest I get) and wanted to know if the new implementation of storage of text data types speedens things up - my text fields tend to contain data ranging from 1000 bytes upwards...



Robert W Marda
Robert W Marda
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1096 Visits: 82
In theory it should speed up the query because for those text fields that have few enough characters to fit in the row SQL Server won't have to do a look up for those characters using a pointer.

However, I think it might not always be beneficial (although I have not tested this yet) because if having the characters of the text in the row changes the page set up so that instead of haveing 30 rows per page you only have one per page I would think this would slow down the query not speed it up.

Currently, we don't use this feature and so I have not explored it to see if it is useful or not.

Robert W. Marda
SQL Programmer
bigdough.com
The world’s leading capital markets contact database and software platform.



Robert W. Marda
SQL Programmer
Ipreo

williamhoos@yahoo.com
williamhoos@yahoo.com
SSC Rookie
SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)

Group: General Forum Members
Points: 29 Visits: 1

The inability to use select distinct and search using LIKE seem like major limitations to the text/ntext fields.

This was a great article, but the solutions seem like some major pains in dealing with this type of field, i.e. having to write speacial queries and use tricks just to see the data at all!

Hopefully these problems are being addressed with Yukon/2005





Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (52K reputation)SSC Guru (52K reputation)SSC Guru (52K reputation)SSC Guru (52K reputation)SSC Guru (52K reputation)SSC Guru (52K reputation)SSC Guru (52K reputation)SSC Guru (52K reputation)

Group: General Forum Members
Points: 52484 Visits: 40327
Robert...

I know it's been over a year since this article came out, but want to say what an outstanding article you've written... very easy to read and great examples!

For all the reasons some folks mention about special handing of TEXT, I've not used the TEXT datatype. Now, it has been heaped upon me and your fine article will make a lot of things a lot easier.

Heh... it would be nicer if this was all being done in 2k5.

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is not.
Just because you can do something in PowerShell, doesnt mean you should. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
HBhagat
HBhagat
Mr or Mrs. 500
Mr or Mrs. 500 (566 reputation)Mr or Mrs. 500 (566 reputation)Mr or Mrs. 500 (566 reputation)Mr or Mrs. 500 (566 reputation)Mr or Mrs. 500 (566 reputation)Mr or Mrs. 500 (566 reputation)Mr or Mrs. 500 (566 reputation)Mr or Mrs. 500 (566 reputation)

Group: General Forum Members
Points: 566 Visits: 934
Hi,
The article is excellent and working on it I also gone into the problems facing text datatype .
But when you say use the substring , for me its is not working.
I have to update my text field with values from 1 positiontill 8014 .
my substring query to update the text field is like
update #test
set Next_Expr = substring(Next_Expr , patindex('%,%', Next_Expr) , 8014)

If I execute this I receive an error as
Server: Msg 212, Level 16, State 1, Procedure proc_Function_Explode, Line 46
Expression result length exceeds the maximum. 8000 max, 8104 found.


For this reason I loss lots of data.

I would be thank full to you if you can suggest any solution for the above query. This update will run till I have ',' in the Next_Expr field.

Please Help......
ChrisM@Work
ChrisM@Work
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10347 Visits: 19234
harsha.bhagat1 (9/1/2008)
Hi,
The article is excellent and working on it I also gone into the problems facing text datatype .
But when you say use the substring , for me its is not working.
I have to update my text field with values from 1 positiontill 8014 .
my substring query to update the text field is like
update #test
set Next_Expr = substring(Next_Expr , patindex('%,%', Next_Expr) , 8014)

If I execute this I receive an error as
Server: Msg 212, Level 16, State 1, Procedure proc_Function_Explode, Line 46
Expression result length exceeds the maximum. 8000 max, 8104 found.


For this reason I loss lots of data.

I would be thank full to you if you can suggest any solution for the above query. This update will run till I have ',' in the Next_Expr field.

Please Help......


See BOL: "The returned string is the same type as the given expression with the exceptions shown in the table."
Given expression Return type 
text varchar



Cheers

ChrisM

“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Exploring Recursive CTEs by Example Dwain Camps
ChrisM@Work
ChrisM@Work
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10347 Visits: 19234
Robert

SELECT CHARINDEX(@Delimiter, @String, @number)



will fail i.e. return 0, if @number exceeds 8000 characters.

Cheers

ChrisM

“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Exploring Recursive CTEs by Example Dwain Camps
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search