Index Help

Hi,
Can any please explain to me the diff.?
Use Northwind
I created a Non-Clustered index on "LastName' and "FirstName" (Combined)
Select * from customers where LastName = 'Hello' and FirstName = 'World'
Select * from customers where FirstName = 'World' and LastName = 'Hello'
Does that make the difference?
I really would like to know the Clustered Index part as well...!!!!!
I created a Clustered index on "LastName' and "FirstName" (Combined)
Select * from customers where LastName = 'Hello' and FirstName = 'World'
Select * from customers where FirstName = 'World' and LastName = 'Hello'
Does that make any difference as well if I filter that FirstName first?
Thanks for the answer on both clustered and Non clustered
EdIt doesn't make any difference. The query optimiser is smart enough to
rearrange the order of the parts of the WHERE clause in order to best
use the indexes it has available. See example below:
create table dbo.mikejunk
(
LastName varchar(50) not null,
FirstName varchar(50) not null
)
go
create nonclustered index IX_mikejunk on dbo.mikejunk (LastName,
FirstName)
go
insert into dbo.mikejunk (LastName, FirstName) values ('Ben-Gan',
'Itzik')
insert into dbo.mikejunk (LastName, FirstName) values ('Delaney',
'Kalen')
insert into dbo.mikejunk (LastName, FirstName) values ('Tripp',
'Kimberly')
insert into dbo.mikejunk (LastName, FirstName) values ('Waymire',
'Richard')
insert into dbo.mikejunk (LastName, FirstName) values ('Kelly',
'Andrew')
insert into dbo.mikejunk (LastName, FirstName) values ('Bertrand',
'Aaron')
insert into dbo.mikejunk (LastName, FirstName) values ('Sommarskog',
'Erland')
insert into dbo.mikejunk (LastName, FirstName) values ('Epprecht',
'Mike')
insert into dbo.mikejunk (LastName, FirstName) values ('Kass', 'Steve')
insert into dbo.mikejunk (LastName, FirstName) values ('Karaszi',
'Tibor')
go
set showplan_text on
go
select * from dbo.mikejunk where LastName = 'Delaney' and FirstName
= 'Kalen'
select * from dbo.mikejunk where FirstName = 'Kalen' and LastName =
'Delaney'
go
set showplan_text off
go
drop table dbo.mikejunk
go
If you have a look at the execution plans you'll see they are both the same:
StmtText
----
--
select * from dbo.mikejunk where LastName = 'Delaney' and FirstName
= 'Kalen'
StmtText
----
----
|--Table Scan(OBJECT:([tempdb].[dbo].[mikejunk]),
WHERE:([tempdb].[dbo].[mikejunk].[LastName]=[@.1] AND
[tempdb].[dbo].[mikejunk].[FirstName]=[@.2]))
StmtText
----
--
select * from dbo.mikejunk where FirstName = 'Kalen' and LastName =
'Delaney'
StmtText
----
----
|--Table Scan(OBJECT:([tempdb].[dbo].[mikejunk]),
WHERE:([tempdb].[dbo].[mikejunk].[FirstName]=[@.1] AND
[tempdb].[dbo].[mikejunk].[LastName]=[@.2]))
Same with the clustered index (try changing the "nonclustered" to
"clustered" in the test script above and look at the results).
However, if you have more columns than just LastName & FirstName in your
table then it probably becomes a moot point, since at the very least the
query engine will need to do a lookup into the clustered index to get
the other columns for the SELECT list or it may just decide that it's
more efficient to do a table/clustered index scan rather than look at
the nonclustered index at all.
*mike hodgson*
blog: http://sqlnerd.blogspot.com
Ed wrote:

