David Emerson
newsgrps at dalyn.co.nz
Sun Jun 1 22:16:17 CDT 2008
Ah hah. And I know just what I can do with the extra 0.006409
seconds ( they don't call it instant coffee for nothing)
At 2/06/2008, you wrote:
>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
> >
>
>
>_______________________________________________
>dba-SQLServer mailing list
>dba-SQLServer at databaseadvisors.com
>http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
>http://www.databaseadvisors.com