difference between rebuild index and drop/recreate index

Hello:
What is the difference between rebuilding index (whether clustered or non
clustered) and dropping & recreating it?
Thanks!
QSame thing. In the end, the same code in SQL Server is executed. First the create code, then the
drop code.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Q" <Q@.discussions.microsoft.com> wrote in message
news:466A4880-AA9B-47A7-8EE8-63B81C809EBE@.microsoft.com...
> Hello:
> What is the difference between rebuilding index (whether clustered or non
> clustered) and dropping & recreating it?
> Thanks!
> Q|||There is a good explanation in the BOL. See "DBCC DBREINDEX".
AMB
"Q" wrote:
> Hello:
> What is the difference between rebuilding index (whether clustered or non
> clustered) and dropping & recreating it?
> Thanks!
> Q|||Hello Tibor:
Thanks!
Q
"Tibor Karaszi" wrote:
> Same thing. In the end, the same code in SQL Server is executed. First the create code, then the
> drop code.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Q" <Q@.discussions.microsoft.com> wrote in message
> news:466A4880-AA9B-47A7-8EE8-63B81C809EBE@.microsoft.com...
> > Hello:
> >
> > What is the difference between rebuilding index (whether clustered or non
> > clustered) and dropping & recreating it?
> >
> > Thanks!
> >
> > Q
>
>|||Hi Alejandro:
Thanks!
Q
"Alejandro Mesa" wrote:
> There is a good explanation in the BOL. See "DBCC DBREINDEX".
>
> AMB
>
> "Q" wrote:
> > Hello:
> >
> > What is the difference between rebuilding index (whether clustered or non
> > clustered) and dropping & recreating it?
> >
> > Thanks!
> >
> > Q|||"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> schrieb
im Newsbeitrag news:eeGe$t2RFHA.576@.TK2MSFTNGP15.phx.gbl...
> Same thing. In the end, the same code in SQL Server is executed. First
the create code, then the
> drop code.
Hmmm, you sure you got the order right? :-)
robert|||> Hmmm, you sure you got the order right? :-)
Yep. This is in order to be able to do a ROLLBACK. This is why you need as much free space as the
table size in order to rebuild a clustered index.
Copy the data, then drop the old data. If something goes bad during this process so we need a
ROLLBACK, just remove the new data (as much as has been done), and the old is still there. :-)
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Robert Klemme" <bob.news@.gmx.net> wrote in message news:OBD$LrXSFHA.1096@.tk2msftngp13.phx.gbl...
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> schrieb
> im Newsbeitrag news:eeGe$t2RFHA.576@.TK2MSFTNGP15.phx.gbl...
>> Same thing. In the end, the same code in SQL Server is executed. First
> the create code, then the
>> drop code.
> Hmmm, you sure you got the order right? :-)
> robert
>|||"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> schrieb
im Newsbeitrag news:OJHG%23vXSFHA.3144@.tk2msftngp13.phx.gbl...
> > Hmmm, you sure you got the order right? :-)
> Yep. This is in order to be able to do a ROLLBACK. This is why you need
as much free space as the
> table size in order to rebuild a clustered index.
> Copy the data, then drop the old data. If something goes bad during this
process so we need a
> ROLLBACK, just remove the new data (as much as has been done), and the
old is still there. :-)
Ouch! /me smacks myself on the forehead.
I should've known better - sorry for the noise.
Kind regards
robert
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Robert Klemme" <bob.news@.gmx.net> wrote in message
news:OBD$LrXSFHA.1096@.tk2msftngp13.phx.gbl...
> >
> > "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com>
schrieb
> > im Newsbeitrag news:eeGe$t2RFHA.576@.TK2MSFTNGP15.phx.gbl...
> >> Same thing. In the end, the same code in SQL Server is executed.
First
> > the create code, then the
> >> drop code.
> >
> > Hmmm, you sure you got the order right? :-)
> >
> > robert
> >
>|||> I should've known better - sorry for the noise.
We all slip from time to time. :-)
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Robert Klemme" <bob.news@.gmx.net> wrote in message news:d4j0bm$7ej$1@.domitilla.aioe.org...
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> schrieb
> im Newsbeitrag news:OJHG%23vXSFHA.3144@.tk2msftngp13.phx.gbl...
>> > Hmmm, you sure you got the order right? :-)
>> Yep. This is in order to be able to do a ROLLBACK. This is why you need
> as much free space as the
>> table size in order to rebuild a clustered index.
>> Copy the data, then drop the old data. If something goes bad during this
> process so we need a
>> ROLLBACK, just remove the new data (as much as has been done), and the
> old is still there. :-)
> Ouch! /me smacks myself on the forehead.
> I should've known better - sorry for the noise.
> Kind regards
> robert
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "Robert Klemme" <bob.news@.gmx.net> wrote in message
> news:OBD$LrXSFHA.1096@.tk2msftngp13.phx.gbl...
>> >
>> > "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com>
> schrieb
>> > im Newsbeitrag news:eeGe$t2RFHA.576@.TK2MSFTNGP15.phx.gbl...
>> >> Same thing. In the end, the same code in SQL Server is executed.
> First
>> > the create code, then the
>> >> drop code.
>> >
>> > Hmmm, you sure you got the order right? :-)
>> >
>> > robert
>> >
>>
>

