• GSquared (8/28/2012)


    The only way I see to possibly avoid tempdb on this one would be to build permanent tables, do the work in them that tempdb is doing in worktables, and use those instead of the CTE. But even that isn't likely to work, because the SQL engine will still need to do sorts on the data, for the distinct and row number operations at the very least, and that's usually done in tempdb.

    If it's blowing tempdb up by 11 Gig, I'm assuming this query is dealing with a huge amount of data. Can you filter the data before-hand, in a separate table or query? That will still use tempdb, probably, but might use less of it.

    Yes you are absolutely correct. This table has records around 6 million...

    ok GSquared, as you say let me filter the records in separate table and will work on it and i will let you know how things going on..

    Thanks,
    Charmer