Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Count Scan

Author  Topic 

vishal_7
Posting Yak Master

127 Posts

Posted - 2006-08-21 : 12:10:04
Hi,

I have a simple temp table which looks like this:

CREATE TABLE #t7e07c01fa80143ff84cb14a2307809f7
(
[AUTOID_TEMP] [int] IDENTITY (1, 1) NOT NULL,
...
)
ON [PRIMARY]

ALTER TABLE #t7e07c01fa80143ff84cb14a2307809f7 ADD
CONSTRAINT [PK_t7e07c01fa80143ff84cb14a2307809f7] PRIMARY KEY NONCLUSTERED
(
[AUTOID_TEMP]
)
ON [PRIMARY]

Insert Into #t7e07c01fa80143ff84cb14a2307809f7
Selet columns... from t....

-- get total records
Declare @TotalCount as int
select @TotalCount = count(AUTOID_TEMP) from #t7e07c01fa80143ff84cb14a2307809f7

Now the above last statement does a index scan. I am new to indexing/tuning and was wondering if its normal - if so why and can I somehow enhance this?

Thanks

nr
SQLTeam MVY

12543 Posts

Posted - 2006-08-21 : 12:16:10
What else would you expect it to do?
You've asked it to count the number of entries and the way to do that with the minimum disk reads is to scan the nunclustered index.

You could do count(*) which would do the same thing at the moment but would take advantage of any better indexes if they were added.


==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.
Go to Top of Page

vishal_7
Posting Yak Master

127 Posts

Posted - 2006-08-21 : 12:18:50
Thanks, I didnt know that this is the default behaviour (still trying to learn).
Go to Top of Page

vishal_7
Posting Yak Master

127 Posts

Posted - 2006-08-21 : 12:22:20
Would it make it any difference if AUTOID_TEMP were clustered?
Go to Top of Page

nr
SQLTeam MVY

12543 Posts

Posted - 2006-08-21 : 12:36:27
Yep - would probably be slower as it would have to scan the data pages.
But as you only have one column there probably wouldn't be much difference.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.
Go to Top of Page

vishal_7
Posting Yak Master

127 Posts

Posted - 2006-08-21 : 12:38:28
I see, Thanks a lot!
Go to Top of Page
   

- Advertisement -