Handling The Text Data Type

Question

Handling The Text Data Type

Robert W Marda

SSChampion

Points: 13413
More actions
May 2, 2003 at 12:00 am

#105963

Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/rmarda/handlingthetextdatatype.asp
Robert W. Marda
Billing and OSS Specialist - SQL Programmer
MCL Systems

Viewing 15 posts - 1 through 15 (of 17 total)

You must be logged in to reply to this topic. Login to reply

winash SSChampion Points: 11446 More actions · Answer 1

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 SSChampion Points: 13413 More actions · Answer 2

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
Billing and OSS Specialist - SQL Programmer
MCL Systems

winash SSChampion Points: 11446 More actions · Answer 3

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 SSChampion Points: 13413 More actions · Answer 4

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
Billing and OSS Specialist - SQL Programmer
MCL Systems

williamhoos@yahoo.com Right there with Babe Points: 789 More actions · Answer 5

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 SSC Guru Points: 1004339 More actions · Answer 6

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.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

HBhagat Hall of Fame Points: 3341 More actions · Answer 7

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 SSC Guru Points: 186127 More actions · Answer 8

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

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 9

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

HBhagat Hall of Fame Points: 3341 More actions · Answer 10

So now if I want the substring to work the way i want do you have an idea to solve this.. as this thing is eating my head now...

I am stuck into this problem from last week..

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 11

harsha.bhagat1 (9/1/2008)
So now if I want the substring to work the way i want do you have an idea to solve this.. as this thing is eating my head now...
I am stuck into this problem from last week..

Harsha, there might just be a solution here http://www.sqlservercentral.com/Forums/Topic560267-8-1.aspx

^{“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

HBhagat Hall of Fame Points: 3341 More actions · Answer 12

HBhagat

Hall of Fame

Points: 3341

September 1, 2008 at 9:07 am

#865695

That is my question only:)

Thats me ..

Harsha Bhagat

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 13

harsha.bhagat1 (9/1/2008)
That is my question only:)
Thats me ..
Harsha Bhagat

Harsha Bhagat, if the proposed solution posted in your original thread fails to work for you, then why not post there, explaining how it fails? Then we can continue working in the original thread without railroading this one.

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

HBhagat Hall of Fame Points: 3341 More actions · Answer 14

Its not the solution..

There is the problem what i found after working on it more today.

I am not able to split my text field. Today I found that the substring is not wrking properly for which my data is not comming properly..

Since there was a suggestion and I think it was you , but it isnot working with the tally table..

Or I think may be the code is not full..

I am still working on it.:) ..

On the site I found handling text field. So iread and posted my problem

The other one is I had asked as I was notr aware that there was an article over here regarding the text handling..

Sorry if I had given any trouble..