difference between rebuild index and drop/recreate index

Hello:
What is the difference between rebuilding index (whether clustered or non
clustered) and dropping & recreating it?
Thanks!
QSame thing. In the end, the same code in SQL Server is executed. First the c
reate code, then the
drop code.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Q" <Q@.discussions.microsoft.com> wrote in message
news:466A4880-AA9B-47A7-8EE8-63B81C809EBE@.microsoft.com...
> Hello:
> What is the difference between rebuilding index (whether clustered or non
> clustered) and dropping & recreating it?
> Thanks!
> Q|||There is a good explanation in the BOL. See "DBCC DBREINDEX".
AMB
"Q" wrote:

> Hello:
> What is the difference between rebuilding index (whether clustered or non
> clustered) and dropping & recreating it?
> Thanks!
> Q|||Hello Tibor:
Thanks!
Q
"Tibor Karaszi" wrote:

> Same thing. In the end, the same code in SQL Server is executed. First the
create code, then the
> drop code.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Q" <Q@.discussions.microsoft.com> wrote in message
> news:466A4880-AA9B-47A7-8EE8-63B81C809EBE@.microsoft.com...
>
>|||Hi Alejandro:
Thanks!
Q
"Alejandro Mesa" wrote:
[vbcol=seagreen]
> There is a good explanation in the BOL. See "DBCC DBREINDEX".
>
> AMB
>
> "Q" wrote:
>|||"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> schrieb
im Newsbeitrag news:eeGe$t2RFHA.576@.TK2MSFTNGP15.phx.gbl...
> Same thing. In the end, the same code in SQL Server is executed. First
the create code, then the
> drop code.
Hmmm, you sure you got the order right? :-)
robert|||> Hmmm, you sure you got the order right? :-)
Yep. This is in order to be able to do a ROLLBACK. This is why you need as m
uch free space as the
table size in order to rebuild a clustered index.
Copy the data, then drop the old data. If something goes bad during this pro
cess so we need a
ROLLBACK, just remove the new data (as much as has been done), and the old i
s still there. :-)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Robert Klemme" <bob.news@.gmx.net> wrote in message news:OBD$LrXSFHA.1096@.tk2msftngp13.phx.g
bl...
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> schrieb
> im Newsbeitrag news:eeGe$t2RFHA.576@.TK2MSFTNGP15.phx.gbl...
> the create code, then the
> Hmmm, you sure you got the order right? :-)
> robert
>|||"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> schrieb
im Newsbeitrag news:OJHG%23vXSFHA.3144@.tk2msftngp13.phx.gbl...
> Yep. This is in order to be able to do a ROLLBACK. This is why you need
as much free space as the
> table size in order to rebuild a clustered index.
> Copy the data, then drop the old data. If something goes bad during this
process so we need a
> ROLLBACK, just remove the new data (as much as has been done), and the
old is still there. :-)
Ouch! /me smacks myself on the forehead.
I should've known better - sorry for the noise.
Kind regards
robert

> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Robert Klemme" <bob.news@.gmx.net> wrote in message
news:OBD$LrXSFHA.1096@.tk2msftngp13.phx.gbl...
schrieb[vbcol=seagreen]
First[vbcol=seagreen]
>|||> I should've known better - sorry for the noise.
We all slip from time to time. :-)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Robert Klemme" <bob.news@.gmx.net> wrote in message news:d4j0bm$7ej$1@.domitilla.aioe.org...[
vbcol=seagreen]
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> schrieb
> im Newsbeitrag news:OJHG%23vXSFHA.3144@.tk2msftngp13.phx.gbl...
> as much free space as the
> process so we need a
> old is still there. :-)
> Ouch! /me smacks myself on the forehead.
> I should've known better - sorry for the noise.
> Kind regards
> robert
>
> news:OBD$LrXSFHA.1096@.tk2msftngp13.phx.gbl...
> schrieb
> First
>[/vbcol]

difference between rebuild index and drop/recreate index

Hello:
What is the difference between rebuilding index (whether clustered or non
clustered) and dropping & recreating it?
Thanks!
Q
Same thing. In the end, the same code in SQL Server is executed. First the create code, then the
drop code.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Q" <Q@.discussions.microsoft.com> wrote in message
news:466A4880-AA9B-47A7-8EE8-63B81C809EBE@.microsoft.com...
> Hello:
> What is the difference between rebuilding index (whether clustered or non
> clustered) and dropping & recreating it?
> Thanks!
> Q
|||There is a good explanation in the BOL. See "DBCC DBREINDEX".
AMB
"Q" wrote:

> Hello:
> What is the difference between rebuilding index (whether clustered or non
> clustered) and dropping & recreating it?
> Thanks!
> Q
|||Hello Tibor:
Thanks!
Q
"Tibor Karaszi" wrote:

> Same thing. In the end, the same code in SQL Server is executed. First the create code, then the
> drop code.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Q" <Q@.discussions.microsoft.com> wrote in message
> news:466A4880-AA9B-47A7-8EE8-63B81C809EBE@.microsoft.com...
>
>
|||Hi Alejandro:
Thanks!
Q
"Alejandro Mesa" wrote:
[vbcol=seagreen]
> There is a good explanation in the BOL. See "DBCC DBREINDEX".
>
> AMB
>
> "Q" wrote:
|||"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> schrieb
im Newsbeitrag news:eeGe$t2RFHA.576@.TK2MSFTNGP15.phx.gbl...
> Same thing. In the end, the same code in SQL Server is executed. First
the create code, then the
> drop code.
Hmmm, you sure you got the order right? :-)
robert
|||> Hmmm, you sure you got the order right? :-)
Yep. This is in order to be able to do a ROLLBACK. This is why you need as much free space as the
table size in order to rebuild a clustered index.
Copy the data, then drop the old data. If something goes bad during this process so we need a
ROLLBACK, just remove the new data (as much as has been done), and the old is still there. :-)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Robert Klemme" <bob.news@.gmx.net> wrote in message news:OBD$LrXSFHA.1096@.tk2msftngp13.phx.gbl...
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> schrieb
> im Newsbeitrag news:eeGe$t2RFHA.576@.TK2MSFTNGP15.phx.gbl...
> the create code, then the
> Hmmm, you sure you got the order right? :-)
> robert
>
|||"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> schrieb
im Newsbeitrag news:OJHG%23vXSFHA.3144@.tk2msftngp13.phx.gbl...
> Yep. This is in order to be able to do a ROLLBACK. This is why you need
as much free space as the
> table size in order to rebuild a clustered index.
> Copy the data, then drop the old data. If something goes bad during this
process so we need a
> ROLLBACK, just remove the new data (as much as has been done), and the
old is still there. :-)
Ouch! /me smacks myself on the forehead.
I should've known better - sorry for the noise.
Kind regards
robert

> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Robert Klemme" <bob.news@.gmx.net> wrote in message
news:OBD$LrXSFHA.1096@.tk2msftngp13.phx.gbl...[vbcol=seagreen]
schrieb[vbcol=seagreen]
First
>
|||> I should've known better - sorry for the noise.
We all slip from time to time. :-)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Robert Klemme" <bob.news@.gmx.net> wrote in message news:d4j0bm$7ej$1@.domitilla.aioe.org...
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> schrieb
> im Newsbeitrag news:OJHG%23vXSFHA.3144@.tk2msftngp13.phx.gbl...
> as much free space as the
> process so we need a
> old is still there. :-)
> Ouch! /me smacks myself on the forehead.
> I should've known better - sorry for the noise.
> Kind regards
> robert
> news:OBD$LrXSFHA.1096@.tk2msftngp13.phx.gbl...
> schrieb
> First
>
sql

