Convert Clustered Index to Non-Clustered

How do I convert an SQL Server 2000 clustered index to non-clustered?
Enterprise Manager would not let me.You drop the clustered index and create a non-clustered index with the same
keys. Note however that the clustered index IS the table and that if you
drop the clustered index you will be left with a heap (unsorted collection
of pages) for the table and that the non-clustered index will just contain
the keys and pointers to the actual data pages.
HTH
Jerry
"jmelkerson" <jmelkerson@.discussions.microsoft.com> wrote in message
news:9D468AD8-7F08-426A-8745-F93CF9756F60@.microsoft.com...
> How do I convert an SQL Server 2000 clustered index to non-clustered?
> Enterprise Manager would not let me.|||Here is why asked the question to begin with:
I have a table with almost 2 million records. The primary is is also the
clustered index. Records are added all day, every day. There is no order to
the way in which new records "arrive". New records/primary keys are not
presented in ascending order therefore a new record could be required to be
inserted anywhere within the table. Sometimes when the system slows down I
can't find any assignable cause; no locks, blocks, etc. I am thinking that
maybe SQL Server is taking a long time keeping the clustered index in order.
Maybe if I make the index non-clustered things will speed up. Does this make
sense?
"Jerry Spivey" wrote:
> You drop the clustered index and create a non-clustered index with the same
> keys. Note however that the clustered index IS the table and that if you
> drop the clustered index you will be left with a heap (unsorted collection
> of pages) for the table and that the non-clustered index will just contain
> the keys and pointers to the actual data pages.
> HTH
> Jerry
> "jmelkerson" <jmelkerson@.discussions.microsoft.com> wrote in message
> news:9D468AD8-7F08-426A-8745-F93CF9756F60@.microsoft.com...
> > How do I convert an SQL Server 2000 clustered index to non-clustered?
> > Enterprise Manager would not let me.
>
>|||Is there an auto incrementing key on the table i.e., an IDENITY property on
a column? If so that may be a good possible candidate for the clustered
index as the data page for the next insert is likely to already be in the
cache. If not you could add one. You could also try dropping the clustered
index trying it without the NC index, then add the NC index to see which is
faster. Additional NC indexes generally degrade write performance.
HTH
Jerry
"jmelkerson" <jmelkerson@.discussions.microsoft.com> wrote in message
news:40BDA1A0-1C45-4DDA-A007-23E4AB6228EC@.microsoft.com...
> Here is why asked the question to begin with:
> I have a table with almost 2 million records. The primary is is also the
> clustered index. Records are added all day, every day. There is no order
> to
> the way in which new records "arrive". New records/primary keys are not
> presented in ascending order therefore a new record could be required to
> be
> inserted anywhere within the table. Sometimes when the system slows down
> I
> can't find any assignable cause; no locks, blocks, etc. I am thinking
> that
> maybe SQL Server is taking a long time keeping the clustered index in
> order.
> Maybe if I make the index non-clustered things will speed up. Does this
> make
> sense?
> "Jerry Spivey" wrote:
>> You drop the clustered index and create a non-clustered index with the
>> same
>> keys. Note however that the clustered index IS the table and that if you
>> drop the clustered index you will be left with a heap (unsorted
>> collection
>> of pages) for the table and that the non-clustered index will just
>> contain
>> the keys and pointers to the actual data pages.
>> HTH
>> Jerry
>> "jmelkerson" <jmelkerson@.discussions.microsoft.com> wrote in message
>> news:9D468AD8-7F08-426A-8745-F93CF9756F60@.microsoft.com...
>> > How do I convert an SQL Server 2000 clustered index to non-clustered?
>> > Enterprise Manager would not let me.
>>|||Yes, there is an Identity column named [Index]. Is an Identity column by
default indexed? The column that is the primary key needs to remain so.
Are you suggesting that I make the primary key a non-clustered index and make
the [Index] column a clustered index?
"Jerry Spivey" wrote:
> Is there an auto incrementing key on the table i.e., an IDENITY property on
> a column? If so that may be a good possible candidate for the clustered
> index as the data page for the next insert is likely to already be in the
> cache. If not you could add one. You could also try dropping the clustered
> index trying it without the NC index, then add the NC index to see which is
> faster. Additional NC indexes generally degrade write performance.
> HTH
> Jerry
> "jmelkerson" <jmelkerson@.discussions.microsoft.com> wrote in message
> news:40BDA1A0-1C45-4DDA-A007-23E4AB6228EC@.microsoft.com...
> > Here is why asked the question to begin with:
> > I have a table with almost 2 million records. The primary is is also the
> > clustered index. Records are added all day, every day. There is no order
> > to
> > the way in which new records "arrive". New records/primary keys are not
> > presented in ascending order therefore a new record could be required to
> > be
> > inserted anywhere within the table. Sometimes when the system slows down
> > I
> > can't find any assignable cause; no locks, blocks, etc. I am thinking
> > that
> > maybe SQL Server is taking a long time keeping the clustered index in
> > order.
> > Maybe if I make the index non-clustered things will speed up. Does this
> > make
> > sense?
> >
> > "Jerry Spivey" wrote:
> >
> >> You drop the clustered index and create a non-clustered index with the
> >> same
> >> keys. Note however that the clustered index IS the table and that if you
> >> drop the clustered index you will be left with a heap (unsorted
> >> collection
> >> of pages) for the table and that the non-clustered index will just
> >> contain
> >> the keys and pointers to the actual data pages.
> >>
> >> HTH
> >>
> >> Jerry
> >> "jmelkerson" <jmelkerson@.discussions.microsoft.com> wrote in message
> >> news:9D468AD8-7F08-426A-8745-F93CF9756F60@.microsoft.com...
> >> > How do I convert an SQL Server 2000 clustered index to non-clustered?
> >> > Enterprise Manager would not let me.
> >>
> >>
> >>
>
>|||Hmmm...not sure I would have named the column 'index' as that is a SQL
Server reserved word. No, a column with an IDENTITY property is not indexed
by default. It is an option as the PRIMARY KEY column is not "required" to
be a clustered index and a clustered index may be better suited to the
IDENTITY column in this case. I would try a few indexing strategies and
choose the best one in performance from your tests.
HTH
Jerry
"jmelkerson" <jmelkerson@.discussions.microsoft.com> wrote in message
news:A6EBD861-9E73-4896-8AFB-2AE09CC177FB@.microsoft.com...
> Yes, there is an Identity column named [Index]. Is an Identity column by
> default indexed? The column that is the primary key needs to remain so.
> Are you suggesting that I make the primary key a non-clustered index and
> make
> the [Index] column a clustered index?
> "Jerry Spivey" wrote:
>> Is there an auto incrementing key on the table i.e., an IDENITY property
>> on
>> a column? If so that may be a good possible candidate for the clustered
>> index as the data page for the next insert is likely to already be in the
>> cache. If not you could add one. You could also try dropping the
>> clustered
>> index trying it without the NC index, then add the NC index to see which
>> is
>> faster. Additional NC indexes generally degrade write performance.
>> HTH
>> Jerry
>> "jmelkerson" <jmelkerson@.discussions.microsoft.com> wrote in message
>> news:40BDA1A0-1C45-4DDA-A007-23E4AB6228EC@.microsoft.com...
>> > Here is why asked the question to begin with:
>> > I have a table with almost 2 million records. The primary is is also
>> > the
>> > clustered index. Records are added all day, every day. There is no
>> > order
>> > to
>> > the way in which new records "arrive". New records/primary keys are
>> > not
>> > presented in ascending order therefore a new record could be required
>> > to
>> > be
>> > inserted anywhere within the table. Sometimes when the system slows
>> > down
>> > I
>> > can't find any assignable cause; no locks, blocks, etc. I am thinking
>> > that
>> > maybe SQL Server is taking a long time keeping the clustered index in
>> > order.
>> > Maybe if I make the index non-clustered things will speed up. Does
>> > this
>> > make
>> > sense?
>> >
>> > "Jerry Spivey" wrote:
>> >
>> >> You drop the clustered index and create a non-clustered index with the
>> >> same
>> >> keys. Note however that the clustered index IS the table and that if
>> >> you
>> >> drop the clustered index you will be left with a heap (unsorted
>> >> collection
>> >> of pages) for the table and that the non-clustered index will just
>> >> contain
>> >> the keys and pointers to the actual data pages.
>> >>
>> >> HTH
>> >>
>> >> Jerry
>> >> "jmelkerson" <jmelkerson@.discussions.microsoft.com> wrote in message
>> >> news:9D468AD8-7F08-426A-8745-F93CF9756F60@.microsoft.com...
>> >> > How do I convert an SQL Server 2000 clustered index to
>> >> > non-clustered?
>> >> > Enterprise Manager would not let me.
>> >>
>> >>
>> >>
>>