Home Forums SQL Server 2012 SQL Server 2012 - T-SQL Temp tables vs "permanent" temp table vs User Table Type RE: Temp tables vs "permanent" temp table vs User Table Type

  • September 20, 2013 at 7:11 am

    #1651906

    PiMané (9/19/2013)

    Jeff Moden (9/19/2013)

    PiMané (9/4/2013)

    What's the best way to store the XML?

    The best way, IMHO, is to shred it on receipt and store it as properly normalized data. XML is formatted data and one of the worst data sins there is is to store formatted data.

    The problem is that we have clients that have receipts with over 1000 lines, so to send the XML has proper data to the SP would mean to call and SP 1000 times or to have user defined data table (only exists in SQL 2008 and VB6 doesn't support it... this is our big problem).

    That why we decided to use the XML to send the data to the SP and then create the temp table with its data to be used in all the process (we are code reviewing the process and so far found over 50 SP, FN and TRG that are called "along" the way...).

    Pedro

    Heh... you asked what the best way to store XML is. I suggested shredding it and storing it in normalized tables. Are you storing the XML in a table or not?

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