[dba-SQLServer] Deleting Global Temporary Tables

I prefer the second because it's shorter :-)

But try them both in Query Analyser and take a look at the execution plan costs!

Version1:  0.00641
Version 2: 0.000001

The simple version is 6410 times more efficient!!!!

-- 
Stuart
 

On 2 Jun 2008 at 14:15, David Emerson wrote:

> With further testing I was able to get both methods to work:
> 
> IF EXISTS (SELECT * FROM tempdb.dbo.sysobjects WHERE id = 
> object_id(N'tempdb.dbo.##tblCAFRClientAsset'))
> DROP TABLE dbo.##tblCAFRClientAsset
> 
> 
> If Object_Id('tempdb..##tblCAFRClientAsset') is Not Null
> DROP TABLE dbo.##tblCAFRClientAsset
> 
> Does one method have any advantages over the other?
> 
> David.
> 
> At 2/06/2008, Stuart wrote:
> >Try:
> >
> >If Object_Id('tempdb..##tblCAFRClientAsset') is Not Null
> >DROP TABLE dbo.##tblCAFRClientAsset
> >
> >--
> >Stuart
> >
> >On 2 Jun 2008 at 11:39, David Emerson wrote:
> >
> > > Group,
> > >
> > > I have this code in a sproc:
> > >
> > >          IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id =
> > > object_id(N'dbo.##tblCAFRClientAsset') AND OBJECTPROPERTY(id,
> > > N'IsUserTable') = 0)
> > >                  DROP TABLE dbo.##tblCAFRClientAsset
> > >
> > >          CREATE TABLE dbo.##tblCAFRClientAsset (
> > >                  [ClientAssetID] [numeric](10, 0) IDENTITY (1, 1) 
> > NOT NULL ,
> > >                  [AssetType] [varchar] (50) COLLATE 
> > Latin1_General_CI_AS NULL ,
> > >                  [Owner] [varchar] (10) COLLATE Latin1_General_CI_AS NULL ,
> > >                  [Description] [varchar] (50) COLLATE
> > > Latin1_General_CI_AS NULL ,
> > >                  [Description2] [varchar] (50) COLLATE
> > > Latin1_General_CI_AS NULL ,
> > >                  [AccountNo] [varchar] (20) COLLATE 
> > Latin1_General_CI_AS NULL ,
> > >                  [MarketValue] [money] NULL ,
> > >                  [Contributions] [money] NULL ,
> > >                  [ContributionsImp] [money] NULL ,
> > >                  [InterestRate] [real] NULL ,
> > >                  [PropertyType] [varchar] (50) COLLATE
> > > Latin1_General_CI_AS NULL ,
> > >                  [PropertyExpenses] [money] NULL ,
> > >                  [InvestmentType] [varchar] (50) COLLATE
> > > Latin1_General_CI_AS NULL ,
> > >                  [InsuranceType] [varchar] (50) COLLATE
> > > Latin1_General_CI_AS NULL ,
> > >                  [InsCover] [money] NULL ,
> > >                  [InsCoverImp] [money] NULL ,
> > >                  [InsPolicyTerm] [smallint] NULL ,
> > >                  [Comment] [varchar] (250) COLLATE 
> > Latin1_General_CI_AS NULL ,
> > >                  [SortOrder] [smallint] NULL
> > >                  )
> > >
> > > I am getting an error message when I run it "There is already an
> > > object named '##tblCAFRClientAsset' in the database.".  This is on
> > > the line trying to create the table.  It seems that the table is not
> > > being dropped.  When I try to run just the select portion of the
> > > first line nothing is returned.  How do I identify if the temporary
> > > table exists so I can drop it?
> > >
> > > Regards
> > >
> > > David Emerson
> > > Dalyn Software Ltd
> > > Wellington, New Zealand
> > > _______________________________________________
> > > dba-SQLServer mailing list
> > > dba-SQLServer at databaseadvisors.com
> > > http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
> > > http://www.databaseadvisors.com
> > >
> >
> >
> >_______________________________________________
> >dba-SQLServer mailing list
> >dba-SQLServer at databaseadvisors.com
> >http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
> >http://www.databaseadvisors.com
> _______________________________________________
> dba-SQLServer mailing list
> dba-SQLServer at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
> http://www.databaseadvisors.com
>