Excellent information! Tnx.

1) No, perhaps my example was not good - have a look at this

http://publib.boulder.ibm.com/infocenter/ids9help/index.jsp?topic=/com.ibm.adref.doc/adrefmst229.htm

and figure 17.

2 no, have a look at the execution plans when you do this

create database btree
go
use btree
create table btree
(pk char(10) not null constraint primarykey primary key, charcol char(20))
go
create index test on btree(charcol)
GO
declare @int int
declare @alpha int
declare @beta int
declare @holding char(3)
select @int=1
select @alpha=1
select @beta=1
while @int< 26
begin
while @alpha<26
begin
while @beta<26
begin
select @holding=char(64+@int)+char(64+@alpha)+char(64+@beta)
insert btree (pk, charcol) values (@holding, @holding)
select @beta=@beta+1
end
set @beta=1
select @alpha=@alpha+1
end
set @alpha=1
select @int=@int+1
end
set showplan_all on
select * from btree where pk like 'm%'

----------------------------------------------------------------------------
----------------------------------------------------------------------------
---------------------------- ----------- ----------- ----------- -----------
------------------- ------------------------------ -------------------------
----------------------------------------------------------------------------
---------------------------------------------------- -----------------------
--------- ------------------------ ------------------------ ----------------
-------- ----------- ------------------------ ------------------------------
-- -------- ------------------------------ -------- ------------------------
select * from btree where pk like 'm%'
2 1 0 NULL NULL
1
NULL 637.23169 NULL
NULL NULL 9.5086023E-3 NULL
NULL SELECT 0 NULL
|--Clustered Index Seek(OBJECT:([btree].[dbo].[btree].[primarykey]),
SEEK:([btree].[pk] >= 'Lþ' AND [btree].[pk] < 'N'),
WHERE:(like([btree].[pk], 'm%', NULL)) ORDERED FORWARD) 2 3
1 Clustered Index Seek Clustered Index Seek
OBJECT:([btree].[dbo].[btree].[primarykey]), SEEK:([btree].[pk] >= 'Lþ' AND
[btree].[pk] < 'N'), WHERE:(like([btree].[pk], 'm%', NULL)) ORDERED FORWARD
[btree].[charcol], [btree].[pk] 637.23169 8.5507222E-3
7.7945489E-4 37 9.3301767E-3
[btree].[charcol], [btree].[pk] NULL PLAN_ROW 0
1.0

(2 row(s) affected)


select * from btree (INDEX (test)) where pk like 'm%'
StmtText
StmtId NodeId Parent PhysicalOp LogicalOp
Argument
DefinedValues EstimateRows EstimateIO
EstimateCPU AvgRowSize TotalSubtreeCost OutputList
Warnings Type Parallel EstimateExecutions
----------------------------------------------------------------------------
-------------------- ----------- ----------- ----------- -------------------
----------- ------------------------------ ---------------------------------
----------------------------------------------------------- ----------------
---------------- ------------------------ ------------------------ ---------
--------------- ----------- ------------------------ -----------------------
--------- -------- ------------------------------ -------- -----------------
-------
select * from btree (INDEX (test)) where pk like 'm%'
156 1 0 NULL NULL
1
NULL 637.23169 NULL
NULL NULL 0.10877679 NULL
NULL SELECT 0 NULL
|--Index Scan(OBJECT:([btree].[dbo].[btree].[test]),
WHERE:(like([btree].[pk], 'm%', NULL))) 156 3 1
Index Scan Index Scan
OBJECT:([btree].[dbo].[btree].[test]), WHERE:(like([btree].[pk], 'm%',
NULL)), FORCEDINDEX [btree].[charcol], [btree].[pk] 637.23169
0.08868961 0.0172187 37 0.10590831
[btree].[charcol], [btree].[pk] NULL PLAN_ROW 0
1.0

(2 row(s) affected)



3) I don't know the answer to this, I would venture to say that it is by
design as most people don't want more than 100 or so rows returned. It could
be a function of hardware, I really don't know, but it is observable. You
start to notice it around 2000 or so rows.


--
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html

Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com

[quoted text, click to view]

BTW - I want to stress that these indexing seek vs scan observations are
only for

1) a clustered index and 2) when you query like this select * from tablename
where columnname like 'test%', it is not valid for this query

select * from tablename where columnname like '%test%'

This does a table scan no matter what index is on the table.

--
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html

Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com

[quoted text, click to view]