December 16, 2009 at 8:42 pm
Comments posted to this topic are about the item Temp Tables
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
December 17, 2009 at 12:32 am
I have little doubt on the second option 'When the session that created it ends'.
If the global table is referenced by some other session at that time (like the table is being updated within a transaction), the table won't be dropped even if the created session ends.
December 17, 2009 at 5:29 am
I agree with subhrajit46.
If some other session is using the temp table, the temp table is not dropped when the session which created it was closed.
December 17, 2009 at 5:39 am
I agree the answer was 1,3,4.
Ideally the QOTD answers would be validated before posting, and there would be a way to get partial credit for the multiple choice answers. This is not the first multiple choice answer that is incorrect.
December 17, 2009 at 6:12 am
1,3,4
Even the answer explanation states that if the session that created it ends, but an active task is still referencing the table, it isn't dropped. So #2 is NOT correct.
December 17, 2009 at 7:08 am
I totally disagree !
This ("When the session that created it ends") unchanged and without further explanation is false, because if there is another connection that accesses this global table it will not be drop !
I want my points back !
(and one more time, all cases apply... tired of that method...)
December 17, 2009 at 7:35 am
I also got it wrong because I didn’t select the answer – “When the session that created it ends”. The reason that I didn’t select it was that all other options always cause the drop of the temporary table, but the ending of the session might cause it and it depends on other factors.
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
December 17, 2009 at 7:37 am
It would be much better if the options #2 and #3 where not separated from each other. Neither #2 nor #3 in itself does not trigger SQL Server to drop the global temporary table.
I think these options look better:
- When the instance restarts
- When the session that created it ends and all active tasks have stopped referencing them
- When specifically dropped by name
-----
To those people who answered '1,3,4': is the condition 'all active tasks have stopped referencing them' sufficient for dropping the table? The answer is 'No, it is not'. If the session that created the table still exists, the table remains even if that session is not active. So, if #2 is intended to be marked as incorrect, #3 should be marked as incorrect too.
(By the way, I also answered '1,3,4' 🙂 )
December 17, 2009 at 8:15 am
I agree with others that option #2 can easily be argued.
December 17, 2009 at 8:16 am
vk-kirov (12/17/2009)
To those people who answered '1,3,4': is the condition 'all active tasks have stopped referencing them' sufficient for dropping the table? The answer is 'No, it is not'. If the session that created the table still exists, the table remains even if that session is not active. So, if #2 is intended to be marked as incorrect, #3 should be marked as incorrect too.
(By the way, I also answered '1,3,4' 🙂 )
I'm not fully agree with you.
When the instance restarts : true : all connections are closed, so the table is released and drop; IMO, an untricky answer, just here for disturbing/confusing.
When the session that created it ends : false : i disagree, and msdn disagree too (msdn give the 'full context' - something like 'and was the only once to reference it )
When all active tasks have stopped referencing them : true : almost the word by word the definition given by msdn 🙂
When specifically dropped by name : true : and the drop will be done as soon as there is no more lock on it
December 17, 2009 at 8:28 am
Temporary tables are automatically dropped when they go out of scope, unless explicitly dropped by using DROP TABLE:
A local temporary table created in a stored procedure is dropped automatically when the stored procedure is finished. The table can be referenced by any nested stored procedures executed by the stored procedure that created the table. The table cannot be referenced by the process that called the stored procedure that created the table.
All other local temporary tables are dropped automatically at the end of the current session.
Global temporary tables are automatically dropped when the session that created the table ends and all other tasks have stopped referencing them. The association between a task and a table is maintained only for the life of a single Transact-SQL statement. This means that a global temporary table is dropped at the completion of the last Transact-SQL statement that was actively referencing the table when the creating session ended.
From BOL - it is inferred that once the process which created the global table has ended, so does the global table. The confusion comes from the statement which says AND all tasks have stopped referencing it. Once the creator has gone away, any existing SQL call will continue to work. After that call has completed the connection to the table is lost and once all connections are gone the table is gone.
The answer is misleading, as #2, by itself is not a true condition.
Just my humble opinion, but easily proven.
Steve Jimmo
Sr DBA
“If we ever forget that we are One Nation Under God, then we will be a Nation gone under." - Ronald Reagan
December 17, 2009 at 8:29 am
I didn't pick 1 because isn't the table dropped when the instance is shutting down as opposed to restarting (picky I know but...)
-------------------------------------------------------------------------
Normal chaos will be resumed as soon as possible. :crazy:
December 17, 2009 at 8:57 am
Dude76 (12/17/2009)
When all active tasks have stopped referencing them : true : almost the word by word the definition given by msdn 🙂
Consider an example.
1) A global temporary table is created in some session;
2) The session goes into sleep mode (but remains open);
3) There are no active tasks referencing the temp table.
Will SQL Server drop the table under such conditions?
December 17, 2009 at 9:06 am
I would agree that #2 is a little incomplete. That answer has been edited and points are being awarded back to everyone that has answered to date.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
December 17, 2009 at 9:07 am
John McC (12/17/2009)
I didn't pick 1 because isn't the table dropped when the instance is shutting down as opposed to restarting (picky I know but...)
A restart includes a shutdown and start up.
Whether it's dropped on shutdown or start up , how do you know? In between shutdown beginning and startup completing, you don't have any way to access the objects.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
Viewing 15 posts - 1 through 15 (of 35 total)
You must be logged in to reply to this topic. Login to reply