2q - Indexs and Optimize...

Mikael:
When people ask me this question (#1), I always say, look at the phone
book. You can think of a phone book as being like a 2-way composite
index on Lastname (Ascending) and Firstname (Ascending), in that order:
Adams, John
Bryant, Frank
Coolidge, Adam
Coolidge, Calvin
Coolidge, Zach
If you ordered it as Lastname (Ascending) and Firstname (Descending):
Adams, John
Bryant, Frank
Coolidge, Zach
Coolidge, Calvin
Coolidge, Adam
If you ordered it as First name (Ascending) ...
Coolidge, Adam
Coolidge, Calvin
Bryant, Frank
Adams, John
Coolidge, Zach
Get the idea?
MC wrote:
> As far as 1) goes theres a world of difference. I would suggest reading
> about indexes, if you have the time 'Inside Sql server 2000' would probably
> give you all the answers you need.
> 2) you can use Profiler tool. Trace queries and possibly filter them by
> duration value. If you put over 3000 you'll see only queries that last over
> 3 seconds...
>
> MC
> "Mikael Syska" <news01@.syska.dk> wrote in message
> news:OBZvoioXHHA.1388@.TK2MSFTNGP05.phx.gbl...
>
"Mikael Syska" <news01@.syska.dk> wrote in message
news:eS7AKXsXHHA.3952@.TK2MSFTNGP04.phx.gbl...
> Yes, I think so ...
> So an index on FirstName DESC, LastName ASC
> and the query ...
> SELECT Firname, LastName FROM t1 ORDER BY LastName, FirstName
> would are the list after that index ... ASC on FirstName and DESC on
> LastName ?
No, ASC on both. An ORDER BY is ASCending unless otherwise specified.
However, the query plans will be different.
Given this script:
create table order_test
( fname varchar(10),
lname varchar(10))
insert into order_test values ('albert', 'zilch')
insert into order_test values ('albert', 'allen')
insert into order_test values ('albert', 'Brend')
insert into order_test values ('Bill', 'zilch')
insert into order_test values ('Bill', 'Cramden')
insert into order_test values ('Wendy', 'Jillson')
create index fname_idx on order_test (fname asc, lname desc)
go
set showplan_all on
go
select * from order_test order by fname, lname
select * from order_test order by fname, lname desc
drop table order_test

Since we're ordering the results in "reverse" of the index, we have an extra
scan in there.
creating an INDEX with DESC is useful when you will generally return the
results in DESC order. It's just an optimization step.
select * from order_test order by fname, lname
|--Sort(ORDER BY[testing].[dbo].[order_test].[fname] ASC,
[testing].[dbo].[order_test].[lname] ASC))
|--Index Scan(OBJECT[testing].[dbo].[order_test].[fname_idx]))
select * from order_test order by fname, lname desc
|--Index Scan(OBJECT[testing].[dbo].[order_test].[fname_idx]), ORDERED
FORWARD)
[vbcol=seagreen]
> and if I had a other index on FirstName ASC and did a
> SELECT Firname, LastName FROM t1 ORDER BY FirstName
> it would sort DESC ... right ? if i guess they are grouped ...
> But are there any performance gain by doing it that way (then some columns
> would be added more than 1 time) ?
> best regrads
> Mikael Syska
> David Markle wrote:
Greg Moore
SQL Server DBA Consulting
sql (at) greenms.com http://www.greenms.com