Difference Between Clustered Index and non Clustered Index

hi all, can u tell me what is basic difference beween
clusterd index and non clusterd index?
Thanks in advance
hrishikeshHello Hrishikesh !
Quioted from DBA-Forum:
--
Assuming SQL 7 or 2K:
A clustered index orders the table and is loaded into memory. SQL Svr
accesses data in the table by directly accessing the Index.
A non clustered index does NOT order the data. It establishes a list of the
locations of the data pages containing the column(s) of the index.
So a clustered index organizes the data for maximum efficient access and a
non clustered index does not.
If you create an index on a field that is NOT in the WHERE clause of a
query, you gain absolutely nothing. Actually, you take a hit because you are
wasting drive space, query time and memory space on a useless index.
If you create an index (Clustered or non clustered) that IS in the WHERE
clause, you will gain a benefit because the index points to the data.
--
HTH, Jens Süßmeyer.|||BOL says:
A clustered index determines the physical order of data in a table. A
clustered index is analogous to a telephone directory, which arranges data
by last name. Because the clustered index dictates the physical storage
order of the data in the table, a table can contain only one clustered
index. However, the index can comprise multiple columns (a composite index),
like the way a telephone directory is organized by last name and first name.
Nonclustered indexes have the same B-tree structure as clustered indexes,
with two significant differences:
a.. The data rows are not sorted and stored in order based on their
nonclustered keys.
b.. The leaf layer of a nonclustered index does not consist of the data
pages.
"Hrishikesh Musale" <musaleh@.mahindrabt.com> wrote in message
news:0da301c36d4f$2ca746f0$a101280a@.phx.gbl...
> hi all, can u tell me what is basic difference beween
> clusterd index and non clusterd index?
> Thanks in advance
> hrishikeshsql

Diff betw Clustered and Non-Clustered Index and their Application

Hi experts,
I have being reading up on index, in particular the difference between a
cluster index and non-cluster index, and how they should be used. However th
e
more I read the more I am.
Basically if anyone can advise me on this question:
- Which kind of index should I use for PRIMARY KEY column, a clustered or
non-clustered index?
- Similarly which kind of index should I use for FOREIGN KEY column, a
clustered or non-clustered index?
A background of how I become .
My understanding of clustered index is, basically rows with column value
that fit an index value are grouped together. In another words, 1 index
value, multiple rows (1-to-many relationship).
Furthermore these rows are sorted within the group itself.
Therefore when SQL Server queries the DB along a column with clustered
index, it will be able to quickly find and return all the rows.
As for non-clustered index, it is basically an index that points directly to
that row. So, 1 index value, 1 row (1-to-1 relationship "ideally").
However on one article, it recommends that primary keys should be clustered
(which is what SQL Server does by default). But on another article, it
recommends that pimary keys should be non-clustered! And that is when I
become .
Since primary keys are unique, so rows are not grouped under a clustered
index at all. SQL Server cannot find more than one row under that index. So
there is no performance value gain.
However non-clustered index is more suited to set on the primary key column.
Afterall one-index value, one primary key.
So I think it makes more sense to set non-cluster index for primary key,
while reserving the clustered index for foreign keys, especially the child
table's foreign key (back to the parent row). The power of the clustered
index should not be wasted on primary key column.
Is my understanding correct?
Could u please kindly advise. TQ very much in advance.Hi
This is very,very big and important issue.
Start with
http://www.sql-server-performance.c...red_indexes.asp
http://www.sql-server-performance.c...red_indexes.asp
http://www.sql-server-performance.c...ing_indexes.asp
"HardKhor" <HardKhor@.discussions.microsoft.com> wrote in message
news:BABFB6D4-09F7-482B-89F9-DF10DFCF1242@.microsoft.com...
> Hi experts,
> I have being reading up on index, in particular the difference between a
> cluster index and non-cluster index, and how they should be used. However
the
> more I read the more I am.
> Basically if anyone can advise me on this question:
> - Which kind of index should I use for PRIMARY KEY column, a clustered or
> non-clustered index?
> - Similarly which kind of index should I use for FOREIGN KEY column, a
> clustered or non-clustered index?
> A background of how I become .
> My understanding of clustered index is, basically rows with column value
> that fit an index value are grouped together. In another words, 1 index
> value, multiple rows (1-to-many relationship).
> Furthermore these rows are sorted within the group itself.
> Therefore when SQL Server queries the DB along a column with clustered
> index, it will be able to quickly find and return all the rows.
> As for non-clustered index, it is basically an index that points directly
to
> that row. So, 1 index value, 1 row (1-to-1 relationship "ideally").
> However on one article, it recommends that primary keys should be
clustered
> (which is what SQL Server does by default). But on another article, it
> recommends that pimary keys should be non-clustered! And that is when I
> become .
> Since primary keys are unique, so rows are not grouped under a clustered
> index at all. SQL Server cannot find more than one row under that index.
So
> there is no performance value gain.
> However non-clustered index is more suited to set on the primary key
column.
> Afterall one-index value, one primary key.
> So I think it makes more sense to set non-cluster index for primary key,
> while reserving the clustered index for foreign keys, especially the child
> table's foreign key (back to the parent row). The power of the clustered
> index should not be wasted on primary key column.
> Is my understanding correct?
> Could u please kindly advise. TQ very much in advance.

Diff betw Clustered and Non-Clustered Index and their Applicat

Hi,
Thanks for the reply. One of the article
(http://www.sql-server-performance.c...red_indexes.asp) was the exact
one that confuses me.
It mentions...
"As a rule of thumb, every table should have a clustered index. Generally,
but not always, the clustered index should be on a column that monotonically
increases--such as an identity column, or some other column where the value
is increasing--and is unique. In many cases, the primary key is the ideal
column for a clustered index."
Which in my understanding basically says... Primary key should use clustered
index.
But in another paragraph...
"The primary key you select for your table should not always be a clustered
index. If you create the primary key and don't specify otherwise, this is th
e
default. Only make the primary key a clustered index if you will be regularl
y
performing range queries on the primary key or need your results sorted by
the primary key. [6.5, 7.0, 2000] Updated 3-5-2004"
They sound contradictory to me.
And on (http://www.sql-server-performance.c...red_indexes.asp), it
states
"If a column in a table is not at least 95% unique, then most likely the
query optimizer will not use a non-clustered index based on that column.
Because of this, don't add non-clustered indexes to columns that aren't at
least 95% unique. For example, a column with "yes" or "no" as the data won't
be at least 95% unique. [6.5, 7.0, 2000] Updated 3-4-2004"
Which sounds more logical to me.
1) Do you have general rule of thumb on this matter:
- clustered or non-clustered index for primary key?
- clustered or non-clustered index for foreign key?
2) Also does my understanding of the functions of clustered and
non-clustered index correct?
Could u please kindly advise. TQ again.
"Uri Dimant" wrote:

> Hi
> This is very,very big and important issue.
> Start with
> http://www.sql-server-performance.c...red_indexes.asp
> http://www.sql-server-performance.c...red_indexes.asp
> http://www.sql-server-performance.c...ing_indexes.asp
> "HardKhor" <HardKhor@.discussions.microsoft.com> wrote in message
> news:BABFB6D4-09F7-482B-89F9-DF10DFCF1242@.microsoft.com...
> the
> to
> clustered
> So
> column.
>
>1) Do you have general rule of thumb on this matter:
- clustered or non-clustered index for primary key?
- clustered or non-clustered index for foreign key?
It depends on your tables, data, and business requirements. In my opinion CI
is more efficient on a range queries as BETWEEN and greater,lower that
returns a set of data.NCI is more effcient to retrieve a few rows or single
row from the table.
You have to choose what column to be defined as primary key and on what
column create CI.
Yes, it is considered a good practice to create a NCI on foreign key to
improve JOIN performance.
2) Also does my understanding of the functions of clustered and
non-clustered index correct?

