Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 


 
     
Recent Posts
   
Popular Topics
Home   
Search
    Members    Calendar    Who's On

Home » Article Discussions » Article Discussions by Author » Discuss Content Posted by Robert Marda » Handling The Text Data Type
Add to briefcase
18 posts, Page 1 of 2
12»»

Handling The Text Data Type Expand / Collapse
Rate Topic
Display Mode
Topic Options
Author
Message
Robert W Marda
Posted Friday, May 02, 2003 12:00 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Friday, February 15, 2013 10:33 AM
Points: 976, Visits: 48
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
Post #11892
winash
Posted Saturday, May 03, 2003 4:44 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, February 18, 2013 3:46 AM
Points: 1,422, Visits: 1,883
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....


Post #61240
Robert W Marda
Posted Saturday, May 03, 2003 9:45 PM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Friday, February 15, 2013 10:33 AM
Points: 976, Visits: 48
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
Post #61241
winash
Posted Friday, May 09, 2003 2:33 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, February 18, 2013 3:46 AM
Points: 1,422, Visits: 1,883
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...


Post #61242
Robert W Marda
Posted Friday, May 09, 2003 10:13 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Friday, February 15, 2013 10:33 AM
Points: 976, Visits: 48
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
Post #61243
williamhoos@yahoo.com
Posted Friday, May 07, 2004 7:21 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, June 27, 2006 12:41 PM
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


Post #114951
Jeff Moden
Posted Tuesday, August 26, 2008 9:42 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 5:13 AM
Points: 32,906, Visits: 26,793
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."

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Post #558998
HBhagat
Posted Monday, September 01, 2008 7:59 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: 2 days ago @ 8:15 AM
Points: 406, Visits: 597
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......
Post #562001
ChrisM@Work
Posted Monday, September 01, 2008 8:16 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 5:03 AM
Points: 5,616, Visits: 10,986
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
Post #562009
ChrisM@Work
Posted Monday, September 01, 2008 8:31 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 5:03 AM
Points: 5,616, Visits: 10,986
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
Post #562012
« Prev Topic | Next Topic »

Add to briefcase
18 posts, Page 1 of 2
12»»

Permissions Expand / Collapse

 
 
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.