Indexing questions

Conceptual questions here...In BOL it mentions 'Rebuilding a clustered index does not rebuild associated nonclustered indexes unless the keyword ALL is specified.'. I'm wondering how this works as non-clustered indexes are built on top of clustered ones - is there in this case a significant slowdown in the speed of accessing non-clustered indexes? Also, I'm guessing here, but is the reason this might be useful is for when you want to disable and rebuild the nonclustered indexes in order to save space?
Cheers,
Paul Ibison, SQL Server MVP

Conceptually speaking, the leaf row of a non-clustered index needs to identify the data row uniquely. There are two ways to do it. First,you can use RID. The down side is that if the data row moves, the non-clustered index entry needs to be updated. Second, you can use clustered index key, if it is unique. The benefit is that if the data row moves say due to rebuild of a clustered index, nothing needs to be changed in the non-clustered index. Of course, in this case, to access data using non-clustered idnex, you will need to traverse the non-clustered index and then need to traverse the clustered index tree. Assuming that clustered index tree will be frequently accessed, it is lkely to be in the buffer pool. So you will incur few extra logical IOs per row access.

I doubt if the second method can be used to save space as it is likely that the clustered index key is longer than RID. The main benefit of the second method is that data row move does not cause the update of non-clustered index as long as the clustered key column value is not changed.

Let us see what others say.

Thanks

|||

Index rebuild (except for disabled index) does not change the logical structure of an index
(like changing a key, adding a new column, or change the sort order), it only changes the physical layout of an index on the disk. You can think of a physical index restructure, restoring

the original fillfactor and allocating the index pages in a right order removing fragmentation and compacting the index space. Therefore there is no need to rebuild the non-clustered index(es) when rebuilding a clustered index. This functionality is fully implemented in SQL Server 2005, and was already partially implemented in SQL Server 2000. We have added the keyword ALL to make sure customers can rebuild all indexes for a given table using one command (ease of use purpose). Clustered or non-clustered index rebuild may increase performance if the index is fragmented, especially when queries use

index scans.
Disabling a clustered index does not really save space. We still need to keep the old clustered index (marked as disabled) to make sure we can rebuild it. Disabling a non-clustered (NC) index brings some space benefit since we drop the old index and keep only its metadata, so when we rebuild an NC index we use either the base table or a clustered index. However this benefit can be easily reached by using two commands DROP NC index and CREATE NC index.

Thanks,

Mirek

|||Thanks Sunil,
this makes perfect sense. I hadn't seen it in this way before - if rebuilding the clustered index is only done to reorder the data (not to remove fragmentation) then there might be no performance gain from also rebuilding the non-clustered index, as the indexes are unlikely to be ordered in the same way.
Cheers,
Paul|||Thanks Mirek,
this makes sense. So in some cases where the table is large (disk space small) it might be useful to rebuild the clustered index without the ALL option, then subsequently disable and rebuild each non-clustered index.
Cheers,
Paul

indexing for replicated tables..

Hi All,
We are having a lot of tables in an online
database 'TRANS'. There are no clustered indexes in the
tables. There are only non-clustered indexes in the
tables. we do a weekly re-org of tables by creating and
dropping non-clustered indexes.
We are planning to configure transactional replication for
this database. For tables having non-clustered indexes
alone - dbcc dbreindex does not improve scan density by
much!
So, the stratergy of creating and dropping clustered
indexes work? If not, what is the best startegy to get the
same done?
My next question is,
is it possible to reindex tables in Merge replicated
databases?If your filegroup has more than one file, scan density is not a good
measure, use logical or extent scan fragmentation...
Almost all tables will benefit from a clustered index... perhaps you might
try adding a clustered to several of the tables and see if that helps...
Regarding Merge replication, you may re-do indexes at the client if you
wish... make sure you always have the unique index on the GUID...
"Bharath" <ramakrishnan.bharadhwaj@.citigroup.com> wrote in message
news:069801c33eda$5b27e640$a101280a@.phx.gbl...
> Hi All,
> We are having a lot of tables in an online
> database 'TRANS'. There are no clustered indexes in the
> tables. There are only non-clustered indexes in the
> tables. we do a weekly re-org of tables by creating and
> dropping non-clustered indexes.
> We are planning to configure transactional replication for
> this database. For tables having non-clustered indexes
> alone - dbcc dbreindex does not improve scan density by
> much!
> So, the stratergy of creating and dropping clustered
> indexes work? If not, what is the best startegy to get the
> same done?
> My next question is,
> is it possible to reindex tables in Merge replicated
> databases?

