Covering Index

Hi,
How can one create covering index and what are the benefits of having covering index?
Thanks much in advance
Thank
GYKCheck out: http://www.sql-server-performance.com/covering_indexes.asp
--
HTH,
SriSamp
Please reply to the whole group only!
http://www32.brinkster.com/srisamp
"GYK" <anonymous@.discussions.microsoft.com> wrote in message
news:3F6E4037-F2B1-4A05-8310-B463ED14E0CA@.microsoft.com...
> Hi,
> How can one create covering index and what are the benefits of having
covering index?
> Thanks much in advance.
> Thanks
> GYK|||Hi,
There is no seperate syntax for Covering Index. Covering index is nothing
but, if your query (Select statement) reads the data from Index page
rather than scanning data page.
For result, Since the Query reads the Index page the execution will be much
faster.
Thanks
Hari
MCDBA
"GYK" <anonymous@.discussions.microsoft.com> wrote in message
news:3F6E4037-F2B1-4A05-8310-B463ED14E0CA@.microsoft.com...
> Hi,
> How can one create covering index and what are the benefits of having
covering index?
> Thanks much in advance.
> Thanks
> GYK|||Hi Hari,
Thanks for your reponse. But the way you have explained, it looks more like a non-clustered index. Even in non-clustered index, the data is first scanned through the index pages(pointers) and then the data is fetched. Am not able to understand the exact difference between them.
Thanks
GYK
-- Hari wrote: --
Hi,
There is no seperate syntax for Covering Index. Covering index is nothing
but, if your query (Select statement) reads the data from Index page
rather than scanning data page.
For result, Since the Query reads the Index page the execution will be much
faster.
Thanks
Hari
MCDBA
"GYK" <anonymous@.discussions.microsoft.com> wrote in message
news:3F6E4037-F2B1-4A05-8310-B463ED14E0CA@.microsoft.com...
> Hi,
>> How can one create covering index and what are the benefits of having
covering index?
>> Thanks much in advance.
>> Thanks
> GYK|||It is a bit similar to a clustered index, actually. Imagine you have an NC
index which contains all the columns you are referring to in your query.
Then there is no need for SQL Server to go an visit the data pages. The
index pages has all the information that the query refers to!
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"GYK" <anonymous@.discussions.microsoft.com> wrote in message
news:A3193BD2-EA46-4299-9ED1-6A2DA35C8E86@.microsoft.com...
> Hi Hari,
> Thanks for your reponse. But the way you have explained, it looks more
like a non-clustered index. Even in non-clustered index, the data is first
scanned through the index pages(pointers) and then the data is fetched. Am
not able to understand the exact difference between them.
> Thanks
> GYK
> -- Hari wrote: --
> Hi,
> There is no seperate syntax for Covering Index. Covering index is
nothing
> but, if your query (Select statement) reads the data from Index page
> rather than scanning data page.
> For result, Since the Query reads the Index page the execution will
be much
> faster.
>
> Thanks
> Hari
> MCDBA
> "GYK" <anonymous@.discussions.microsoft.com> wrote in message
> news:3F6E4037-F2B1-4A05-8310-B463ED14E0CA@.microsoft.com...
> > Hi,
> >> How can one create covering index and what are the benefits of
having
> covering index?
> >> Thanks much in advance.
> >> Thanks
> > GYK
>
>|||Hi,
Thanks guys, I think i got the concept :-)
Basically I guess it is just a concept, where in all the information provided by the users (in the where clause) is covered by the non-clustered index, and it refers to the index pages rather than the data pages. Hope am right here
Thanks very muc
Yogish|||Hi Yogesh,
Like Tibor mentioned , not only the where clause even the contents in select
clause will be picked from Index page.
eg:
If You have an index on empno, empname,empsex field in empdetails table,
you query is:
select empno,empname,empsex from empdetails where empno betweeen 100 and 110
and empsex='m'
In this case empno , empname and empsex will be picked from Index page, this
select statement never reads the data page to extract emp info.
Tibor,
Correct me if I am wrong.
Thanks
Hari
MCDBA
"GYK" <anonymous@.discussions.microsoft.com> wrote in message
news:43255A14-BA5A-4003-805F-57B2E9190AD6@.microsoft.com...
> Hi,
> Thanks guys, I think i got the concept :-)
> Basically I guess it is just a concept, where in all the information
provided by the users (in the where clause) is covered by the non-clustered
index, and it refers to the index pages rather than the data pages. Hope am
right here?
> Thanks very much
> Yogish|||> In this case empno , empname and empsex will be picked from Index page,
this
> select statement never reads the data page to extract emp info.
> Tibor,
> Correct me if I am wrong.
Correctomundo! :-)
Also, SQL Server can even combine several NC indexes so that the combination
of those covers a query. So we might want to differentiate between a covered
index (the index covers the whole query) and a query which is covered by
indexes (where (possibly) several indexes in combination covers a query).
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Hari" <hari_prasad_k@.hotmail.com> wrote in message
news:%23spIxkQqDHA.4004@.TK2MSFTNGP11.phx.gbl...
> Hi Yogesh,
> Like Tibor mentioned , not only the where clause even the contents in
select
> clause will be picked from Index page.
> eg:
> If You have an index on empno, empname,empsex field in empdetails table,
> you query is:
> select empno,empname,empsex from empdetails where empno betweeen 100 and
110
> and empsex='m'
> In this case empno , empname and empsex will be picked from Index page,
this
> select statement never reads the data page to extract emp info.
> Tibor,
> Correct me if I am wrong.
>
> Thanks
> Hari
> MCDBA
>
>
> "GYK" <anonymous@.discussions.microsoft.com> wrote in message
> news:43255A14-BA5A-4003-805F-57B2E9190AD6@.microsoft.com...
> > Hi,
> >
> > Thanks guys, I think i got the concept :-)
> >
> > Basically I guess it is just a concept, where in all the information
> provided by the users (in the where clause) is covered by the
non-clustered
> index, and it refers to the index pages rather than the data pages. Hope
am
> right here?
> >
> > Thanks very much
> > Yogish
>|||Hi Hari, Tibor
Thanks a lot. I get the picture now...
GYK|||I have a question to what Hari has sai
Hari said, the following
Table - Empdetail
Query - Select empno,empname,empsex from empdetails where empno betweeen 100 and 11
and empsex='m
Index - empno, empname,empse
My questio
=======Index is on 3 columns, but "Where" clause is on 2 columns, will still the index be used|||Yes.
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Prasanna" <anonymous@.discussions.microsoft.com> wrote in message
news:A200A69F-42AF-4233-AAA1-7A44C300E59C@.microsoft.com...
> I have a question to what Hari has said
> Hari said, the following
> Table - Empdetails
> Query - Select empno,empname,empsex from empdetails where
empno betweeen 100 and 110
> and empsex='m'
> Index - empno, empname,empsex
> My question
> ========> Index is on 3 columns, but "Where" clause is on 2 columns, will still the
index be used?
>