Interview question

Hi,
I had a job interview yesterday and they gave me a small test to complete.
One of the questions was the following... I was not sure what to answer...
If you have the following table:
CREATE TABLE [Customers] (
[FirstName] [varchar] (50) NOT NULL ,
[LastName] [varchar] (50) NOT NULL
) ON [PRIMARY]
And all the queries that you will have for this table are like these:
1. LastName ='Simpson'
2. FirstName ='John' and LastName='Smith'
3. LastName='Parker' and FirstName like 'J%'
4. LastName ='Owen'
5. FirstName ='Danny' and LastName='Jackson'
6. LastName='Owen' and FirstName= 'Michael'
7. LastName like 'A%'
.
(with different values for FirstName and LastName):
What kind of index (only one) do you think it would be more effective for
running those queries faster? Please, explain why.
Any ideas?
Thanks!!On Thu, 10 Mar 2005 10:07:23 -0500, Star wrote:
(snip)
>What kind of index (only one) do you think it would be more effective for
>running those queries faster? Please, explain why.
Hi Star,
A composite index on (lastname, firstname) would be best. Since there
are no other columns in this table, might as well make it clustered.
Reason: all queries presented include at least the last name (or the
start of the last name); some include (part of) the first name as well,
so each query can use this index to immediately jump to the part of the
index where matches are.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Clustered index on (lastname, firstname).
The common column in all queries is Lastname so it should appear first in
the index. Clustered because if you have only one index it usually makes
sense to cluster it.
David Portas
SQL Server MVP
--|||create nonclustered index ix_nc_customers_lastname_firstname on
customers(lastname, firstname)
go
- nonclustered
- composite
- first column should be LastName in order to allow 1, 4 and 7
AMB
"Star" wrote:

> Hi,
> I had a job interview yesterday and they gave me a small test to complete.
> One of the questions was the following... I was not sure what to answer...
> If you have the following table:
> CREATE TABLE [Customers] (
> [FirstName] [varchar] (50) NOT NULL ,
> [LastName] [varchar] (50) NOT NULL
> ) ON [PRIMARY]
> And all the queries that you will have for this table are like these:
> 1. LastName ='Simpson'
> 2. FirstName ='John' and LastName='Smith'
> 3. LastName='Parker' and FirstName like 'J%'
> 4. LastName ='Owen'
> 5. FirstName ='Danny' and LastName='Jackson'
> 6. LastName='Owen' and FirstName= 'Michael'
> 7. LastName like 'A%'
> ..
> (with different values for FirstName and LastName):
>
> What kind of index (only one) do you think it would be more effective for
> running those queries faster? Please, explain why.
>
> Any ideas?
> Thanks!!
>
>|||An Index on LastName, FirstName would be the most optimal as there is no
occurance of
FirstName column only.
The above index will be used by both the criterias which have
LastName,FirstName and just
LastName.
Gopi
"Star" <nospam@.nospam.com> wrote in message
news:e88xfLYJFHA.1396@.TK2MSFTNGP10.phx.gbl...
> Hi,
> I had a job interview yesterday and they gave me a small test to complete.
> One of the questions was the following... I was not sure what to answer...
> If you have the following table:
> CREATE TABLE [Customers] (
> [FirstName] [varchar] (50) NOT NULL ,
> [LastName] [varchar] (50) NOT NULL
> ) ON [PRIMARY]
> And all the queries that you will have for this table are like these:
> 1. LastName ='Simpson'
> 2. FirstName ='John' and LastName='Smith'
> 3. LastName='Parker' and FirstName like 'J%'
> 4. LastName ='Owen'
> 5. FirstName ='Danny' and LastName='Jackson'
> 6. LastName='Owen' and FirstName= 'Michael'
> 7. LastName like 'A%'
> .
> (with different values for FirstName and LastName):
>
> What kind of index (only one) do you think it would be more effective for
> running those queries faster? Please, explain why.
>
> Any ideas?
> Thanks!!
>|||Thank you, guys!
It helped a lot... now I know for the next time :(|||this interview wasn't in Orlando (or Celebration) Florida was it ?
Greg Jackson
PDX, Oregon|||definately Clustered on LastName, FirstName
GAJ