Indexes

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
B.J
No, you don't need to create NC on the column that has already CI.
http://www.sql-server-performance.co...ed_indexes.asp
http://www.sql-server-performance.co...ed_indexes.asp
"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
|||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
|||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[vbcol=seagreen]
> 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...
index[vbcol=seagreen]
is[vbcol=seagreen]
index[vbcol=seagreen]
make
>
|||> 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
> same column *can* be
> 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...
> same column *can* be
> This is because the nc index
> will fit on the leaf level
> compared to of SLQ server
> index
> is
> index
> make
>
|||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...[vbcol=seagreen]
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...[vbcol=seagreen]
the[vbcol=seagreen]
benefical?[vbcol=seagreen]
in[vbcol=seagreen]
the[vbcol=seagreen]
pages[vbcol=seagreen]
asking[vbcol=seagreen]
creates[vbcol=seagreen]
I
>
|||I got a significantly lower cost with the non-clustered index. Here's a reproduction sript. It uses
only 10000 rows to make it faster to run, along with the STATISTICS IO result I got:
SET NOCOUNT ON
USE tempdb
GO
CREATE TABLE t(c1 int identity, c2 char(5000) default 'a')
--Insert values
DECLARE @.i int
SET @.i = 1
WHILE @.i <= 10000
BEGIN
INSERT t DEFAULT VALUES
SET @.i = @.i + 1
END
GO
CREATE CLUSTERED INDEX t_cl ON t(c1)
SET STATISTICS IO ON
GO
SELECT SUM(c1) FROM t
--Table 't'. Scan count 1, logical reads 10002, physical reads 1, read-ahead reads 10016.
GO
CREATE NONCLUSTERED INDEX t_nc ON t(c1)
GO
SELECT SUM(c1) FROM t
--Table 't'. Scan count 1, logical reads 14, physical reads 0, read-ahead reads 0.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Uri Dimant" <urid@.iscar.co.il> wrote in message news:%23ItcPwnGFHA.3964@.TK2MSFTNGP14.phx.gbl...
> 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...
> benefical?
> fit per data page (makes it
> need to look at 100000
> non-clustered index has one row in
> 1000 rows fit per index
> I.e., the leaf level of
> leaf level of the NC index
> 100 pages.
> news:u9l07emGFHA.2976@.TK2MSFTNGP15.phx.gbl...
> the
> benefical?
> in
> the
> pages
> asking
> creates
> I
>