> Which in my understanding basically says... Primary key should use
clustered
> index.
Up to you.
"HardKhor" <HardKhor@.discussions.microsoft.com> wrote in message
news:05A5C898-E9AD-4B0F-9F43-E0E0BA75A9EA@.microsoft.com...
> Hi,
> Thanks for the reply. One of the article
> (http://www.sql-server-performance.c...red_indexes.asp) was the
exact
> one that confuses me.
> It mentions...
> "As a rule of thumb, every table should have a clustered index. Generally,
> but not always, the clustered index should be on a column that
monotonically
> increases--such as an identity column, or some other column where the
value
> is increasing--and is unique. In many cases, the primary key is the ideal
> column for a clustered index."
> Which in my understanding basically says... Primary key should use
clustered
> index.
> But in another paragraph...
> "The primary key you select for your table should not always be a
clustered
> index. If you create the primary key and don't specify otherwise, this is
the
> default. Only make the primary key a clustered index if you will be
regularly
> performing range queries on the primary key or need your results sorted by
> the primary key. [6.5, 7.0, 2000] Updated 3-5-2004"
> They sound contradictory to me.
> And on (http://www.sql-server-performance.c...red_indexes.asp),
it
> states
> "If a column in a table is not at least 95% unique, then most likely the
> query optimizer will not use a non-clustered index based on that column.
> Because of this, don't add non-clustered indexes to columns that aren't at
> least 95% unique. For example, a column with "yes" or "no" as the data
won't
> be at least 95% unique. [6.5, 7.0, 2000] Updated 3-4-2004"
> Which sounds more logical to me.
> 1) Do you have general rule of thumb on this matter:
> - clustered or non-clustered index for primary key?
> - clustered or non-clustered index for foreign key?
> 2) Also does my understanding of the functions of clustered and
> non-clustered index correct?
> Could u please kindly advise. TQ again.
> "Uri Dimant" wrote:
>
a
However
or
value
index
directly
I
clustered
index.
key,
child
clustered|||Watch out for rule-of-thumbs!
You have to weigh in both retrieval and inserting of data. The part about cr
eating cl ix on a column
which is monotonically increasing is all about inserting data. This is so th
at all data goes to the
end of the table and you don't get page split in your data (remember that cl
ix = data). But it
doesn't address how you search data. Fine, cluster on your identify PK, but
perhaps it is more
important to support range queries on a datetime or lastname column? Also, r
emember that page splits
can be handled using index defragmentation, and perhaps the table isn't that
insert intensitive in
the first place?
I don't know where they get the 95% unique number. It is all about selectivi
ty. Understand how SQL
Server can use a clustered index and also non-clustered indexes (navigate th
e index then bookmark
lookups). Also remember that the optimizer is cost based.
Based on above, you should be able to make informed decisions of what to clu
ster on.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"HardKhor" <HardKhor@.discussions.microsoft.com> wrote in message
news:05A5C898-E9AD-4B0F-9F43-E0E0BA75A9EA@.microsoft.com...
> Hi,
> Thanks for the reply. One of the article
> (http://www.sql-server-performance.c...red_indexes.asp) was the exac
t
> one that confuses me.
> It mentions...
> "As a rule of thumb, every table should have a clustered index. Generally,
> but not always, the clustered index should be on a column that monotonical
ly
> increases--such as an identity column, or some other column where the valu
e
> is increasing--and is unique. In many cases, the primary key is the ideal
> column for a clustered index."
> Which in my understanding basically says... Primary key should use cluster
ed
> index.
> But in another paragraph...
> "The primary key you select for your table should not always be a clustere
d
> index. If you create the primary key and don't specify otherwise, this is
the
> default. Only make the primary key a clustered index if you will be regula
rly
> performing range queries on the primary key or need your results sorted by
> the primary key. [6.5, 7.0, 2000] Updated 3-5-2004"
> They sound contradictory to me.
> And on (http://www.sql-server-performance.c...red_indexes.asp), i
t
> states
> "If a column in a table is not at least 95% unique, then most likely the
> query optimizer will not use a non-clustered index based on that column.
> Because of this, don't add non-clustered indexes to columns that aren't at
> least 95% unique. For example, a column with "yes" or "no" as the data won
't
> be at least 95% unique. [6.5, 7.0, 2000] Updated 3-4-2004"
> Which sounds more logical to me.
> 1) Do you have general rule of thumb on this matter:
> - clustered or non-clustered index for primary key?
> - clustered or non-clustered index for foreign key?
> 2) Also does my understanding of the functions of clustered and
> non-clustered index correct?
> Could u please kindly advise. TQ again.
> "Uri Dimant" wrote:
>|||On Wed, 20 Jul 2005 11:57:06 +0300, "Uri Dimant" <urid@.iscar.co.il> wrote:
in <uqvI#jQjFHA.4000@.TK2MSFTNGP12.phx.gbl>

