Index on Index

Hi Guys,
Recently, my friend faced an interesting interview question that asks
"Can we create an index on an index" .....
Is it possible to create that?If so how?
Could anyone trace it out...............
Thanks,
Dutt.....Hello,
We can not create an Index based on a INDEX object.
Thanks
Hari
"Dutt" <Mr.Dutt@.gmail.com> wrote in message
news:1180329868.716346.164660@.x35g2000prf.googlegroups.com...
> Hi Guys,
> Recently, my friend faced an interesting interview question that asks
> "Can we create an index on an index" .....
> Is it possible to create that?If so how?
> Could anyone trace it out...............
> Thanks,
> Dutt.....
>|||It's hard to know exactly what the interviewer meant.
When you create a clustered index on a table, the table is then part of the
clustered index. So in a sense, when you then build a nonclustered index on
a table with a clustered index, you're building an index on an index.
Nonclustered indexes contain clustered index keys, so that further seems to
imply that the nonclustered index is build 'on' the clustered index.
--
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://sqlblog.com
"Dutt" <Mr.Dutt@.gmail.com> wrote in message
news:1180329868.716346.164660@.x35g2000prf.googlegroups.com...
> Hi Guys,
> Recently, my friend faced an interesting interview question that asks
> "Can we create an index on an index" .....
> Is it possible to create that?If so how?
> Could anyone trace it out...............
> Thanks,
> Dutt.....
>|||Dutt,
It sure is a good interview question, because it allows the interviewed
person to explain the difference between the pure syntax (No, you cannot
create an index on an index), the semantics (you *can* create an index
on an indexed view) and index implementation (isn't an Indexed View just
an index structure with included columns?)
I can understand that a novice would not know how to answer the
question. My reply would probably be: Why would you ever want to index
an index? What would be the added value as opposed to creating an index
on the base table or view?
Gert-Jan
Dutt wrote:
> Hi Guys,
> Recently, my friend faced an interesting interview question that asks
> "Can we create an index on an index" .....
> Is it possible to create that?If so how?
> Could anyone trace it out...............
> Thanks,
> Dutt.....|||Instead of giving a yes or no answer, I'd either ask the interviewer to
clarify or just briefly explain how SQL Server does indexing and hope that
the interviewer is open-minded (i.e. doesn't have a preset answer in mind) to
see that the interviewee understands the fundamentals and therefore shouldn't
have any problem once the intent of the question becomes clear.
Linchi
"Dutt" wrote:
> Hi Guys,
> Recently, my friend faced an interesting interview question that asks
> "Can we create an index on an index" .....
> Is it possible to create that?If so how?
> Could anyone trace it out...............
> Thanks,
> Dutt.....
>