Re: use of tempdb by union all

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

From: Tenaya (ct_at_ct.ct)
Date: 03/22/04 

Date: Mon, 22 Mar 2004 09:14:43 -0500

Andrew,

It was my impression that the tempdb database will be used when SQL Server
has insufficient memory / disk space to carry out the operation in the
current database ... i.e., the one in which the operation was "launched"
(via the USE command, either implicitly or explicitly).

For example, the section "tempdb and Index Creation" in the SQL Server 2000
Books Online indicates that one has to explicitly request SQL Server to use
the tempdb database when creating an index ... with the implication that
otherwise the resources of the current database will be used.

I think it might be misleading to imply that the tempdb database area is the
"first choice" of SQL Server when dealing with "most temporary or
intermediate operations" :-)

Chief Tenaya

"Andrew J. Kelly" <sqlmvpnoooospam@shadhawk.com> wrote in message
news:uQ69PMBEEHA.1452@TK2MSFTNGP09.phx.gbl...
> That is essentially what tempdb's purpose is in life. Most temporary or
> intermediate operations will utilize tempdb.
>
> --
> Andrew J. Kelly SQL MVP
>
>
> "simo" <anonymous@discussions.microsoft.com> wrote in message
> news:818D0359-BEBC-4B30-BAC8-2B5D8BD8A930@microsoft.com...
> > Hi,
> > I need confirmation of the fact that when you perform an operation like
> UNION ALL, tempdb is used as the working area for unionizing the data
before
> returning the new result set.
> > can anybody confirm this?
> >
> > many thanks.
>
>

Relevant Pages

  • Re: use of tempdb by union all
    ... SQL Server will indeed allocate "scratch space" from the user database, ... >> It was my impression that the tempdb database will be used when SQL ...
    (microsoft.public.sqlserver.programming)
  • Re: Merge error 2147200925 - why does reboot the server machine could solve problem.
    ... but the problem occurs 'in certain circimstances' and ... relates to teh production of temporary tables, ... the tempdb database might play a part. ... Paul Ibison SQL Server MVP, ...
    (microsoft.public.sqlserver.replication)
  • Re: Drive Space Problem
    ... Maybe it is just the tempdb database (it gets cleaned during every restart ... of sql server) ... > After searching this forum I haven't found a specific answer for my ... > of things listed in this forum to no avail, ...
    (microsoft.public.sqlserver.server)
  • Re: Tempdb
    ... Try KB 307487 on How To: Shrink the Tempdb Database in SQL Server ... Andrew C. Madsen ... Harley-Davidson Motor Company ...
    (microsoft.public.sqlserver.server)