Results 1 to 8 of 8
  1. #1
    Join Date
    Feb 2005
    Location
    Utah
    Posts
    73

    Unanswered: Concatenation of Text

    I have an instance where I need to concatenate some data that is stored in a text datatype. I can't cast it to a varchar/char because that may well truncate the data. I just read about UPDATETEXT, which I think I can use, but I need to use it for a bunch or rows and it looks like this works on one row at a time. Anyone have experience with this?

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Yes, UpdateText processes only one row per call.

    The MDAC infrastructure was never meant to handle BLOBs, so trying to update a TEXT column in a million rows at once is really a bad idea. I'd either change the column datatype, or find a different way to acheive the same goal.

    This just sounds like a problem sniffing eagerly at a new victim to me!

    -PatP

  3. #3
    Join Date
    Feb 2005
    Location
    Utah
    Posts
    73
    Thanks Pat. Unfortunately, I have to use a TEXT column because the data can exceed the 8k limit for VARCHAR and CHAR. I guess my choices are to use DTS and some sort of ActiveX script, write a script to concatenate before it hits the database, or do the unthinkable and write a cursor (although I don't really want to do that).

    Dandy

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Nothing quite like being caught between the devil and the deep blue sea, is there? Given those choices, I'd opt for DTS.

    Actually, cursors aren't logically bad, it is just that their performance is awful compared to set operations. Many databases like Oracle and Z-Series DB2 rely on cursors to do much of anything.

    Be forewarned though, TEXT manipulation is slower than other datatypes. Just retrieving or storing a row with a TEXT or IMAGE column takes a lot longer than it does without those columns.

    -PatP

  5. #5
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Of course it does, doh...First it needs to get the pointer (binary(16)), then retrieve 8K worth of data stored in a separate set of pages which results in at least 1 additional IO per row retrieved. BLOBs are wonderful (hehehe) when used for what they were invented, - not for set-based operations. And this is one of the few cases when a cursor may very well be applicable.

    Quote Originally Posted by pat phelan
    The MDAC infrastructure was never meant to handle BLOBs...
    Really? Who told you that?
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  6. #6
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote Originally Posted by rdjabarov
    Really? Who told you that?
    It was either the guy who invented DTS, or the GPM for MS-SQL 7.0. They were both there, I just don't remember who actually said it and who just nodded sagely.

    -PatP

  7. #7
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Oh, I see, and I'm an airplane
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  8. #8
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    I'm a teapot! I'm a teapot!
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •