June 17, 2003 at 9:34 am
How can I get a clustered index seek from the below code?(currently a clustered index scan). I have move the CI to each column and still scans.
WHERE upper(Chapter) = upper( @board)
AND ltrim(upper( WEB_LOGIN)) = upper(@Board + '_' + @userid)
AND upper(PASSWORD) = UPPER(@Password)
AND LOGIN_DISABLED = 0
Chapter data is not unique.
Web_Login can be numbers, characters or any combination.
June 17, 2003 at 9:54 am
You won't get a clustered index seek on your where clause because of the functions operating against the table columns. e.g. upper(Chapter).
If you have your SQL Server collation configured as case insensitive, then you shouldn't need to do the upper conversion.
June 18, 2003 at 9:47 am
Here's a somewhat related question:
These two tables have the exact same data (approximately 10 million rows):
create table tImpression1 (userId numeric, time datetime, col1 int, col2 int, col3 int)
create table tImpression2 (userId numeric, time datetime, col1 int, col2 int, col3 int)
One of the tables has this index:
create unique clustered index tImpression1_1 on
tImpression1([userId],[time])
with fillfactor = 100, IGNORE_DUP_KEY
The other table has this index:
create unique clustered index tImpression2_1 on
tImpression2([userId],[time] desc)
with fillfactor = 100, IGNORE_DUP_KEY
I want to populate these two tables from the previous two tables:
create table tImpression1Grouped (
userId varchar(50), [date] datetime )
go
create table tImpression2Grouped (
userId varchar(50), [date] datetime )
go
Shouldn't the index with time column ordered in desc order perform better?
If not, why not? and what would make this group by statement perform best?
insert into tImpression1Grouped
select userId, max([time])
from tImpression1
group by userId
insert into tImpression2Grouped
select userId, max([time])
from tImpression2
group by userId
Thanks...
-j
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply