Re: Index Speed use of REPLACE in clause

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

From: Gert-Jan Strik (sorry_at_toomuchspamalready.nl)
Date: 12/17/04 

Date: Fri, 17 Dec 2004 15:58:59 +0100

Thanks,

Gert-Jan

Adam Machanic wrote:
>
> Confirmed:
>
> Microsoft SQL Server 2000 - 8.00.760 (Intel X86) Dec 17 2002 14:22:05
> Copyright (c) 1988-2003 Microsoft Corporation Standard Edition on Windows
> NT 5.0 (Build 2195: Service Pack 4)
>
> |--Compute
> Scalar(DEFINE:([Customers].[CompanyNameNoSpace]=replace([Customers].[Company
> Name], Convert(space(1)), Convert(''))))
> |--Bookmark Lookup(BOOKMARK:([Bmk1000]),
> OBJECT:([Northwind].[dbo].[Customers]))
> |--Index
> Seek(OBJECT:([Northwind].[dbo].[Customers].[IX_Customers_CompanyNameNoSpace]
> ), SEEK:([Customers].[CompanyNameNoSpace]=Convert([@1])) ORDERED FORWARD)
>
> :-)
>
> --
> Adam Machanic
> SQL Server MVP
> http://www.sqljunkies.com/weblog/amachanic
> --
>
> "Gert-Jan Strik" <sorry@toomuchspamalready.nl> wrote in message
> news:41C2E4BC.868B93CB@toomuchspamalready.nl...
> > I could be wrong. I thought that maybe indexes on computed columns would
> > not be automatically used in Standard Edition, just as indexes on views
> > are not automatically used (unless you use Enterprise Edition or provide
> > special hints).
> >
> > But as I said: I could be wrong. Maybe someone with Standard Edition can
> > test this example to see if an index seek is used.
> >
> > USE Northwind
> > GO
> > ALTER TABLE Customers ADD CompanyNameNoSpace AS
> > Replace(CompanyName,space(1),'')
> > GO
> > CREATE INDEX IX_Customers_CompanyNameNoSpace ON
> > Customers(CompanyNameNoSpace)
> > GO
> > SET SHOWPLAN_TEXT ON
> > GO
> > SELECT * FROM Customers WHERE CompanyNameNoSpace='EasternConnection'
> > GO
> > SET SHOWPLAN_TEXT OFF
> >
> > Gert-Jan
> >
> > Adam Machanic wrote:
> > >
> > > "Gert-Jan Strik" <sorry@toomuchspamalready.nl> wrote in message
> > > news:41C22D18.3AE1AF4E@toomuchspamalready.nl...
> > > > Yes, a little test on the Northwind database suggests that this should
> > > > work (provided you are using Enterprise Edition).
> > >
> > > What's Enterprise Edition have to do with it?
> > >
> > > --
> > > Adam Machanic
> > > SQL Server MVP
> > > http://www.sqljunkies.com/weblog/amachanic
> > > --

Relevant Pages

  • Re: newbie: how convert nvchar to datetime?
    ... SQL Server MVP ... How to I alter this column to become a DateTime field type? ... Arithmetic Overflow Error converting expression to data type datetime. ...
    (microsoft.public.sqlserver.mseq)
  • Re: Alter Database Move column
    ... Tibor Karaszi, SQL Server MVP ... > Is it possible to move a column using an SQL script? ... > alter table MasterStationTest ...
    (microsoft.public.sqlserver.server)
  • Re: Alter Table for 39 million rows
    ... either way the disk space will have to be allocated and pages ... SQL Server MVP ... > I'm trying to alter a table column from tinyint to a smallint, however, the> table contains> 39 million rows and running the alter table takes a long ...
    (microsoft.public.sqlserver.programming)
  • Re: alter column but setting the default value
    ... SQL Server MVP ... "Sam Martin" wrote in message ... > alter table dbo.mytable ... > add mydatecolumn datetime null ...
    (microsoft.public.sqlserver.programming)
  • Re: Constraints
    ... Tibor Karaszi, SQL Server MVP ... ALTER TABLE tblname ... CHECK CONSTRAINT ALL ... ALTER TABLE JOURNAL NOCHECK CONSTRAINT ALL ...
    (microsoft.public.sqlserver.server)