SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
 Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Count Scan		  New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

vishal_7
Posting Yak Master

127 Posts
Posted - 08/21/2006 :  12:10:04  Show Profile  Reply with Quote
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

United Kingdom
12543 Posts
Posted - 08/21/2006 :  12:16:10  Show Profile  Visit nr's Homepage  Reply with Quote
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 - 08/21/2006 :  12:18:50  Show Profile  Reply with Quote
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 - 08/21/2006 :  12:22:20  Show Profile  Reply with Quote
Would it make it any difference if AUTOID_TEMP were clustered?
Go to Top of Page

nr
SQLTeam MVY

United Kingdom
12543 Posts
Posted - 08/21/2006 :  12:36:27  Show Profile  Visit nr's Homepage  Reply with Quote
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 - 08/21/2006 :  12:38:28  Show Profile  Reply with Quote
I see, Thanks a lot!
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000