Can I: remove clustered constraint, but keep clustering?

All,
In a database accessed 24/7, I have a table a few GB in size that is
currently clustered on a UNIQUE constraint. The data rules have changed,
and the key combination is no longer going to be unique. However, it is
still desirable to cluster on that set of columns.
On a test database, when I do
ALTER TABLE foo
DROP CONSTRAINT bar
CREATE CLUSTERED INDEX bar
ON foo (zip, zap)
it takes lots of time (over an hour). I believe this is because the DROP
CONSTRAINT removes clustering, so SQL Server re-organizes the table as a
heap, then the CREATE INDEX restores clustering, so SQL Server re-organizes
the table according to the clustered index. This is less efficient than I
would prefer, and I do not want to take the database off-line for an hour.
Is there some SQL Server trick I do not know about? Can I leave the
constraint, but somehow disable checking? Can I safely edit the system
tables? I suspect the latter is possible, but I would not want to proceed
without expert advice.
TIA,
Scott NicholAll,
I have found info about DROP_EXISTING at, e.g.,
http://www.winnetmag.com/SQLServer/Article/ArticleID/40405/40405.html, but
this does not apply since I want to go from a constraint to an index, right?
--
Scott Nichol
"Scott Nichol" <reply_to_newsgroup@.scottnichol.com> wrote in message
news:uF4D7C8TEHA.204@.TK2MSFTNGP10.phx.gbl...
> All,
> In a database accessed 24/7, I have a table a few GB in size that is
> currently clustered on a UNIQUE constraint. The data rules have changed,
> and the key combination is no longer going to be unique. However, it is
> still desirable to cluster on that set of columns.
> On a test database, when I do
> ALTER TABLE foo
> DROP CONSTRAINT bar
> CREATE CLUSTERED INDEX bar
> ON foo (zip, zap)
> it takes lots of time (over an hour). I believe this is because the DROP
> CONSTRAINT removes clustering, so SQL Server re-organizes the table as a
> heap, then the CREATE INDEX restores clustering, so SQL Server
re-organizes
> the table according to the clustered index. This is less efficient than I
> would prefer, and I do not want to take the database off-line for an hour.
> Is there some SQL Server trick I do not know about? Can I leave the
> constraint, but somehow disable checking? Can I safely edit the system
> tables? I suspect the latter is possible, but I would not want to proceed
> without expert advice.
> TIA,
> Scott Nichol
>|||The answer is no you cannot disabled the constraint nor can you modify the system table safely to handle the change
As for what is happening, when the clustered index is dropped all non-clustered indexes are changed to having row poitners to the data instead of using the clustered index as the access path
All of them are rebuilt as soon as the clustered index is dropped
When you then recreate the clustered index the whole non-clustered thing happens again switch back from the row pointers to the data identifiers to use accessing the clustered index
I suggest script the drop and add of all your non-clustered indexes, drop them first, then drop and add your clustered, then readd your non-clustered. May still take a while thou so you will have to do an outage time frame.