• SQLkiwi (12/20/2010)


    In practice, I often find that the presence of a temporary object (of sufficient size or complexity to make a table variable a poor choice) an indicator that I am doing something dumb. Specifically, manipulating large amounts of data, creating indexes, relying on statistics to produce a non-trivial plan...all this is work that is performed again and again, on every execution. Often, it indicates that the present overall database design is lacking.

    Paul

    Granted,

    Sometimes we're stuck in the land where we can't make the necessary database structural changes because we're stuck with legacy code that doesn't have an abstracted database access layer (i.e. one that has all the SQL calls as stored procs and not embedded SQL) and we find ourselves constrained in ways that we're stuck with it that way, and temp tables are the only viable work around.

    Other times it's just part of a massive ETL process, and we need to build working tables to eventually manipulate the data into a way to make the final tables what we want, with some wonky intermediate steps.

    I personally don't care for table variables, probably due to the all the rumors that developers around me were given. Things like how table variables are memory only and temp tables always go to disk. Those sorts of things have turned me off to using them as my go to tool, and instead I tend to use temp tables first, and use table variables when I need them for specific use.

    In this case, the end result is very likely "it depends" and/or "to each his own". Provided we're both willing to go to the other tool when necessary, I don't think it's that important. 🙂



    --Mark Tassin
    MCITP - SQL Server DBA
    Proud member of the Anti-RBAR alliance.
    For help with Performance click this link[/url]
    For tips on how to post your problems[/url]