Prev: select statement ,sum with calculation
Next: Return a specific string based on positions of Y/N data
From: Ron on 6 Dec 2009 06:59
Thanks again all. Yes Erland I understand your meaning, the bottom line being
that the index usage, whether the clustered and not clustered will ultimately
be whether the optimizer choses one or the other based on the data held. I
suppose by adding another nonclustered, the choice is there assuming that I
can afford the overhead expense of another index.

Gert thanks for the tip on String Summary Statistics

Regards all.

"Erland Sommarskog" wrote:

> Gert-Jan Strik (sorrytoomuchspamalready(a)xs4all.nl) writes:
> > You must be using a different version (probably 2008?), because however
> > hard I try, I keep getting an Index Scan on SQL Server 2005, even with
> > using sp_executesql.
>
> Actually, I ran it on SQL 2005. I get the same result on SQL 2008.
>
> Maybe the collation matters. My collation is Finnish_Swedish_CS_AS.
>
>
> --
> Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se
>
> Links for SQL Server Books Online:
> SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
> SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
> SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
>
> .
>
From: Erland Sommarskog on 6 Dec 2009 09:28
Erland Sommarskog (esquel(a)sommarskog.se) writes:
> Gert-Jan Strik (sorrytoomuchspamalready(a)xs4all.nl) writes:
>> You must be using a different version (probably 2008?), because however
>> hard I try, I keep getting an Index Scan on SQL Server 2005, even with
>> using sp_executesql.
>
> Actually, I ran it on SQL 2005. I get the same result on SQL 2008.
>
> Maybe the collation matters. My collation is Finnish_Swedish_CS_AS.

No, the collation does not matter. This script gives an Index Scan for
both columns:

CREATE TABLE bludder
(OrderID int NOT NULL PRIMARY KEY,
CustID_CI nchar(5) COLLATE Finnish_Swedish_CI_AS NOT NULL,
CustID_CS nchar(5) COLLATE Finnish_Swedish_CS_AS NOT NULL)

INSERT bludder(OrderID, CustID_CI, CustID_CS)
SELECT OrderID, CustomerID, CustomerID
FROM Northwind..Orders
go
CREATE INDEX CI ON bludder(CustID_CI)
CREATE INDEX CS ON bludder(CustID_CS)
go
exec sp_executesql
N'select count(*) from bludder WHERE CustID_CI LIKE ''%'' + @s',
N'@s nvarchar(20)', '%W%'
exec sp_executesql
N'select count(*) from bludder WHERE CustID_CS LIKE ''%'' + @s',
N'@s nvarchar(20)', '%W%'
go
DROP TABLE bludder

But if I make the column char(5), I get an Index Scan.

But it is not that simple that this can only happen with the Unicode
data types. I first ran into this with a varchar(80) column. (And the
index "seek" was highly undesired, as I was working with a way to
make such searches faster, described in chapter 17 in this book:
http://www.sqlservermvpdeepdives.com/ and of which the royalties
goes to War Child International.)

--
Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

From: Ron on 6 Dec 2009 11:13
:-) I have this book erland, a good cause.

"Erland Sommarskog" wrote:

> Erland Sommarskog (esquel(a)sommarskog.se) writes:
> > Gert-Jan Strik (sorrytoomuchspamalready(a)xs4all.nl) writes:
> >> You must be using a different version (probably 2008?), because however
> >> hard I try, I keep getting an Index Scan on SQL Server 2005, even with
> >> using sp_executesql.
> >
> > Actually, I ran it on SQL 2005. I get the same result on SQL 2008.
> >
> > Maybe the collation matters. My collation is Finnish_Swedish_CS_AS.
>
> No, the collation does not matter. This script gives an Index Scan for
> both columns:
>
> CREATE TABLE bludder
> (OrderID int NOT NULL PRIMARY KEY,
> CustID_CI nchar(5) COLLATE Finnish_Swedish_CI_AS NOT NULL,
> CustID_CS nchar(5) COLLATE Finnish_Swedish_CS_AS NOT NULL)
>
> INSERT bludder(OrderID, CustID_CI, CustID_CS)
> SELECT OrderID, CustomerID, CustomerID
> FROM Northwind..Orders
> go
> CREATE INDEX CI ON bludder(CustID_CI)
> CREATE INDEX CS ON bludder(CustID_CS)
> go
> exec sp_executesql
> N'select count(*) from bludder WHERE CustID_CI LIKE ''%'' + @s',
> N'@s nvarchar(20)', '%W%'
> exec sp_executesql
> N'select count(*) from bludder WHERE CustID_CS LIKE ''%'' + @s',
> N'@s nvarchar(20)', '%W%'
> go
> DROP TABLE bludder
>
> But if I make the column char(5), I get an Index Scan.
>
> But it is not that simple that this can only happen with the Unicode
> data types. I first ran into this with a varchar(80) column. (And the
> index "seek" was highly undesired, as I was working with a way to
> make such searches faster, described in chapter 17 in this book:
> http://www.sqlservermvpdeepdives.com/ and of which the royalties
> goes to War Child International.)
>
> --
> Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se
>
> Links for SQL Server Books Online:
> SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
> SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
> SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
>
> .
>
From: Gert-Jan Strik on 6 Dec 2009 11:36
Erland Sommarskog wrote:
> No, the collation does not matter.
> But if I make the column char(5), I get an Index Scan.
> But it is not that simple that this can only happen with the Unicode
> data types.

That is so unusual to see a new phenomenon when it has been around for
decades. With your repro script I even get this virtually unbounded
index seek on SQL Server 7.0. It goes to show how infrequently I use
sp_executesql (which definitely seems to be a prerequisite), especially
in combination with unicode.

Here are three lines of the 2005 query plan (somewhat simplified):

|--Compute Scalar(DEFINE:(Expr1003=LikeRangeStart('%'+@s),
Expr1004=LikeRangeEnd('%'+@s), Expr1005=LikeRangeInfo('%'+@s)))
| |--Constant Scan
|--Index Seek(OBJECT:(bludder.CI), SEEK:(bludder.CustID_CI BETWEEN
Expr1003 AND Expr1004) ORDERED)

Which brings me to the question: what do the functions LikeRangeStart,
LikeRangeEnd and LikeRangeInfo actually do? A quick search on the
Internet only produces query plans, no explanation.

As to the effectiveness of such a seek: it does not seem very effective,
at least not for this situation. I just finished reading your chapter 17
yesterday, and your setup gives a bit more data to play around, so I
tried it on this 1,000,000 row persons table. The index "seek" does
exactly the same amount of logical and physical reads as the index scan.
So in this case, the seek doesn't add any value. My guess is, that these
LikeRange functions only add value when the leading character is a
regular character.

--
Gert-Jan
SQL Server MVP
From: Gert-Jan Strik on 6 Dec 2009 11:42
Oh, there is one other important thing I noticed.

The scenario with the index scan used parallellism. The scenario with
sp_executesql that used LikeRangeStart and LikeRangeEnd in an index seek
dit not use parallellism.

It is no surprise that the parallel plan outperformed the serial plan,
even if it was only by 1.9% on my machine.

--
Gert-Jan
SQL Server MVP
First  |  Prev  |  Next  |  Last
Pages: 1 2 3 4 5
Prev: select statement ,sum with calculation
Next: Return a specific string based on positions of Y/N data