If user a creates a temp table the same time as user b different temp tables will be created in tempDB so this won't be an issue. That is unless you are using global temporary table (prefixed with ##)
Rich
-----Original Message-----
From: Terry Crosby [mailto:mssqldba-ezmlmshield-x85460023.[Email address protected]
Sent: 29 April 2008 14:24
To: LazyDBA Discussion
Subject: RE: Temp tables
Depends on your application. I have many users calling the same sproc, possibly at the same time, so truncating the same table(s) each time won't work for me. I don't know what to say about the bug in S2K5 (I'm still on S2K). Maybe it's fixed in sp3??
---------------------------------------------------------------------TO REPLY TO EVERYBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLYTo post a dba job: http://jobs.lazydba.comTo subscribe : http://www.LazyDBA.comTo unsubscribe: http://www.lazydba.com/unsubscribe.html
> To: [Email address protected] From: mssqldba-ezmlmshield-x55510407.[Email address protected] Date: Tue, 29 Apr 2008 09:19:06 -0400> Subject: RE: Temp tables> > What about reusing the same table multiple times in an application or SPROC? Less overhead to truncate and re-use than to drop and recreate, plus the bug in 2005.> > Stephen> > -----Original Message-----> From: Terry Crosby [mailto:mssqldba-ezmlmshield-x92522132.[Email address protected] > Sent: Tuesday, April 29, 2008 9:14 AM> To: LazyDBA Discussion> Subject: RE: Temp tables> > In S2K (I run it with sp4), if you use ## in a sproc or QA, when you exit the application, all references to the table are deleted. No need for cleanup.---------------------------------------------------------------------TO REPLY TO EVERYBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLYTo post a dba job: http://jobs.lazydba.comTo subscribe : http://www.LazyDBA.comTo unsubscribe: http://www.lazydba.com/unsubscribe.html> > > To: [Email address protected] From: mssqldba-ezmlmshield-x99889572.[Email address protected] Date: Mon, 28 Apr 2008 18:27:22 -0400> Subject: Re: Temp tables> > I prefer to truncate as well. And SQL2005 does not destroy temp tables correctly (don't remember if it was all temp tables or ones in SPROCs, but it is a bug) which can build up in the tempDB. If you are using hundreds a day, could be an issue, but DBCC FreeProCache dumps them. I don't have a big problem, so I periodically do this. Truncating the temp tables will minimize the problem. > > > > Stephen Dyckes> > > > > > > > -----Original Message-----> > From: Richard Douglas <mssqldba-ezmlmshield-x30781098.[Email address protected]> > To: LazyDBA Discussion <[Email address protected]> > Sent: Mon Apr 28 18:12:47 2008> > Subject: RE: Temp tables> > > > I personally tend to truncate. I know there is a Knowledge Base article> > > > on one of the versions of SQL Server that says if you drop and create> > > > temp tables too many times it causes all sorts of problems.> > > > > > > > Rich> > > > > > > > -----Original Message-----> > > > From: Jonathan Kerr> > > > [mailto:mssqldba-ezmlmshield-x26527120.[Email address protected] > > > > Sent: 28 April 2008 22:52> > > > To: LazyDBA Discussion> > > > Subject: Temp tables> > > > > > > > > > > > Just wondering on proples preference here, when you use # or ## tables> > > > and you need to clear all the data out of them to re use in a script do> > > > you: > > > > - ever bother using Truncate? > > > > - Or just drop and recreate?> > > > > > > > Cheers,> > > > > > > > JK..> > > > > > > > > > > > > > > > ---------------------------------------------------------------------> > > > TO REPLY TO EVERYBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY> > > > To post a dba job: http://jobs.lazydba.com> > > > To subscribe : http://www.LazyDBA.com> > > > To unsubscribe: http://www.lazydba.com/unsubscribe.html> > > > > > > > > > > > Confidentiality: This e-mail and any attachments are confidential and intended solely for the addressee(s) and may also be legally privileged or exempt from disclosure under applicable law. If you are not the intended addressee, or have received this email in error, please do not read the contents of the email or open attachments, notify the sender or email [Email address protected] immediately, delete it from your system and do not copy, disclose or otherwise act upon any part of this e-mail or its attachments.> > > > > > > > Security Warning: Internet communications are not guaranteed to be secure or virus free. Except to the extent Equidebt may not exclude its liability under law, Equidebt Limited does not accept responsibility for any loss arising from unauthorised access to, or interference with, any internet communications by any third party, or from the transmission of any viruses.> > > > > > > > Monitoring: Replies to this e-mail may be monitored by Equidebt Limited for operational or business reasons, within the scope of the law.> > > > > > > > Opinions: Any opinion or other information in this e-mail or its attachments that does not relate to the business of Equidebt Limited or is personal to the sender is not given or endorsed by Equidebt Limited. > > > > > > > > Information: Equidebt Limited. Registered in England and Wales (registered no. 2686796). Registered Office: Equity House, Ettington Road, Wellesbourne, Warwickshire, CV35 9GA> > > > > > > > > > ---------------------------------------------------------------------> > TO REPLY TO EVERYBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY> > To post a dba job: http://jobs.lazydba.com> > To subscribe : http://www.LazyDBA.com> > To unsubscribe: http://www.lazydba.com/unsubscribe.html> > > > > > > > -----------------------------------------> > The information contained in this e-mail message is intended only> for the personal and confidential use of the recipient(s) named> above. This message may be an attorney-client communication and/or> work product and as such is privileged and confidential. If the> reader of this message is not the intended recipient or an agent> responsible for delivering it to the intended recipient, you are> hereby notified that you have received this document in error and> that any review, dissemination, distribution, or copying of this> message is strictly prohibited. If you have received this> communication in error, please notify us immediately by e-mail, and> delete the original message.> > > ---------------------------------------------------------------------> TO REPLY TO EVERYBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY> To post a dba job: http://jobs.lazydba.com> To subscribe : http://www.LazyDBA.com> To unsubscribe: http://www.lazydba.com/unsubscribe.html> > _________________________________________________________________> Make i'm yours. Create a custom banner to support your cause.> http://im.live.com/Messenger/IM/Contribute/Default.aspx?source=TXT_TAGHM_MSN_Make_IM_Yours> > ---------------------------------------------------------------------> TO REPLY TO EVERYBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY> To post a dba job: http://jobs.lazydba.com> To subscribe : http://www.LazyDBA.com> To unsubscribe: http://www.lazydba.com/unsubscribe.html> > > > > ---------------------------------------------------------------------> TO REPLY TO EVERYBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY> To post a dba job: http://jobs.lazydba.com> To subscribe : http://www.LazyDBA.com> To unsubscribe: http://www.lazydba.com/unsubscribe.html> ---------------------------------------------------------------------TO REPLY TO EVERYBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLYTo post a dba job: http://jobs.lazydba.comTo subscribe : http://www.LazyDBA.comTo unsubscribe: http://www.lazydba.com/unsubscribe.html
_________________________________________________________________
Make i'm yours. Create a custom banner to support your cause.
http://im.live.com/Messenger/IM/Contribute/Default.aspx?source=TXT_TAGHM_MSN_Make_IM_Yours
---------------------------------------------------------------------
TO REPLY TO EVERYBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
To post a dba job: http://jobs.lazydba.com
To subscribe : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html
Confidentiality: This e-mail and any attachments are confidential and intended solely for the addressee(s) and may also be legally privileged or exempt from disclosure under applicable law. If you are not the intended addressee, or have received this email in error, please do not read the contents of the email or open attachments, notify the sender or email [Email address protected] immediately, delete it from your system and do not copy, disclose or otherwise act upon any part of this e-mail or its attachments.
Security Warning: Internet communications are not guaranteed to be secure or virus free. Except to the extent Equidebt may not exclude its liability under law, Equidebt Limited does not accept responsibility for any loss arising from unauthorised access to, or interference with, any internet communications by any third party, or from the transmission of any viruses.
Monitoring: Replies to this e-mail may be monitored by Equidebt Limited for operational or business reasons, within the scope of the law.
Opinions: Any opinion or other information in this e-mail or its attachments that does not relate to the business of Equidebt Limited or is personal to the sender is not given or endorsed by Equidebt Limited.
Information: Equidebt Limited. Registered in England and Wales (registered no. 2686796). Registered Office: Equity House, Ettington Road, Wellesbourne, Warwickshire, CV35 9GA
MS Sql Server LazyDBA home page