>Yes, it is considered a good practice to create a NCI on foreign key to
>improve JOIN performance.
Is it better, worse, or no difference if the foreign key is also a CI on its
own
PK?
Consider the following:
CREATE TABLE ShowTime.dbo.Countries (
CountryID TINYINT NOT NULL CONSTRAINT PK_Countries PRIMARY KEY,
CountryCode CHAR(3) NOT NULL,
Country VARCHAR(45) NOT NULL)
CREATE TABLE ShowTime.dbo.States (
StateID TINYINT NOT NULL CONSTRAINT PK_States PRIMARY KEY,
CountryID TINYINT NOT NULL CONSTRAINT FK_States_Country FOREIGN KEY
(CountryID) REFERENCES ShowTime.dbo.Countries(CountryID),
State CHAR(2) NOT NULL)
I'm interpreting your remark to mean in the absence of an index on the forei
gn
key it would be considered to be good practice to create a NCI?
Stefan Berglund|||Tibor Karaszi wrote:
> Watch out for rule-of-thumbs!
> You have to weigh in both retrieval and inserting of data. The part
> about creating cl ix on a column which is monotonically increasing is
> all about inserting data. This is so that all data goes to the end of
> the table and you don't get page split in your data (remember that cl
> ix = data). But it doesn't address how you search data. Fine, cluster
> on your identify PK, but perhaps it is more important to support
> range queries on a datetime or lastname column? Also, remember that
> page splits can be handled using index defragmentation, and perhaps
> the table isn't that insert intensitive in the first place?
> I don't know where they get the 95% unique number. It is all about
> selectivity. Understand how SQL Server can use a clustered index and
> also non-clustered indexes (navigate the index then bookmark
> lookups). Also remember that the optimizer is cost based.
> Based on above, you should be able to make informed decisions of what
> to cluster on.
I would add that clustered index keys are stored internally by SQL
Server as unique values and the clustered index key values are the
pointers in all non-clustered indexes. This has a couple implications:
1- Your clustered index key should be small if your table contains
non-clustered indexes. If you cluster on a large key (say 100 bytes),
then you've added 100 bytes to each key value in every non-clustered
index.
2- If you cluster on a non-unique column, SQL Server will make it
unique internally. While this is transparent to database users, it
further increases the size of the index and all non-clustered indexes.
David Gugick
Quest Software
www.imceda.com
www.quest.com|||"Tibor Karaszi" wrote:

> Watch out for rule-of-thumbs!
>
1) TQ for ur reply.
Yes I understand that is no one-size-fits-all approach to this. Which is why
I am trying to understand from a conceptual point of view, difference betwee
n
clustered and non-clustered index and their application, before I can make
informed decisions on how they fit into my application.
However I don't believe anyone has given a clear answer on whether my
understanding on them is correct. I.e. essentially, a clustered index is a
sorted group of rows under a common index value (1 index value = M rows).
Whereas a non-clustered index is, basically 1 index value = 1 row. Could u
pls kindly advise, essentially, is my understanding correct?
2) My database has plenty of parent-and-child tables, e.g. Invoice and
Invoice Item. So the child table [InvoiceItem] has a foreign key
[InvoiceCode] referring back to the primary key [Code] of [Invoice] table
(1-M relationship).
So when query the database to populate my biz reports, a very common
requirement is to join the parent and child tables along their keys.
Also in a typical requirement, I will need join the results from the
parent-child join, with other tables, such as [Item] which contains details
on the [InvoiceItem], E.g. [Item].[Code] = [InvoiceItem].[ItemCode].
Therefore there is a lot of 1-M joins.
So based on my understanding of clustered and non-clustered indexes and how
SQL Server uses them to search for data, at least in the case of parent-chil
d
relationship, it makes more sense to set a clustered index at child's foreig
n
key column, i.e. [InvoiceItem].[InvoiceCode], and non-clustered index at the
parent's primary key column, i.e. [Invoice].[Code]. Do u agree?

> You have to weigh in both retrieval and inserting of data. The part about
creating cl ix on a column
> which is monotonically increasing is all about inserting data. This is so
that all data goes to the
> end of the table and you don't get page split in your data (remember that
cl ix = data). But it
> doesn't address how you search data. Fine, cluster on your identify PK, bu
t perhaps it is more
> important to support range queries on a datetime or lastname column?
3) Based on ur explaination, the majority of range queries that I do is
along the date fields of various transaction tables, e.g. finding the
invoices for this mth, last mth, last year, 10 years ago, etc. So it is also
POSSIBLE to consider [TransactionDate] as a candidate for cluster index? Do u
agree?

