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.
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 #t7e07c01fa80143ff84cb14a2307809f7Selet columns... from t....-- get total recordsDeclare @TotalCount as intselect @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. |
|
|
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). |
|
|
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? |
|
|
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. |
|
|
vishal_7
Posting Yak Master
127 Posts |
Posted - 2006-08-21 : 12:38:28
|
I see, Thanks a lot! |
|
|
|
|
|