Handling The Text Data Type

  • 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

  • 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....

  • 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

  • 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...

  • 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

  • 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

  • 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)

  • 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......

  • 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

  • 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

  • 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.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

  • That is my question only:)

    Thats me ..

    Harsha Bhagat

  • 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

  • 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..

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

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