Rebuilding clustered index

Working on a vendor database upgrade. They want the clustered index to be
rebuilt. I have huge table with more than 10 million records....have a
clustered index and 10 non-clustered indexes...
what are my options,
As an example lets take Orders table and CIX_Orders is the clustered index.
1. DBCC DBReindex ('Northwind.dbo.Orders', CIX_Orders)
2.create clustered index [CIX_Orders] on [dbo].[Orders_Temp](
1;OrderID])
with drop_existing
How does the above two differ?
Thanks very muchOn SQL 2000 and 2005 these two should be the same.
HTH
Kalen Delaney, SQL Server MVP
"Ranga" <Ranga@.discussions.microsoft.com> wrote in message
news:5DF73102-DD50-4635-B712-C7FDA3609F9F@.microsoft.com...
> Working on a vendor database upgrade. They want the clustered index to be
> rebuilt. I have huge table with more than 10 million records....have a
> clustered index and 10 non-clustered indexes...
> what are my options,
> As an example lets take Orders table and CIX_Orders is the clustered
> index.
> 1. DBCC DBReindex ('Northwind.dbo.Orders', CIX_Orders)
> 2.create clustered index [CIX_Orders] on [dbo].[Orders_Temp](&
#91;OrderID])
> with drop_existing
> How does the above two differ?
> Thanks very much|||Thanks Kalen...
I am using SQL 2000
My questions is will my non-clustered indexes if I rebuild my clustered
index ?
"Kalen Delaney" wrote:

> On SQL 2000 and 2005 these two should be the same.
> --
> HTH
> Kalen Delaney, SQL Server MVP
>
> "Ranga" <Ranga@.discussions.microsoft.com> wrote in message
> news:5DF73102-DD50-4635-B712-C7FDA3609F9F@.microsoft.com...
>
>|||Hi,
Yes it will rebuild all the non-clustered indexes if you rebuild the
clustered.
This is because the nonclustered index contains the keys of clustered
index. So to retake the new set of clustered keys; non clustered index is
rebuild.
Thanks
Hari
SQL Server MVP
"Ranga" <Ranga@.discussions.microsoft.com> wrote in message
news:7E10DBD5-A7CF-47C8-BC8F-6063AE24F4A2@.microsoft.com...[vbcol=seagreen]
> Thanks Kalen...
> I am using SQL 2000
> My questions is will my non-clustered indexes if I rebuild my clustered
> index ?
>
> "Kalen Delaney" wrote:
>|||Not if you use one of these two methods, and you aren't redefining anything
about the indexes. If you are just rebuilding the indexes exactly as they
were for the purpose of removing fragmentation, the nonclustered indexes
will not have to be touched. That is why these two methods are preferred
over a separate drop index and create index.
HTH
Kalen Delaney, SQL Server MVP
"Ranga" <Ranga@.discussions.microsoft.com> wrote in message
news:7E10DBD5-A7CF-47C8-BC8F-6063AE24F4A2@.microsoft.com...[vbcol=seagreen]
> Thanks Kalen...
> I am using SQL 2000
> My questions is will my non-clustered indexes if I rebuild my clustered
> index ?
>
> "Kalen Delaney" wrote:
>|||That is only true if the Clustered index is not unique. If it is unique and
you rebuild the clustered index it does not automatically rebuild the
non-clustered using DBREINDEX. In 2005 it does not matter if the clustered
index is unique or not since they handle the uniquifier in a much better
manor that does not change the uniquifier values when reindexed.
Andrew J. Kelly SQL MVP
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:%23iVBCS%23xGHA.4840@.TK2MSFTNGP04.phx.gbl...
> Hi,
> Yes it will rebuild all the non-clustered indexes if you rebuild the
> clustered.
> This is because the nonclustered index contains the keys of clustered
> index. So to retake the new set of clustered keys; non clustered index is
> rebuild.
> Thanks
> Hari
> SQL Server MVP
> "Ranga" <Ranga@.discussions.microsoft.com> wrote in message
> news:7E10DBD5-A7CF-47C8-BC8F-6063AE24F4A2@.microsoft.com...
>|||Hi Ranga
It is usually more important for the non-clustered indexes to be rebuilt
than clustered indexes. The non-clustered indexes purely provide support for
query performance whilst clustered indexes are really the table storage
structure, so non-clustered indexes always play a performance role whilst
clustered indexes only provide performance support sometimes. Given the
specialised role of non-clustered indexes, it's critical that they be
re-built if you're doing this for performance reasons. We often rebuild our
non-clustered indexes many times between clustered index rebuilds..
Regards,
Greg Linwood
SQL Server MVP
"Ranga" <Ranga@.discussions.microsoft.com> wrote in message
news:7E10DBD5-A7CF-47C8-BC8F-6063AE24F4A2@.microsoft.com...[vbcol=seagreen]
> Thanks Kalen...
> I am using SQL 2000
> My questions is will my non-clustered indexes if I rebuild my clustered
> index ?
>
> "Kalen Delaney" wrote:
>|||Hi Greg
Can you elaborate on this?
While I agree that nonclustered index have more of a performance support
role in more cases, I don't see what that has to do with rebuilding them.
How exactly are you finding that rebuilding helps with the performance
support of nonclustered indexes?
In particular, if the statistics are up to date, and you're using the nc
index to find just a few rows, why is rebuilding a necessary thing?
Since clustered indexes are the table storage, any scan or partial scan of
the data is impacted by the fragmentation of the clustered index, making it
imperative that the clustered index be rebuilt.
HTH
Kalen Delaney, SQL Server MVP
"Greg Linwood" <g_linwood@.hotmail.com> wrote in message
news:u%239yjE$xGHA.1936@.TK2MSFTNGP06.phx.gbl...
> Hi Ranga
> It is usually more important for the non-clustered indexes to be rebuilt
> than clustered indexes. The non-clustered indexes purely provide support
> for query performance whilst clustered indexes are really the table
> storage structure, so non-clustered indexes always play a performance role
> whilst clustered indexes only provide performance support sometimes. Given
> the specialised role of non-clustered indexes, it's critical that they be
> re-built if you're doing this for performance reasons. We often rebuild
> our non-clustered indexes many times between clustered index rebuilds..
> Regards,
> Greg Linwood
> SQL Server MVP
> "Ranga" <Ranga@.discussions.microsoft.com> wrote in message
> news:7E10DBD5-A7CF-47C8-BC8F-6063AE24F4A2@.microsoft.com...
>|||Hi Kalen
Re> "and you're using the nc index to find just a few rows". This isn't a
good assumption b/c in most OLTPs, ncix's are range-scanned as much as
they're seek'd. Any range or full scan should ideally occur within a ncix
where page density is higher & read io is therefore more efficient than it
can ever be in a cix.. Any range or full scan that occurs within a cix will
always be less efficient other than in the obscure scenario where all
columns in a table are actually required by the query.
Re> Since clustered indexes are the table storage, any scan or partial scan
of the data is impacted by the fragmentation.
Not if the ncix covers the query. In this case, the fragmentation of the
ncix is all that matters & fragmentation in the cix is immaterial. Ideally,
all performance critical queries should be covered by ncixs, so this is
fairly important.
Re>How exactly are you finding that rebuilding helps with the performance
support of nonclustered indexes?
We have empirically measured proof and documented user feedback that
rebuilding ncixs alone usually improves the overall performance of otherwise
well configured oltp system. Happy to show you the data next time you're out
here too (c:
Regards,
Greg Linwood
SQL Server MVP
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:%23B3nhY$xGHA.4524@.TK2MSFTNGP04.phx.gbl...
> Hi Greg
> Can you elaborate on this?
> While I agree that nonclustered index have more of a performance support
> role in more cases, I don't see what that has to do with rebuilding them.
> How exactly are you finding that rebuilding helps with the performance
> support of nonclustered indexes?
> In particular, if the statistics are up to date, and you're using the nc
> index to find just a few rows, why is rebuilding a necessary thing?
> Since clustered indexes are the table storage, any scan or partial scan of
> the data is impacted by the fragmentation of the clustered index, making
> it imperative that the clustered index be rebuilt.
> --
> HTH
> Kalen Delaney, SQL Server MVP
>
> "Greg Linwood" <g_linwood@.hotmail.com> wrote in message
> news:u%239yjE$xGHA.1936@.TK2MSFTNGP06.phx.gbl...
>|||Thanks everyone for their feedback..this is a very interesting topic...I hav
e
another Q..If the clustered index is part of PK constraint, what will the
exact syntax to rebuild it using the both methods (DBReindex and Create with
Drop_Existing)
Thanks,
Ranga
"Greg Linwood" wrote:

> Hi Kalen
> Re> "and you're using the nc index to find just a few rows". This isn't a
> good assumption b/c in most OLTPs, ncix's are range-scanned as much as
> they're seek'd. Any range or full scan should ideally occur within a ncix
> where page density is higher & read io is therefore more efficient than it
> can ever be in a cix.. Any range or full scan that occurs within a cix wil
l
> always be less efficient other than in the obscure scenario where all
> columns in a table are actually required by the query.
> Re> Since clustered indexes are the table storage, any scan or partial sca
n
> of the data is impacted by the fragmentation.
> Not if the ncix covers the query. In this case, the fragmentation of the
> ncix is all that matters & fragmentation in the cix is immaterial. Ideally
,
> all performance critical queries should be covered by ncixs, so this is
> fairly important.
> Re>How exactly are you finding that rebuilding helps with the performance
> support of nonclustered indexes?
> We have empirically measured proof and documented user feedback that
> rebuilding ncixs alone usually improves the overall performance of otherwi
se
> well configured oltp system. Happy to show you the data next time you're o
ut
> here too (c:
> Regards,
> Greg Linwood
> SQL Server MVP
> "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
> news:%23B3nhY$xGHA.4524@.TK2MSFTNGP04.phx.gbl...
>
>