Indexing a view that contains text or ntext

I've created a view and started to create my first index (unique, clustered)
but got the following error:
"Cannot create index on view 'MyDB.dbo.myview'. It contains text, ntext,
image or xml columns. (Mircrosoft SQL Server, Error: 1492)"
There is only one field among the fields I'm using that is ntext.
Unfortunately I don't see how I can change it's data type since the largest
number of characters in this field is over 22,000 characters. What are my
options for creating indexes on this view? Is there a workaround that I can
do? Any suggestions?
Hi
From BOL: "Note Columns consisting of the ntext, text, or image data types
cannot be specified as columns for an index. In addition, a view cannot
include any text, ntext, or image columns, even if they are not referenced in
the CREATE INDEX statement."
Therefore with your current view you can not create an index. Does this
column need to be in the view?
John
"archuleta37" wrote:

> I've created a view and started to create my first index (unique, clustered)
> but got the following error:
> "Cannot create index on view 'MyDB.dbo.myview'. It contains text, ntext,
> image or xml columns. (Mircrosoft SQL Server, Error: 1492)"
> There is only one field among the fields I'm using that is ntext.
> Unfortunately I don't see how I can change it's data type since the largest
> number of characters in this field is over 22,000 characters. What are my
> options for creating indexes on this view? Is there a workaround that I can
> do? Any suggestions?

Indexing a view that contains text or ntext

I've created a view and started to create my first index (unique, clustered)
but got the following error:
"Cannot create index on view 'MyDB.dbo.myview'. It contains text, ntext,
image or xml columns. (Mircrosoft SQL Server, Error: 1492)"
There is only one field among the fields I'm using that is ntext.
Unfortunately I don't see how I can change it's data type since the largest
number of characters in this field is over 22,000 characters. What are my
options for creating indexes on this view? Is there a workaround that I can
do? Any suggestions?Hi
From BOL: "Note Columns consisting of the ntext, text, or image data types
cannot be specified as columns for an index. In addition, a view cannot
include any text, ntext, or image columns, even if they are not referenced i
n
the CREATE INDEX statement."
Therefore with your current view you can not create an index. Does this
column need to be in the view?
John
"archuleta37" wrote:

> I've created a view and started to create my first index (unique, clustere
d)
> but got the following error:
> "Cannot create index on view 'MyDB.dbo.myview'. It contains text, ntext,
> image or xml columns. (Mircrosoft SQL Server, Error: 1492)"
> There is only one field among the fields I'm using that is ntext.
> Unfortunately I don't see how I can change it's data type since the larges
t
> number of characters in this field is over 22,000 characters. What are my
> options for creating indexes on this view? Is there a workaround that I ca
n
> do? Any suggestions?

Indexing a view that contains text or ntext

I've created a view and started to create my first index (unique, clustered)
but got the following error:
"Cannot create index on view 'MyDB.dbo.myview'. It contains text, ntext,
image or xml columns. (Mircrosoft SQL Server, Error: 1492)"
There is only one field among the fields I'm using that is ntext.
Unfortunately I don't see how I can change it's data type since the largest
number of characters in this field is over 22,000 characters. What are my
options for creating indexes on this view? Is there a workaround that I can
do? Any suggestions?Hi
From BOL: "Note Columns consisting of the ntext, text, or image data types
cannot be specified as columns for an index. In addition, a view cannot
include any text, ntext, or image columns, even if they are not referenced in
the CREATE INDEX statement."
Therefore with your current view you can not create an index. Does this
column need to be in the view?
John
"archuleta37" wrote:
> I've created a view and started to create my first index (unique, clustered)
> but got the following error:
> "Cannot create index on view 'MyDB.dbo.myview'. It contains text, ntext,
> image or xml columns. (Mircrosoft SQL Server, Error: 1492)"
> There is only one field among the fields I'm using that is ntext.
> Unfortunately I don't see how I can change it's data type since the largest
> number of characters in this field is over 22,000 characters. What are my
> options for creating indexes on this view? Is there a workaround that I can
> do? Any suggestions?

Indexes vs Clustered Indexes

What is the difference please?On Sun, 17 Sep 2006 18:34:59 GMT, Curt wrote:

Quote:

Originally Posted by

>What is the difference please?

