How to FInd Actual Row Count

Question

How to FInd Actual Row Count

New2SQL-343122

Default port

Points: 1461
More actions
June 27, 2012 at 12:53 am

#271758

Hi,
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'))
begin
set @name = char(@rows) + char(@rows) + char(@rows)
insert into T1 values (@rows, @Name)
set @rows = @rows + 1
end
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)
go
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)
go
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 */

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 1

New2SQL-343122 (6/27/2012)
Hi,
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'))
begin
set @name = char(@rows) + char(@rows) + char(@rows)
insert into T1 values (@rows, @Name)
set @rows = @rows + 1
end
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)
go
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)
go
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 */

"Records" aren't scanned - pages are.

AFAIK there's no record of the number of pages scanned by an operator. Actual Row Count is the number of rows returned by the operator.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

New2SQL-343122 Default port Points: 1461 More actions · Answer 2

Thanks for Reply,

My Question is how to find the Actual Row Count and where it will show me the acutal row count likewise Estimated Row Count in the Execution Plan by the time when Index Scan Influenced.