Re: use of tempdb by union all
From: Andrew J. Kelly (sqlmvpnoooospam_at_shadhawk.com)
Date: 03/23/04
- Next message: Baisong Wei[MSFT]: "RE: Call a Stored Procedure From HTML page using ADODB.Recordset"
- Previous message: Aaron Prohaska: "Table Design Review wanted"
- In reply to: Tenaya: "Re: use of tempdb by union all"
- Next in thread: Tenaya: "Re: use of tempdb by union all"
- Reply: Tenaya: "Re: use of tempdb by union all"
- Messages sorted by: [ date ] [ thread ]
Date: Mon, 22 Mar 2004 20:17:27 -0600
> You are certainly correct about table variables using the tempdb database,
> and not being entirely memory resident ... however, if I might, I think
that
> that is somewhat extraneous to the thread ... no one brought up the topic
of
> table variables before you :-)
Well no one brought up Indexing before you either<g>. I was simply using it
as an example as to why I said what I did in my original post.
> The original poster asked whether a UNION ALL will **always** use the
tempdb
> database. I would suggest that the other responses on this thread have
> implied that the answer to that is "Yes", and I would contend that that is
> not a valid reply.
Here is the posters original words and no where does he state **always**.
If he had I would qualified my response accordingly. As it was I used the
term "most" which in my experience with production db's is the case. I
believe we have answered the OP's question and see no need to continue down
this path. I only felt compelled to reply based on the fact that twice now
you interjected words like "first choice" and "always" that were not written
by me. But thanks for the comments.
>>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?
-- Andrew J. Kelly SQL MVP "Tenaya" <ct@ct.ct> wrote in message news:#bsxK3BEEHA.2804@tk2msftngp13.phx.gbl... > Andrew, > > You are certainly correct about table variables using the tempdb database, > and not being entirely memory resident ... however, if I might, I think that > that is somewhat extraneous to the thread ... no one brought up the topic of > table variables before you :-) > > The original poster asked whether a UNION ALL will **always** use the tempdb > database. I would suggest that the other responses on this thread have > implied that the answer to that is "Yes", and I would contend that that is > not a valid reply. > > In the following situation, are you stating the SQL Server will use the > tempdb database to hold the "intermediate" resultset? > > use pubs > go > > select * from dbo.authors where au_lname = 'White' > union all > select * from dbo.authors where au_lname = 'Green' > go > > Chief Tenaya > > > "Andrew J. Kelly" <sqlmvpnoooospam@shadhawk.com> wrote in message > news:eFMH8tBEEHA.2768@tk2msftngp13.phx.gbl... > > While the amount of free memory does play a part in when or if tempdb is > > used it is used more often than most people realize. For instance a lot > of > > people are under the impression that if they use table variables instead > of > > temp tables that it will only use memory. Even when ample memory is > > available the table var will most likely create a presence in tempdb. The > > only operations that occur in the current db are things like index > creation > > / rebuilding. The exception is if you specify the USE TEMPDB option of a > > create index. All other temporary operations such as Joins, Unions, > > sub-selects etc will not use the current db for scratch space. Even > CHECKDB > > uses tempdb. That is what tempdb is for. If it can get by with the > available > > free memory then fine but I feel that is the exception more than the norm. > I > > never stated "it was the first choice" but it happens more often than > people > > assume and if they plan on tempdb being utilized it will save them > headaches > > in the future. > > > > -- > > Andrew J. Kelly SQL MVP > > > > > > "Tenaya" <ct@ct.ct> wrote in message > > news:utUgIgBEEHA.2600@TK2MSFTNGP09.phx.gbl... > > > 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. > > > > > > > > > > > > > > > > > > > >
- Next message: Baisong Wei[MSFT]: "RE: Call a Stored Procedure From HTML page using ADODB.Recordset"
- Previous message: Aaron Prohaska: "Table Design Review wanted"
- In reply to: Tenaya: "Re: use of tempdb by union all"
- Next in thread: Tenaya: "Re: use of tempdb by union all"
- Reply: Tenaya: "Re: use of tempdb by union all"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
