>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
>
>
> 
>

> 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:
>
>>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
>>
>>
>> 
>>