Hi Curt,

SQL Server has two types of indexes, clustered and non-clustered.

The similarity is that both are organised as a B-tree, with root and
intermediate pages holding indexed values and pointers to pages on the
next lower level.

On the lowest ("leaf") level, pages in a clustered index contain the
values of the indexed columns of each individual row in the table; in
addition, the values of all other columns are also stored in the same
leaf page. This means that all data in a table is stored in the leaf
pages of a clustered index - this is the reason why only one clustered
index per table is allowed, and why in simplified descriptions, the
table data is said to be "stored in clustered index order". This is only
true as long as you realise that the order is logical, achieved by
following pointer chains - in reality, the data will probably be
scattered all over the sectors of your hard disk, or even spread over
multiple spindles.

In a non-clustered index, the leaf pages also contain the values of the
indexed columns of each row - but in this case, these values are only
accompanied by a pointer to where the full row is stored. If the table
has a clustered index, this pointer is the clustered index key. If the
table has no clustered index (such a table is called a "heap"), this
pointer is a combination of file number, page number, and row number of
the page, pointing to the original location of the row. The row might
have moved; in that case a forwarding pointer in the original location
will point to the current location.

--
Hugo Kornelis, SQL Server MVP

indexes urgent please

HI
I created clustered index on 115 million records which runs 6 hours after i
created index now it is running for 16 minutes which is fine.
if i run that query statement(update statement) 1st time it will take 16
minutes and second time it will take 50 minutes
and third time it will take 1hour 30 minutes.this is weired can any one
please let me know what to do
again i am doing rebuilding and updatestats each time.
Thanks in advance
Message posted via droptable.com
http://www.droptable.com/Uwe/Forums.aspx/sql-server-replication/200706/1
This is probably the wrong group for this, however you need to make sure
that you are not updating a clustered key as this will cause movement and/or
page splits in your clustered index. There may also be lots of
locking/blocking going on depending on how many rows you are updating.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"pardhiveswar via droptable.com" <u8956@.uwe> wrote in message
news:736fe741cfaeb@.uwe...
> HI
> I created clustered index on 115 million records which runs 6 hours after
> i
> created index now it is running for 16 minutes which is fine.
> if i run that query statement(update statement) 1st time it will take 16
> minutes and second time it will take 50 minutes
> and third time it will take 1hour 30 minutes.this is weired can any one
> please let me know what to do
> again i am doing rebuilding and updatestats each time.
> Thanks in advance
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forums.aspx/sql-server-replication/200706/1
>

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 mak
e
also non-clustered index ...
Thank youB.J
No, you don't need to create NC on the column that has already CI.
http://www.sql-server-performance.c...red_indexes.asp
http://www.sql-server-performance.c...red_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 sam
e 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 com
pared 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 i
s
> whether clustered index only order table physically or it also creates ind
ex
> ..., and if it orders table phusically only may I gain something when I ma
ke
> 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
> 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[vbcol=seagreen]
>|||> Can you elaborate a little bit under what circumstances it can be benefica
l?
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 ne
ed to look at 100000
pages to do the SUM.
Now create a non-clustered index on c1. And do the same SELECT. A non-cluste
red index has one row in
the leaf page per row in the table. For the sake of discussion, say that 100
0 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 lea
f level of the NC index
and only have to read 100 pages. We cut down from reading 100000 pages to 10
0 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 benefica
l?
> 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 i
n
> 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...
benefical?[vbcol=seagreen]
> 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...
the[vbcol=seagreen]
benefical?[vbcol=seagreen]
in[vbcol=seagreen]
the[vbcol=seagreen]
pages[vbcol=seagreen]
asking[vbcol=seagreen]
creates[vbcol=seagreen]
I[vbcol=seagreen]
>|||I got a significantly lower cost with the non-clustered index. Here's a repr
oduction sript. It uses
only 10000 rows to make it faster to run, along with the STATISTICS IO resul
t 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 re
ads 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.gb
l...
> 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 sa
me
> as well.
>
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote i
n
> 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
>

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 youB.J
No, you don't need to create NC on the column that has already CI.
http://www.sql-server-performance.com/clustered_indexes.asp
http://www.sql-server-performance.com/nonclustered_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
> 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
>|||> 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
>>
>|||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
> >>
> >>
> >
> >
>|||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...
>> > 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
>> >>
>> >>
>> >
>> >
>>
>

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
>