Let's say that you have a 10gb table, 23m rows, and somehow it was created
and populated with ten indexes, including one that is clustered and unique,
but none of them seems to be marked as the Primary Key. Is there any magic
trick we can do to get it instantly marked as Primary Key?
This is SQLServer 2005.
What we ... er, that is, my hypothetical friend ... does not want to do, is
to drop the clustered index, rewriting the 10gb table, then define the PK,
rewriting the 10gb table again. I believe I read that SQLServer 2005 has a
new trick that might let it move the clustered index in *one* copy instead of
two, but how about *no* copies instead of one'
It occurs to me as I write this, that dropping all the secondary indexes
might be a good first step.
Thanks for good advice,
Josh
ps - the clustered unique index that is not the PK, is already conveniently
*named* XPKblahblahblah.
pps - will it make any difference to anything that uses that database, that
we do have the unique clustered key on the right fields, but it is not the
Primary Key?Beleive it or not, if you fully qualify the foreign key constraint, you will
be able to create it against the unique index.
Wierd, but true.
"JRStern" <JRStern@.discussions.microsoft.com> wrote in message
news:9D5E95E4-685A-4D16-8F85-21B4DA4355B6@.microsoft.com...
> Let's say that you have a 10gb table, 23m rows, and somehow it was created
> and populated with ten indexes, including one that is clustered and
> unique,
> but none of them seems to be marked as the Primary Key. Is there any
> magic
> trick we can do to get it instantly marked as Primary Key?
> This is SQLServer 2005.
> What we ... er, that is, my hypothetical friend ... does not want to do,
> is
> to drop the clustered index, rewriting the 10gb table, then define the PK,
> rewriting the 10gb table again. I believe I read that SQLServer 2005 has
> a
> new trick that might let it move the clustered index in *one* copy instead
> of
> two, but how about *no* copies instead of one'
> It occurs to me as I write this, that dropping all the secondary indexes
> might be a good first step.
> Thanks for good advice,
> Josh
> ps - the clustered unique index that is not the PK, is already
> conveniently
> *named* XPKblahblahblah.
> pps - will it make any difference to anything that uses that database,
> that
> we do have the unique clustered key on the right fields, but it is not the
> Primary Key?|||On Fri, 9 Nov 2007 16:45:05 -0800, "Jay" <nospam@.nospam.org> wrote:
>Beleive it or not, if you fully qualify the foreign key constraint, you will
>be able to create it against the unique index.
>Wierd, but true.
Right, was doing that on SQL7 when replication demanded PK be a GUID.
I'd still like to fiddle the bits so the little gold keys show up on
the index.
(kind of academic now, actually, the guy involved spent the two hours
rewriting the 10gb table, bless the new fast hardware!)
(and actually, with the new SQL2005 features, I guess the trick is you
DON'T first delete the secondary indexes)
J.
>"JRStern" <JRStern@.discussions.microsoft.com> wrote in message
>news:9D5E95E4-685A-4D16-8F85-21B4DA4355B6@.microsoft.com...
>> Let's say that you have a 10gb table, 23m rows, and somehow it was created
>> and populated with ten indexes, including one that is clustered and
>> unique,
>> but none of them seems to be marked as the Primary Key. Is there any
>> magic
>> trick we can do to get it instantly marked as Primary Key?
>> This is SQLServer 2005.
>> What we ... er, that is, my hypothetical friend ... does not want to do,
>> is
>> to drop the clustered index, rewriting the 10gb table, then define the PK,
>> rewriting the 10gb table again. I believe I read that SQLServer 2005 has
>> a
>> new trick that might let it move the clustered index in *one* copy instead
>> of
>> two, but how about *no* copies instead of one'
>> It occurs to me as I write this, that dropping all the secondary indexes
>> might be a good first step.
>> Thanks for good advice,
>> Josh
>> ps - the clustered unique index that is not the PK, is already
>> conveniently
>> *named* XPKblahblahblah.
>> pps - will it make any difference to anything that uses that database,
>> that
>> we do have the unique clustered key on the right fields, but it is not the
>> Primary Key?
>