>Hi,
> Can any please explain to me the diff.?
>Use Northwind
>I created a Non-Clustered index on "LastName' and "FirstName" (Combined)
>Select * from customers where LastName = 'Hello' and FirstName = 'World'
>Select * from customers where FirstName = 'World' and LastName = 'Hello'
>Does that make the difference?
>I really would like to know the Clustered Index part as well...!!!!!
>I created a Clustered index on "LastName' and "FirstName" (Combined)
>Select * from customers where LastName = 'Hello' and FirstName = 'World'
>Select * from customers where FirstName = 'World' and LastName = 'Hello'
>Does that make any difference as well if I filter that FirstName first?
>Thanks for the answer on both clustered and Non clustered
>Ed
>
>|||Oops, sorry - I just noticed that the showplan results I posted were
from a run I did with no indexes. <blush> Here are the showplan
results I meant to post (using the nonclustered index):
StmtText
----
--
select * from dbo.mikejunk where LastName = 'Delaney' and FirstName
= 'Kalen'
StmtText
----
----
--
|--Index S(OBJECT:([tempdb].[dbo].[mikejunk].[IX_mikejunk]),
SEEK:([tempdb].[dbo].[mikejunk].[LastName]=[@.1] AND
[tempdb].[dbo].[mikejunk].[FirstName]=[@.2]) ORDERED FORWARD)
StmtText
----
--
select * from dbo.mikejunk where FirstName = 'Kalen' and LastName =
'Delaney'
StmtText
----
----
--
|--Index S(OBJECT:([tempdb].[dbo].[mikejunk].[IX_mikejunk]),
SEEK:([tempdb].[dbo].[mikejunk].[LastName]=[@.2] AND
[tempdb].[dbo].[mikejunk].[FirstName]=[@.1]) ORDERED FORWARD)
*mike hodgson*
blog: http://sqlnerd.blogspot.com
Mike Hodgson wrote:
> It doesn't make any difference. The query optimiser is smart enough
> to rearrange the order of the parts of the WHERE clause in order to
> best use the indexes it has available. See example below:
> create table dbo.mikejunk
> (
> LastName varchar(50) not null,
> FirstName varchar(50) not null
> )
> go
> create nonclustered index IX_mikejunk on dbo.mikejunk (LastName,
> FirstName)
> go
> insert into dbo.mikejunk (LastName, FirstName) values ('Ben-Gan',
> 'Itzik')
> insert into dbo.mikejunk (LastName, FirstName) values ('Delaney',
> 'Kalen')
> insert into dbo.mikejunk (LastName, FirstName) values ('Tripp',
> 'Kimberly')
> insert into dbo.mikejunk (LastName, FirstName) values ('Waymire',
> 'Richard')
> insert into dbo.mikejunk (LastName, FirstName) values ('Kelly',
> 'Andrew')
> insert into dbo.mikejunk (LastName, FirstName) values ('Bertrand',
> 'Aaron')
> insert into dbo.mikejunk (LastName, FirstName) values
> ('Sommarskog', 'Erland')
> insert into dbo.mikejunk (LastName, FirstName) values ('Epprecht',
> 'Mike')
> insert into dbo.mikejunk (LastName, FirstName) values ('Kass',
> 'Steve')
> insert into dbo.mikejunk (LastName, FirstName) values ('Karaszi',
> 'Tibor')
> go
> set showplan_text on
> go
> select * from dbo.mikejunk where LastName = 'Delaney' and
> FirstName = 'Kalen'
> select * from dbo.mikejunk where FirstName = 'Kalen' and LastName
> = 'Delaney'
> go
> set showplan_text off
> go
> drop table dbo.mikejunk
> go
>
> If you have a look at the execution plans you'll see they are both the
> same:
> StmtText
> ---
--
> select * from dbo.mikejunk where LastName = 'Delaney' and
> FirstName = 'Kalen'
> StmtText
> ---
----
--
> |--Table Scan(OBJECT:([tempdb].[dbo].[mikejunk]),
> WHERE:([tempdb].[dbo].[mikejunk].[LastName]=[@.1] AND
> [tempdb].[dbo].[mikejunk].[FirstName]=[@.2]))
>
> StmtText
> ---
--
> select * from dbo.mikejunk where FirstName = 'Kalen' and LastName
> = 'Delaney'
> StmtText
> ---
----
--
> |--Table Scan(OBJECT:([tempdb].[dbo].[mikejunk]),
> WHERE:([tempdb].[dbo].[mikejunk].[FirstName]=[@.1] AND
> [tempdb].[dbo].[mikejunk].[LastName]=[@.2]))
> Same with the clustered index (try changing the "nonclustered" to
> "clustered" in the test script above and look at the results).
> However, if you have more columns than just LastName & FirstName in
> your table then it probably becomes a moot point, since at the very
> least the query engine will need to do a lookup into the clustered
> index to get the other columns for the SELECT list or it may just
> decide that it's more efficient to do a table/clustered index scan
> rather than look at the nonclustered index at all.
> --
> *mike hodgson*
> blog: http://sqlnerd.blogspot.com
>
> Ed wrote:
>