how to effectively modify index

Question

Hej everyone,

problem : I am looking for right way to convert an index from clustered to non-clustered

Description : I have a table as below in sybase db:

dbo.UserLog
------------------------
Id | UserId |time | ....
------------------------

This is hash partitioned using UserId.

Currently it has 2 indexes

UserId : non-clustered
time: clustered

This table has about 20 million records.

I now want to make UserId as clustered index and time as non-clustered index.

is it correct to user alter index to change from clustered to non-clustered or do i drop index and recreate. does the fact that userId is used in hash partitioning have any implications to this?

To me alter seems way to go but I have not yet tried this.

Answer 1

Just drop both and then recreate them the way you want them. Since you are doing such a major change to each index, I'm sure the DB engine is just dropping and creating them when you run the alter anyway. If you are worried about the the fact that userId is used in hash partitioning then creating each index new would seem the safest to me, but I'm sure Sybase is not going to mess up if you use alter.