> I don't know where they get the 95% unique number. It is all about selecti
vity. Understand how SQL
> Server can use a clustered index and also non-clustered indexes (navigate
the index then bookmark
> lookups). Also remember that the optimizer is cost based.
3) My understanding of selectivity means how fast data can be uniquely
identified. So it implies a preference towards a field with 95% unqiue
values. Hence the 95% unique number thingy. Is that true?

> Based on above, you should be able to make informed decisions of what to c
luster on.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "HardKhor" <HardKhor@.discussions.microsoft.com> wrote in message
> news:05A5C898-E9AD-4B0F-9F43-E0E0BA75A9EA@.microsoft.com...
>|||Inline...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"HardKhor" <HardKhor@.discussions.microsoft.com> wrote in message
news:D5D39394-6183-46E5-B6FA-40B5957CB882@.microsoft.com...
> "Tibor Karaszi" wrote:
>
> 1) TQ for ur reply.
You're welcome :-)

> Yes I understand that is no one-size-fits-all approach to this. Which is w
hy
> I am trying to understand from a conceptual point of view, difference betw
een
> clustered and non-clustered index and their application, before I can make
> informed decisions on how they fit into my application.
> However I don't believe anyone has given a clear answer on whether my
> understanding on them is correct. I.e. essentially, a clustered index is a
> sorted group of rows under a common index value (1 index value = M rows).
> Whereas a non-clustered index is, basically 1 index value = 1 row. Could u
> pls kindly advise, essentially, is my understanding correct?
SQL Server does not store the value once and the number of occurances as a n
umber, if that is what
you mean. Bot cl and nc indexes are b-trees (there are good drawings on this
in Books Online).
For a cl ix, the leaf level *is the data*. I.e., it is sorted (the linked li
st) according to the
index key and the pages contains all the columns.
An nc ix leaf level only contains the index key and a "row locator"/bookmark
. This is used to
navigate to the corresponding page and row number on that pag.

>
> 2) My database has plenty of parent-and-child tables, e.g. Invoice and
> Invoice Item. So the child table [InvoiceItem] has a foreign key
> [InvoiceCode] referring back to the primary key [Code] of [Invoice] table
> (1-M relationship).
> So when query the database to populate my biz reports, a very common
> requirement is to join the parent and child tables along their keys.
> Also in a typical requirement, I will need join the results from the
> parent-child join, with other tables, such as [Item] which contains details
> on the [InvoiceItem], E.g. [Item].[Code] = [InvoiceItem].[ItemCode].
> Therefore there is a lot of 1-M joins.
> So based on my understanding of clustered and non-clustered indexes and ho
w
> SQL Server uses them to search for data, at least in the case of parent-ch
ild
> relationship, it makes more sense to set a clustered index at child's fore
ign
> key column, i.e. [InvoiceItem].[InvoiceCode], and non-clustered index at the
> parent's primary key column, i.e. [Invoice].[Code]. Do u agree?
Yes, cl on fk can often ba a good candidate. You still have to weigh in othe
r candidates as well as
consider fragmentation aspects.

>
> 3) Based on ur explaination, the majority of range queries that I do is
> along the date fields of various transaction tables, e.g. finding the
> invoices for this mth, last mth, last year, 10 years ago, etc. So it is al
so
> POSSIBLE to consider [TransactionDate] as a candidate for cluster index? Do u
> agree?
Yes.

>
> 3) My understanding of selectivity means how fast data can be uniquely
> identified. So it implies a preference towards a field with 95% unqiue
> values. Hence the 95% unique number thingy. Is that true?
Selectivity is quite simply for a certain table and a certain restriction (c
ondition in the WHERE
clause), how many percent of the total number of rows. 1000 rows in table, c
ondition returns 100
rows = 10% selectivity.
Density is another term we use. Here we *do not* involve a query or search c
ondition. We only look
at the data. Say you have a column named gender. Say we only have two possib
le values (male and
female). Say that both are represented in the table. We now have a density o
f 1/2 = 0.5. Or say that
we do business with 20 other companies. These 10 companies are all represent
ed in the table. For the
"company column", we have a density of 0.05 (1/20). When we calculatye densi
ty, we do not care about
the number of rows in the table.
>