I have put together a script that was intended to move all existing
non-clustered indexes in a SQL 7 database to the secondary filegroup.
However, I am getting unexpected results in a comparison. Here is a snippet
from what I am doing that contains my print statement (To verify that I'm
getting the values for comparison that I need) and the output that shows it
is not acting as expected. Any help is appreciated:
foreach (@table) {
print outfile "\n\n\n--$_ :\n\n";
$selecth = $dbh->prepare(qq{sp_helpindex "$_"}) || die "can't prepare
sp_helpindex\n$dbh::errstr\n";
$selecth->execute() || die "Can't execute sp_helpindex\n$dbh::errstr\n";
my ($row, $name, $descr, $keynames, $create, $drop);
eval{
$selecth->bind_columns(undef, \($name, $descr, $keynames));
while ($row = $selecth->fetchrow_arrayref) {
my @keyarray = split /, /, $keynames;
$keynames = '['. join('], [', @keyarray).']';
$countone = grep /^nonclustered/i, $descr;
$counttwo = grep /unique/i, $descr;
$countthree = grep /primary key/i, $descr;
$create = qq{CREATE NONCLUSTERED INDEX \[$name\] \n\tON
$_($keynames) ON SECONDARY\n}
if ($countone == 1 and $counttwo == 0 and
$countthree == 0);
$create = qq{CREATE UNIQUE NONCLUSTERED INDEX \[$name\]
\n\tON $_($keynames) ON SECONDARY\n}
if ($countone == 1 and $counttwo == 1 and
$countthree == 0);
$create = qq{ALTER TABLE $_ \n\tadd constraint
$name PRIMARY KEY NONCLUSTERED($keynames) on SECONDARY\n}
if ($countone == 1 and
$countthree == 1);
$drop = qq{DROP INDEX $_.\[$name\]\n} if ($countone == 1
and $countthree == 0);
$drop = qq{alter table $_ drop constraint
$name\n}
if ($countone == 1 and $counttwo ==
1 and $countthree == 1);
print
"$name,\t$countone\t$counttwo\t$countthree\n$drop$create"
if ($countone == 1 and $counttwo == 1 and
$countthree == 1) ;
print outfile $drop.$create;
}
}
}
I see my indent format went bad when I pasted that. However that's the
comparison. I need to handle the nonclustered primary keys different from a
nonclustered unique index. That's why I go through the pains of the
comparisons. Here is an example of two different results that are printed
showing something behaving different from what I expect:
one output:
CMS_FTEXT_COLUM0, 1 1 1
alter table dbo.[CMS_FTEXT_COLUM] drop constraint CMS_FTEXT_COLUM0
ALTER TABLE dbo.[CMS_FTEXT_COLUM]
add constraint CMS_FTEXT_COLUM0 PRIMARY KEY
NONCLUSTERED([COLUMN_ID]) on SECONDARY
A second output:
MKA_FEVENT_MAIL0, 1 1 1
alter table dbo.[MKA_FEVENT_MAIL] drop constraint MKA_FEVENT_MAIL0
ALTER TABLE dbo.[MKA_FEVENT_MAIL]
add constraint MKA_FEVENT_MAIL0 PRIMARY KEY
NONCLUSTERED([FIRM_EVENT_UNO], [MAIL_UNO]) on SECONDARY
another output:
GLT_JRNL_Z0, 1 1 1
alter table dbo.[GLT_JRNL_Z] drop constraint GLT_JRNL_Z0
CREATE UNIQUE NONCLUSTERED INDEX [GLT_JRNL_Z0]
ON dbo.[GLT_JRNL_Z]([STATUS], [JE_NUMBER]) ON SECONDARY
Perousing the outfile (over 800 tables and over 1500 nonclustered indexes, I
haven't been through every one yet) it looks as if it always get the right
statement for $drop, but although about 90% of the $create lines that are
printed are the right one, there are some like the third example which
should use an alter table to add back a primary key constraint that instead
just create a unique nonclustered index. You can see from the output that
all three values are 1, so how can it be doing this? What am I missing? how
can it make the right evaluation most of the time, but not all of the time?
Steve H.
