Re: Indexes

From: Uri Dimant (urid_at_iscar.co.il)
Date: 02/24/05 

Date: Thu, 24 Feb 2005 16:18:43 +0200

Hi ,Tibor
In theoretics , looks fine.
I did the test based on example that you gave me,
In both queries ( after creation NCI) the optimizer was used clustered
indexe scan. Its ok, because we have a clustered index but I was expecting
to see that the optimizer was looked at NCI. The execution time is the same
as well.

"Tibor Karaszi" <tibor_please.no.email_karaszi@hotmail.nomail.com> wrote in
message news:eNJ5zHnGFHA.2736@TK2MSFTNGP09.phx.gbl...
> > Can you elaborate a little bit under what circumstances it can be
benefical?
>
> I'll try:-). Example:
>
> CREATE TABLE t(c1 int, c2 char(5000))
>
> Clustered index on c1. With the row size I specified above, only one row
fit per data page (makes it
> easy to do the maths). Imagine 100000 rows.
>
> SELECT SUM(c1) FROM t
>
> Above need to look at every row. Only one row fit per page, so SQL Server
need to look at 100000
> pages to do the SUM.
>
> Now create a non-clustered index on c1. And do the same SELECT. A
non-clustered index has one row in
> the leaf page per row in the table. For the sake of discussion, say that
1000 rows fit per index
> page. You have 100000 rows in the table and 1000 rows fit per index page.
I.e., the leaf level of
> the index has about 100 pages. SQL Server can do the SUM by scanning the
leaf level of the NC index
> and only have to read 100 pages. We cut down from reading 100000 pages to
100 pages.
>
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
>
> "Uri Dimant" <urid@iscar.co.il> wrote in message
news:u9l07emGFHA.2976@TK2MSFTNGP15.phx.gbl...
> > Hi, Tibor
> >> The clustered index does indeed come with an index tree. NC index on
the
> > same column *can* be
> >> beneficial,
> >
> > Can you elaborate a little bit under what circumstances it can be
benefical?
> > I have just finished test with a larger table that one column has both
> > indexes and even if I got fewer rows it was using clustrerd index.
> >
> >
> >
> >
> > "Tibor Karaszi" <tibor_please.no.email_karaszi@hotmail.nomail.com> wrote
in
> > message news:%231D6PFmGFHA.2900@TK2MSFTNGP10.phx.gbl...
> >> The clustered index does indeed come with an index tree. NC index on
the
> > same column *can* be
> >> beneficial, however, in case you can cover queries using that nc index.
> > This is because the nc index
> >> only contains the columns over which you create the index, so more rows
> > will fit on the leaf level
> >> of that index. Imagine a scan now, where SQL Server can read fewer
pages
> > compared to of SLQ server
> >> needed to read the data pages.
> >>
> >> --
> >> Tibor Karaszi, SQL Server MVP
> >> http://www.karaszi.com/sqlserver/default.asp
> >> http://www.solidqualitylearning.com/
> >>
> >>
> >> "B.J." <BJ@discussions.microsoft.com> wrote in message
> >> news:FAED71F9-78BA-4E8D-B5A6-2BDB076B8AF6@microsoft.com...
> >> > Hi,
> >> >
> >> > Do I need to create non-clustered index on same columns as clustered
> > index
> >> > has, if I have already created clustered index ? What I am really
asking
> > is
> >> > whether clustered index only order table physically or it also
creates
> > index
> >> > ..., and if it orders table phusically only may I gain something when
I
> > make
> >> > also non-clustered index ...
> >> >
> >> > Thank you
> >>
> >>
> >
> >
>
>

Relevant Pages

  • Re: Indexes
    ... With the row size I specified above, only one row fit per data page (makes it ... SQL Server can do the SUM by scanning the leaf level of the NC index ... >> The clustered index does indeed come with an index tree. ...
    (microsoft.public.sqlserver.server)
  • Re: Traversing Leaf Levels to satisfy a query
    ... And one should also keep in mind that there's nothing in the non-leaf from where SQL Server can deduct that "this is the only row". ... I.e., for this to apply, we need to work on unique indexes (and appropriate predicate i query), which also happens to cover the query in question. ... appears that Index Keys are propigated to all levels of the index, ... of those keys happens to be on an intermediate Leaf level, ...
    (microsoft.public.sqlserver.server)
  • Re: Where are indexes of views?
    ... The index on a view is a clustered index, so the results of the materialized ... See SQL Server Books Online ... topic Clustered Indexes for a description of the index leaf level. ...
    (microsoft.public.sqlserver.programming)
  • Re: Covering indexes versus column order in Delaney
    ... In the leaf level of the index, you have every single lastname, ... covered query because SQL Server can get the the result by just scanning the ... even better one, because all the John's will be together, and SQL Server ... In neither case will SQL Server have to go to the data, so both are covering ...
    (microsoft.public.sqlserver.programming)
  • Re: Indexed Views
    ... Columnist, SQL Server Professional ... "Steve Z" wrote:> Pg 443 of Inside MS SQL Server 2000... ... > The first index you must build on a view is a clustered index, and since the> clestered index contains all the data at its leaf level, this index actually> does materialize the view. ...
    (microsoft.public.sqlserver.server)
Loading