From the Below Script, how to find Actual row count at Step 4 & Step 5.
Basially, need to validated between Index Scan and Index Seek.
Using SQL Server 2000.
/* Script Begin */
Step 1 :
Create table T1 (id int, name varchar(10))
Step 2 :
declare @rows int, @name varchar(3)
set @rows = ascii('A')
while (@rows <= ascii('Z'))
set @name = char(@rows) + char(@rows) + char(@rows)
insert into T1 values (@rows, @Name)
set @rows = @rows + 1
Step 3 :
-- Table Scan will appear when no Index exists
SET STATISTICS XML OFF
select * from T1
Step 4 :
-- Creating Clustered index on ID Column to appear Index Scan while searching for Name Field
Create Clustered index IX_ID on T1(ID)
select * from T1 (nolock) where Name = 'BBB'
-- Question : In this Stage, technically I want to prove how many records were scanned in the table T1 ( Acutal Row Count ).
-- This is not appearing in the Execution Plan ( Acutual Row Count ) using SQL Server 2000
Step 5 :
-- Creating NonClustered index on Name Column to appear Index Seek while searching for Name Field
Create NonClustered index IX_Name on T1(Name)
select * from T1(nolock) where Name = 'BBB'
-- How to technically prove to others that Index Seek touching one record from the Table T1 and Index Scan touching all the records as step 4
/* Script End */