We're seeing funny things with SQL Server 2000 when dealing with indexes on
a customer table. When we issue:
DBCC reindex (cust)
we see an error Message 169: a column has been specified more than once in
the order by list.
We see the same error during Update Statistics.
We're using the Query analyzer - index tuning wizard to create indexes on
this table. The table has 12 indexes, and the field Cust_num is used in 7 of
12 indexes.
We tried to remove indexes one by one, we get same Message 169.
Any clues? Thanks!!> The table has 12 indexes,
WOW! That isn't the most I've ever seen, but it's certainly more than I'm
used to. Why is one column covered in seven different indices?
> We tried to remove indexes one by one, we get same Message 169.
Can you show the result of:
EXEC sp_help tablename
EXEC sp_helpindex tablename|||Here is the output from sp_helpindex
I think the hypotheticals are added by the Index wizard ... how do you get
rid of these?
--
Address nonclustered located on PRIMARY Address
CustID nonclustered unique located on PRIMARY CustID
CustNum nonclustered unique located on PRIMARY CustNum
hind_1682821057_24A_2A nonclustered hypothetical auto create located on
PRIMARY PriceCode CustNum
hind_1682821057_2A_24A_50A nonclustered hypothetical auto create located
on PRIMARY CustNum PriceCode AssignedLocation
hind_1682821057_2A_50A nonclustered hypothetical auto create located on
PRIMARY CustNum AssignedLocation
hind_1682821057_50A_24A_2A nonclustered hypothetical auto create located
on PRIMARY AssignedLocation PriceCode CustNum
hind_1682821057_50A_2A nonclustered hypothetical auto create located on
PRIMARY AssignedLocation CustNum
hind_c_1682821057_24A clustered hypothetical auto create located on
PRIMARY PriceCode
hind_c_1682821057_24A_2A clustered hypothetical auto create located on
PRIMARY PriceCode CustNum
hind_c_1682821057_2A clustered hypothetical auto create located on PRIMARY
CustNum
hind_c_1682821057_2A_24A clustered hypothetical auto create located on
PRIMARY CustNum PriceCode
Name nonclustered located on PRIMARY Name
tblCust10 nonclustered located on PRIMARY CustNum Name City Phone
tblCust13 nonclustered located on PRIMARY CustNum SalesComm
tblCust2 clustered located on PRIMARY State CustNum
tblCust23 nonclustered located on PRIMARY CustNum PriceCode
tblCust25 nonclustered located on PRIMARY CustNum Name City State
tblCust26 nonclustered located on PRIMARY PriceCode CustNum Name City
State
tblCust31 nonclustered located on PRIMARY AssignedLocation PriceCode
tblCust7 nonclustered located on PRIMARY SalesComm DateNewComm
tblCust9 nonclustered located on PRIMARY CustNum PriceIncrease
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:#oAjuKxLFHA.3844@.TK2MSFTNGP14.phx.gbl...
> > The table has 12 indexes,
> WOW! That isn't the most I've ever seen, but it's certainly more than I'm
> used to. Why is one column covered in seven different indices?
> > We tried to remove indexes one by one, we get same Message 169.
> Can you show the result of:
> EXEC sp_help tablename
> EXEC sp_helpindex tablename
>|||DROP INDEX should do it.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Dean J Garrett" <info@.amuletc.com> wrote in message news:%23Sxvb3xLFHA.2648@.TK2MSFTNGP14.phx.gbl...
> Here is the output from sp_helpindex
> I think the hypotheticals are added by the Index wizard ... how do you get
> rid of these?
> --
> Address nonclustered located on PRIMARY Address
> CustID nonclustered unique located on PRIMARY CustID
> CustNum nonclustered unique located on PRIMARY CustNum
> hind_1682821057_24A_2A nonclustered hypothetical auto create located on
> PRIMARY PriceCode CustNum
> hind_1682821057_2A_24A_50A nonclustered hypothetical auto create located
> on PRIMARY CustNum PriceCode AssignedLocation
> hind_1682821057_2A_50A nonclustered hypothetical auto create located on
> PRIMARY CustNum AssignedLocation
> hind_1682821057_50A_24A_2A nonclustered hypothetical auto create located
> on PRIMARY AssignedLocation PriceCode CustNum
> hind_1682821057_50A_2A nonclustered hypothetical auto create located on
> PRIMARY AssignedLocation CustNum
> hind_c_1682821057_24A clustered hypothetical auto create located on
> PRIMARY PriceCode
> hind_c_1682821057_24A_2A clustered hypothetical auto create located on
> PRIMARY PriceCode CustNum
> hind_c_1682821057_2A clustered hypothetical auto create located on PRIMARY
> CustNum
> hind_c_1682821057_2A_24A clustered hypothetical auto create located on
> PRIMARY CustNum PriceCode
> Name nonclustered located on PRIMARY Name
> tblCust10 nonclustered located on PRIMARY CustNum Name City Phone
> tblCust13 nonclustered located on PRIMARY CustNum SalesComm
> tblCust2 clustered located on PRIMARY State CustNum
> tblCust23 nonclustered located on PRIMARY CustNum PriceCode
> tblCust25 nonclustered located on PRIMARY CustNum Name City State
> tblCust26 nonclustered located on PRIMARY PriceCode CustNum Name City
> State
> tblCust31 nonclustered located on PRIMARY AssignedLocation PriceCode
> tblCust7 nonclustered located on PRIMARY SalesComm DateNewComm
> tblCust9 nonclustered located on PRIMARY CustNum PriceIncrease
>
> "Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
> news:#oAjuKxLFHA.3844@.TK2MSFTNGP14.phx.gbl...
>> > The table has 12 indexes,
>> WOW! That isn't the most I've ever seen, but it's certainly more than I'm
>> used to. Why is one column covered in seven different indices?
>> > We tried to remove indexes one by one, we get same Message 169.
>> Can you show the result of:
>> EXEC sp_help tablename
>> EXEC sp_helpindex tablename
>>
>
Showing posts with label reindex. Show all posts
Showing posts with label reindex. Show all posts
Friday, March 30, 2012
Index troubles
We're seeing funny things with SQL Server 2000 when dealing with indexes on
a customer table. When we issue:
DBCC reindex (cust)
we see an error Message 169: a column has been specified more than once in
the order by list.
We see the same error during Update Statistics.
We're using the Query analyzer - index tuning wizard to create indexes on
this table. The table has 12 indexes, and the field Cust_num is used in 7 of
12 indexes.
We tried to remove indexes one by one, we get same Message 169.
Any clues? Thanks!!
> The table has 12 indexes,
WOW! That isn't the most I've ever seen, but it's certainly more than I'm
used to. Why is one column covered in seven different indices?
> We tried to remove indexes one by one, we get same Message 169.
Can you show the result of:
EXEC sp_help tablename
EXEC sp_helpindex tablename
|||Here is the output from sp_helpindex
I think the hypotheticals are added by the Index wizard ... how do you get
rid of these?
Address nonclustered located on PRIMARY Address
CustID nonclustered unique located on PRIMARY CustID
CustNum nonclustered unique located on PRIMARY CustNum
hind_1682821057_24A_2A nonclustered hypothetical auto create located on
PRIMARY PriceCode CustNum
hind_1682821057_2A_24A_50A nonclustered hypothetical auto create located
on PRIMARY CustNum PriceCode AssignedLocation
hind_1682821057_2A_50A nonclustered hypothetical auto create located on
PRIMARY CustNum AssignedLocation
hind_1682821057_50A_24A_2A nonclustered hypothetical auto create located
on PRIMARY AssignedLocation PriceCode CustNum
hind_1682821057_50A_2A nonclustered hypothetical auto create located on
PRIMARY AssignedLocation CustNum
hind_c_1682821057_24A clustered hypothetical auto create located on
PRIMARY PriceCode
hind_c_1682821057_24A_2A clustered hypothetical auto create located on
PRIMARY PriceCode CustNum
hind_c_1682821057_2A clustered hypothetical auto create located on PRIMARY
CustNum
hind_c_1682821057_2A_24A clustered hypothetical auto create located on
PRIMARY CustNum PriceCode
Name nonclustered located on PRIMARY Name
tblCust10 nonclustered located on PRIMARY CustNum Name City Phone
tblCust13 nonclustered located on PRIMARY CustNum SalesComm
tblCust2 clustered located on PRIMARY State CustNum
tblCust23 nonclustered located on PRIMARY CustNum PriceCode
tblCust25 nonclustered located on PRIMARY CustNum Name City State
tblCust26 nonclustered located on PRIMARY PriceCode CustNum Name City
State
tblCust31 nonclustered located on PRIMARY AssignedLocation PriceCode
tblCust7 nonclustered located on PRIMARY SalesComm DateNewComm
tblCust9 nonclustered located on PRIMARY CustNum PriceIncrease
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:#oAjuKxLFHA.3844@.TK2MSFTNGP14.phx.gbl...
> WOW! That isn't the most I've ever seen, but it's certainly more than I'm
> used to. Why is one column covered in seven different indices?
>
> Can you show the result of:
> EXEC sp_help tablename
> EXEC sp_helpindex tablename
>
|||DROP INDEX should do it.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Dean J Garrett" <info@.amuletc.com> wrote in message news:%23Sxvb3xLFHA.2648@.TK2MSFTNGP14.phx.gbl...
> Here is the output from sp_helpindex
> I think the hypotheticals are added by the Index wizard ... how do you get
> rid of these?
> --
> Address nonclustered located on PRIMARY Address
> CustID nonclustered unique located on PRIMARY CustID
> CustNum nonclustered unique located on PRIMARY CustNum
> hind_1682821057_24A_2A nonclustered hypothetical auto create located on
> PRIMARY PriceCode CustNum
> hind_1682821057_2A_24A_50A nonclustered hypothetical auto create located
> on PRIMARY CustNum PriceCode AssignedLocation
> hind_1682821057_2A_50A nonclustered hypothetical auto create located on
> PRIMARY CustNum AssignedLocation
> hind_1682821057_50A_24A_2A nonclustered hypothetical auto create located
> on PRIMARY AssignedLocation PriceCode CustNum
> hind_1682821057_50A_2A nonclustered hypothetical auto create located on
> PRIMARY AssignedLocation CustNum
> hind_c_1682821057_24A clustered hypothetical auto create located on
> PRIMARY PriceCode
> hind_c_1682821057_24A_2A clustered hypothetical auto create located on
> PRIMARY PriceCode CustNum
> hind_c_1682821057_2A clustered hypothetical auto create located on PRIMARY
> CustNum
> hind_c_1682821057_2A_24A clustered hypothetical auto create located on
> PRIMARY CustNum PriceCode
> Name nonclustered located on PRIMARY Name
> tblCust10 nonclustered located on PRIMARY CustNum Name City Phone
> tblCust13 nonclustered located on PRIMARY CustNum SalesComm
> tblCust2 clustered located on PRIMARY State CustNum
> tblCust23 nonclustered located on PRIMARY CustNum PriceCode
> tblCust25 nonclustered located on PRIMARY CustNum Name City State
> tblCust26 nonclustered located on PRIMARY PriceCode CustNum Name City
> State
> tblCust31 nonclustered located on PRIMARY AssignedLocation PriceCode
> tblCust7 nonclustered located on PRIMARY SalesComm DateNewComm
> tblCust9 nonclustered located on PRIMARY CustNum PriceIncrease
>
> "Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
> news:#oAjuKxLFHA.3844@.TK2MSFTNGP14.phx.gbl...
>
a customer table. When we issue:
DBCC reindex (cust)
we see an error Message 169: a column has been specified more than once in
the order by list.
We see the same error during Update Statistics.
We're using the Query analyzer - index tuning wizard to create indexes on
this table. The table has 12 indexes, and the field Cust_num is used in 7 of
12 indexes.
We tried to remove indexes one by one, we get same Message 169.
Any clues? Thanks!!
> The table has 12 indexes,
WOW! That isn't the most I've ever seen, but it's certainly more than I'm
used to. Why is one column covered in seven different indices?
> We tried to remove indexes one by one, we get same Message 169.
Can you show the result of:
EXEC sp_help tablename
EXEC sp_helpindex tablename
|||Here is the output from sp_helpindex
I think the hypotheticals are added by the Index wizard ... how do you get
rid of these?
Address nonclustered located on PRIMARY Address
CustID nonclustered unique located on PRIMARY CustID
CustNum nonclustered unique located on PRIMARY CustNum
hind_1682821057_24A_2A nonclustered hypothetical auto create located on
PRIMARY PriceCode CustNum
hind_1682821057_2A_24A_50A nonclustered hypothetical auto create located
on PRIMARY CustNum PriceCode AssignedLocation
hind_1682821057_2A_50A nonclustered hypothetical auto create located on
PRIMARY CustNum AssignedLocation
hind_1682821057_50A_24A_2A nonclustered hypothetical auto create located
on PRIMARY AssignedLocation PriceCode CustNum
hind_1682821057_50A_2A nonclustered hypothetical auto create located on
PRIMARY AssignedLocation CustNum
hind_c_1682821057_24A clustered hypothetical auto create located on
PRIMARY PriceCode
hind_c_1682821057_24A_2A clustered hypothetical auto create located on
PRIMARY PriceCode CustNum
hind_c_1682821057_2A clustered hypothetical auto create located on PRIMARY
CustNum
hind_c_1682821057_2A_24A clustered hypothetical auto create located on
PRIMARY CustNum PriceCode
Name nonclustered located on PRIMARY Name
tblCust10 nonclustered located on PRIMARY CustNum Name City Phone
tblCust13 nonclustered located on PRIMARY CustNum SalesComm
tblCust2 clustered located on PRIMARY State CustNum
tblCust23 nonclustered located on PRIMARY CustNum PriceCode
tblCust25 nonclustered located on PRIMARY CustNum Name City State
tblCust26 nonclustered located on PRIMARY PriceCode CustNum Name City
State
tblCust31 nonclustered located on PRIMARY AssignedLocation PriceCode
tblCust7 nonclustered located on PRIMARY SalesComm DateNewComm
tblCust9 nonclustered located on PRIMARY CustNum PriceIncrease
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:#oAjuKxLFHA.3844@.TK2MSFTNGP14.phx.gbl...
> WOW! That isn't the most I've ever seen, but it's certainly more than I'm
> used to. Why is one column covered in seven different indices?
>
> Can you show the result of:
> EXEC sp_help tablename
> EXEC sp_helpindex tablename
>
|||DROP INDEX should do it.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Dean J Garrett" <info@.amuletc.com> wrote in message news:%23Sxvb3xLFHA.2648@.TK2MSFTNGP14.phx.gbl...
> Here is the output from sp_helpindex
> I think the hypotheticals are added by the Index wizard ... how do you get
> rid of these?
> --
> Address nonclustered located on PRIMARY Address
> CustID nonclustered unique located on PRIMARY CustID
> CustNum nonclustered unique located on PRIMARY CustNum
> hind_1682821057_24A_2A nonclustered hypothetical auto create located on
> PRIMARY PriceCode CustNum
> hind_1682821057_2A_24A_50A nonclustered hypothetical auto create located
> on PRIMARY CustNum PriceCode AssignedLocation
> hind_1682821057_2A_50A nonclustered hypothetical auto create located on
> PRIMARY CustNum AssignedLocation
> hind_1682821057_50A_24A_2A nonclustered hypothetical auto create located
> on PRIMARY AssignedLocation PriceCode CustNum
> hind_1682821057_50A_2A nonclustered hypothetical auto create located on
> PRIMARY AssignedLocation CustNum
> hind_c_1682821057_24A clustered hypothetical auto create located on
> PRIMARY PriceCode
> hind_c_1682821057_24A_2A clustered hypothetical auto create located on
> PRIMARY PriceCode CustNum
> hind_c_1682821057_2A clustered hypothetical auto create located on PRIMARY
> CustNum
> hind_c_1682821057_2A_24A clustered hypothetical auto create located on
> PRIMARY CustNum PriceCode
> Name nonclustered located on PRIMARY Name
> tblCust10 nonclustered located on PRIMARY CustNum Name City Phone
> tblCust13 nonclustered located on PRIMARY CustNum SalesComm
> tblCust2 clustered located on PRIMARY State CustNum
> tblCust23 nonclustered located on PRIMARY CustNum PriceCode
> tblCust25 nonclustered located on PRIMARY CustNum Name City State
> tblCust26 nonclustered located on PRIMARY PriceCode CustNum Name City
> State
> tblCust31 nonclustered located on PRIMARY AssignedLocation PriceCode
> tblCust7 nonclustered located on PRIMARY SalesComm DateNewComm
> tblCust9 nonclustered located on PRIMARY CustNum PriceIncrease
>
> "Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
> news:#oAjuKxLFHA.3844@.TK2MSFTNGP14.phx.gbl...
>
Index troubles
We're seeing funny things with SQL Server 2000 when dealing with indexes on
a customer table. When we issue:
DBCC reindex (cust)
we see an error Message 169: a column has been specified more than once in
the order by list.
We see the same error during Update Statistics.
We're using the Query analyzer - index tuning wizard to create indexes on
this table. The table has 12 indexes, and the field Cust_num is used in 7 of
12 indexes.
We tried to remove indexes one by one, we get same Message 169.
Any clues? Thanks!!> The table has 12 indexes,
WOW! That isn't the most I've ever seen, but it's certainly more than I'm
used to. Why is one column covered in seven different indices?
> We tried to remove indexes one by one, we get same Message 169.
Can you show the result of:
EXEC sp_help tablename
EXEC sp_helpindex tablename|||Here is the output from sp_helpindex
I think the hypotheticals are added by the Index wizard ... how do you get
rid of these?
Address nonclustered located on PRIMARY Address
CustID nonclustered unique located on PRIMARY CustID
CustNum nonclustered unique located on PRIMARY CustNum
hind_1682821057_24A_2A nonclustered hypothetical auto create located on
PRIMARY PriceCode CustNum
hind_1682821057_2A_24A_50A nonclustered hypothetical auto create located
on PRIMARY CustNum PriceCode AssignedLocation
hind_1682821057_2A_50A nonclustered hypothetical auto create located on
PRIMARY CustNum AssignedLocation
hind_1682821057_50A_24A_2A nonclustered hypothetical auto create located
on PRIMARY AssignedLocation PriceCode CustNum
hind_1682821057_50A_2A nonclustered hypothetical auto create located on
PRIMARY AssignedLocation CustNum
hind_c_1682821057_24A clustered hypothetical auto create located on
PRIMARY PriceCode
hind_c_1682821057_24A_2A clustered hypothetical auto create located on
PRIMARY PriceCode CustNum
hind_c_1682821057_2A clustered hypothetical auto create located on PRIMARY
CustNum
hind_c_1682821057_2A_24A clustered hypothetical auto create located on
PRIMARY CustNum PriceCode
Name nonclustered located on PRIMARY Name
tblCust10 nonclustered located on PRIMARY CustNum Name City Phone
tblCust13 nonclustered located on PRIMARY CustNum SalesComm
tblCust2 clustered located on PRIMARY State CustNum
tblCust23 nonclustered located on PRIMARY CustNum PriceCode
tblCust25 nonclustered located on PRIMARY CustNum Name City State
tblCust26 nonclustered located on PRIMARY PriceCode CustNum Name City
State
tblCust31 nonclustered located on PRIMARY AssignedLocation PriceCode
tblCust7 nonclustered located on PRIMARY SalesComm DateNewComm
tblCust9 nonclustered located on PRIMARY CustNum PriceIncrease
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:#oAjuKxLFHA.3844@.TK2MSFTNGP14.phx.gbl...
> WOW! That isn't the most I've ever seen, but it's certainly more than I'm
> used to. Why is one column covered in seven different indices?
>
> Can you show the result of:
> EXEC sp_help tablename
> EXEC sp_helpindex tablename
>|||DROP INDEX should do it.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Dean J Garrett" <info@.amuletc.com> wrote in message news:%23Sxvb3xLFHA.2648@.TK2MSFTNGP14.ph
x.gbl...
> Here is the output from sp_helpindex
> I think the hypotheticals are added by the Index wizard ... how do you get
> rid of these?
> --
> Address nonclustered located on PRIMARY Address
> CustID nonclustered unique located on PRIMARY CustID
> CustNum nonclustered unique located on PRIMARY CustNum
> hind_1682821057_24A_2A nonclustered hypothetical auto create located on
> PRIMARY PriceCode CustNum
> hind_1682821057_2A_24A_50A nonclustered hypothetical auto create located
> on PRIMARY CustNum PriceCode AssignedLocation
> hind_1682821057_2A_50A nonclustered hypothetical auto create located on
> PRIMARY CustNum AssignedLocation
> hind_1682821057_50A_24A_2A nonclustered hypothetical auto create located
> on PRIMARY AssignedLocation PriceCode CustNum
> hind_1682821057_50A_2A nonclustered hypothetical auto create located on
> PRIMARY AssignedLocation CustNum
> hind_c_1682821057_24A clustered hypothetical auto create located on
> PRIMARY PriceCode
> hind_c_1682821057_24A_2A clustered hypothetical auto create located on
> PRIMARY PriceCode CustNum
> hind_c_1682821057_2A clustered hypothetical auto create located on PRIMA
RY
> CustNum
> hind_c_1682821057_2A_24A clustered hypothetical auto create located on
> PRIMARY CustNum PriceCode
> Name nonclustered located on PRIMARY Name
> tblCust10 nonclustered located on PRIMARY CustNum Name City Phone
> tblCust13 nonclustered located on PRIMARY CustNum SalesComm
> tblCust2 clustered located on PRIMARY State CustNum
> tblCust23 nonclustered located on PRIMARY CustNum PriceCode
> tblCust25 nonclustered located on PRIMARY CustNum Name City State
> tblCust26 nonclustered located on PRIMARY PriceCode CustNum Name City
> State
> tblCust31 nonclustered located on PRIMARY AssignedLocation PriceCode
> tblCust7 nonclustered located on PRIMARY SalesComm DateNewComm
> tblCust9 nonclustered located on PRIMARY CustNum PriceIncrease
>
> "Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
> news:#oAjuKxLFHA.3844@.TK2MSFTNGP14.phx.gbl...
>
a customer table. When we issue:
DBCC reindex (cust)
we see an error Message 169: a column has been specified more than once in
the order by list.
We see the same error during Update Statistics.
We're using the Query analyzer - index tuning wizard to create indexes on
this table. The table has 12 indexes, and the field Cust_num is used in 7 of
12 indexes.
We tried to remove indexes one by one, we get same Message 169.
Any clues? Thanks!!> The table has 12 indexes,
WOW! That isn't the most I've ever seen, but it's certainly more than I'm
used to. Why is one column covered in seven different indices?
> We tried to remove indexes one by one, we get same Message 169.
Can you show the result of:
EXEC sp_help tablename
EXEC sp_helpindex tablename|||Here is the output from sp_helpindex
I think the hypotheticals are added by the Index wizard ... how do you get
rid of these?
Address nonclustered located on PRIMARY Address
CustID nonclustered unique located on PRIMARY CustID
CustNum nonclustered unique located on PRIMARY CustNum
hind_1682821057_24A_2A nonclustered hypothetical auto create located on
PRIMARY PriceCode CustNum
hind_1682821057_2A_24A_50A nonclustered hypothetical auto create located
on PRIMARY CustNum PriceCode AssignedLocation
hind_1682821057_2A_50A nonclustered hypothetical auto create located on
PRIMARY CustNum AssignedLocation
hind_1682821057_50A_24A_2A nonclustered hypothetical auto create located
on PRIMARY AssignedLocation PriceCode CustNum
hind_1682821057_50A_2A nonclustered hypothetical auto create located on
PRIMARY AssignedLocation CustNum
hind_c_1682821057_24A clustered hypothetical auto create located on
PRIMARY PriceCode
hind_c_1682821057_24A_2A clustered hypothetical auto create located on
PRIMARY PriceCode CustNum
hind_c_1682821057_2A clustered hypothetical auto create located on PRIMARY
CustNum
hind_c_1682821057_2A_24A clustered hypothetical auto create located on
PRIMARY CustNum PriceCode
Name nonclustered located on PRIMARY Name
tblCust10 nonclustered located on PRIMARY CustNum Name City Phone
tblCust13 nonclustered located on PRIMARY CustNum SalesComm
tblCust2 clustered located on PRIMARY State CustNum
tblCust23 nonclustered located on PRIMARY CustNum PriceCode
tblCust25 nonclustered located on PRIMARY CustNum Name City State
tblCust26 nonclustered located on PRIMARY PriceCode CustNum Name City
State
tblCust31 nonclustered located on PRIMARY AssignedLocation PriceCode
tblCust7 nonclustered located on PRIMARY SalesComm DateNewComm
tblCust9 nonclustered located on PRIMARY CustNum PriceIncrease
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:#oAjuKxLFHA.3844@.TK2MSFTNGP14.phx.gbl...
> WOW! That isn't the most I've ever seen, but it's certainly more than I'm
> used to. Why is one column covered in seven different indices?
>
> Can you show the result of:
> EXEC sp_help tablename
> EXEC sp_helpindex tablename
>|||DROP INDEX should do it.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Dean J Garrett" <info@.amuletc.com> wrote in message news:%23Sxvb3xLFHA.2648@.TK2MSFTNGP14.ph
x.gbl...
> Here is the output from sp_helpindex
> I think the hypotheticals are added by the Index wizard ... how do you get
> rid of these?
> --
> Address nonclustered located on PRIMARY Address
> CustID nonclustered unique located on PRIMARY CustID
> CustNum nonclustered unique located on PRIMARY CustNum
> hind_1682821057_24A_2A nonclustered hypothetical auto create located on
> PRIMARY PriceCode CustNum
> hind_1682821057_2A_24A_50A nonclustered hypothetical auto create located
> on PRIMARY CustNum PriceCode AssignedLocation
> hind_1682821057_2A_50A nonclustered hypothetical auto create located on
> PRIMARY CustNum AssignedLocation
> hind_1682821057_50A_24A_2A nonclustered hypothetical auto create located
> on PRIMARY AssignedLocation PriceCode CustNum
> hind_1682821057_50A_2A nonclustered hypothetical auto create located on
> PRIMARY AssignedLocation CustNum
> hind_c_1682821057_24A clustered hypothetical auto create located on
> PRIMARY PriceCode
> hind_c_1682821057_24A_2A clustered hypothetical auto create located on
> PRIMARY PriceCode CustNum
> hind_c_1682821057_2A clustered hypothetical auto create located on PRIMA
RY
> CustNum
> hind_c_1682821057_2A_24A clustered hypothetical auto create located on
> PRIMARY CustNum PriceCode
> Name nonclustered located on PRIMARY Name
> tblCust10 nonclustered located on PRIMARY CustNum Name City Phone
> tblCust13 nonclustered located on PRIMARY CustNum SalesComm
> tblCust2 clustered located on PRIMARY State CustNum
> tblCust23 nonclustered located on PRIMARY CustNum PriceCode
> tblCust25 nonclustered located on PRIMARY CustNum Name City State
> tblCust26 nonclustered located on PRIMARY PriceCode CustNum Name City
> State
> tblCust31 nonclustered located on PRIMARY AssignedLocation PriceCode
> tblCust7 nonclustered located on PRIMARY SalesComm DateNewComm
> tblCust9 nonclustered located on PRIMARY CustNum PriceIncrease
>
> "Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
> news:#oAjuKxLFHA.3844@.TK2MSFTNGP14.phx.gbl...
>
Wednesday, March 7, 2012
index database
In vfp database, i can use the command ' reindex' to index the database
again.
for SQL server, can i do that '
thanks a lotCan you explain what such a "reindex" is supposed to achieve? It is possible that you are looking
for the DBCC DBREINDEX or DBCC INDEXDEFRAG (2000) or for 2005 ALTER INDEX with either the REBUILD or
the REORGANIZE options.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Agnes" <agnes@.dynamictech.com.hk> wrote in message news:e$dcsir9GHA.2268@.TK2MSFTNGP05.phx.gbl...
> In vfp database, i can use the command ' reindex' to index the database again.
> for SQL server, can i do that '
> thanks a lot
>|||Hi Agnes
You don't give the version of SQL Server you are using check out DBCC
DBREINDEX and INDEXDEFRAG in Books Online if you are using SQL 2000 also DBCC
SHOWCONTIG which has an example script to only reindex indexes that are
fragmented by a certain amount. If using SQL 2005 check out the ALTER INDEX
statement and the sys.db_index_physical_stats view.
John
"Agnes" wrote:
> In vfp database, i can use the command ' reindex' to index the database
> again.
> for SQL server, can i do that '
> thanks a lot
>
>|||sp_MSforeachtable 'dbcc dbreindex(''?'')'
--
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Agnes" <agnes@.dynamictech.com.hk> wrote in message
news:e$dcsir9GHA.2268@.TK2MSFTNGP05.phx.gbl...
> In vfp database, i can use the command ' reindex' to index the database
> again.
> for SQL server, can i do that '
> thanks a lot
>
again.
for SQL server, can i do that '
thanks a lotCan you explain what such a "reindex" is supposed to achieve? It is possible that you are looking
for the DBCC DBREINDEX or DBCC INDEXDEFRAG (2000) or for 2005 ALTER INDEX with either the REBUILD or
the REORGANIZE options.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Agnes" <agnes@.dynamictech.com.hk> wrote in message news:e$dcsir9GHA.2268@.TK2MSFTNGP05.phx.gbl...
> In vfp database, i can use the command ' reindex' to index the database again.
> for SQL server, can i do that '
> thanks a lot
>|||Hi Agnes
You don't give the version of SQL Server you are using check out DBCC
DBREINDEX and INDEXDEFRAG in Books Online if you are using SQL 2000 also DBCC
SHOWCONTIG which has an example script to only reindex indexes that are
fragmented by a certain amount. If using SQL 2005 check out the ALTER INDEX
statement and the sys.db_index_physical_stats view.
John
"Agnes" wrote:
> In vfp database, i can use the command ' reindex' to index the database
> again.
> for SQL server, can i do that '
> thanks a lot
>
>|||sp_MSforeachtable 'dbcc dbreindex(''?'')'
--
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Agnes" <agnes@.dynamictech.com.hk> wrote in message
news:e$dcsir9GHA.2268@.TK2MSFTNGP05.phx.gbl...
> In vfp database, i can use the command ' reindex' to index the database
> again.
> for SQL server, can i do that '
> thanks a lot
>
Friday, February 24, 2012
index database
In vfp database, i can use the command ' reindex' to index the database
again.
for SQL server, can i do that ?
thanks a lot
Can you explain what such a "reindex" is supposed to achieve? It is possible that you are looking
for the DBCC DBREINDEX or DBCC INDEXDEFRAG (2000) or for 2005 ALTER INDEX with either the REBUILD or
the REORGANIZE options.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Agnes" <agnes@.dynamictech.com.hk> wrote in message news:e$dcsir9GHA.2268@.TK2MSFTNGP05.phx.gbl...
> In vfp database, i can use the command ' reindex' to index the database again.
> for SQL server, can i do that ?
> thanks a lot
>
|||Hi Agnes
You don't give the version of SQL Server you are using check out DBCC
DBREINDEX and INDEXDEFRAG in Books Online if you are using SQL 2000 also DBCC
SHOWCONTIG which has an example script to only reindex indexes that are
fragmented by a certain amount. If using SQL 2005 check out the ALTER INDEX
statement and the sys.db_index_physical_stats view.
John
"Agnes" wrote:
> In vfp database, i can use the command ' reindex' to index the database
> again.
> for SQL server, can i do that ?
> thanks a lot
>
>
|||sp_MSforeachtable 'dbcc dbreindex(''?'')'
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Agnes" <agnes@.dynamictech.com.hk> wrote in message
news:e$dcsir9GHA.2268@.TK2MSFTNGP05.phx.gbl...
> In vfp database, i can use the command ' reindex' to index the database
> again.
> for SQL server, can i do that ?
> thanks a lot
>
again.
for SQL server, can i do that ?
thanks a lot
Can you explain what such a "reindex" is supposed to achieve? It is possible that you are looking
for the DBCC DBREINDEX or DBCC INDEXDEFRAG (2000) or for 2005 ALTER INDEX with either the REBUILD or
the REORGANIZE options.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Agnes" <agnes@.dynamictech.com.hk> wrote in message news:e$dcsir9GHA.2268@.TK2MSFTNGP05.phx.gbl...
> In vfp database, i can use the command ' reindex' to index the database again.
> for SQL server, can i do that ?
> thanks a lot
>
|||Hi Agnes
You don't give the version of SQL Server you are using check out DBCC
DBREINDEX and INDEXDEFRAG in Books Online if you are using SQL 2000 also DBCC
SHOWCONTIG which has an example script to only reindex indexes that are
fragmented by a certain amount. If using SQL 2005 check out the ALTER INDEX
statement and the sys.db_index_physical_stats view.
John
"Agnes" wrote:
> In vfp database, i can use the command ' reindex' to index the database
> again.
> for SQL server, can i do that ?
> thanks a lot
>
>
|||sp_MSforeachtable 'dbcc dbreindex(''?'')'
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Agnes" <agnes@.dynamictech.com.hk> wrote in message
news:e$dcsir9GHA.2268@.TK2MSFTNGP05.phx.gbl...
> In vfp database, i can use the command ' reindex' to index the database
> again.
> for SQL server, can i do that ?
> thanks a lot
>
index database
In vfp database, i can use the command ' reindex' to index the database
again.
for SQL server, can i do that '
thanks a lotCan you explain what such a "reindex" is supposed to achieve? It is possible
that you are looking
for the DBCC DBREINDEX or DBCC INDEXDEFRAG (2000) or for 2005 ALTER INDEX wi
th either the REBUILD or
the REORGANIZE options.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Agnes" <agnes@.dynamictech.com.hk> wrote in message news:e$dcsir9GHA.2268@.TK2MSFTNGP05.phx.g
bl...
> In vfp database, i can use the command ' reindex' to index the database ag
ain.
> for SQL server, can i do that '
> thanks a lot
>|||Hi Agnes
You don't give the version of SQL Server you are using check out DBCC
DBREINDEX and INDEXDEFRAG in Books Online if you are using SQL 2000 also DBC
C
SHOWCONTIG which has an example script to only reindex indexes that are
fragmented by a certain amount. If using SQL 2005 check out the ALTER INDEX
statement and the sys.db_index_physical_stats view.
John
"Agnes" wrote:
> In vfp database, i can use the command ' reindex' to index the database
> again.
> for SQL server, can i do that '
> thanks a lot
>
>|||sp_MSforeachtable 'dbcc dbreindex(''?'')'
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Agnes" <agnes@.dynamictech.com.hk> wrote in message
news:e$dcsir9GHA.2268@.TK2MSFTNGP05.phx.gbl...
> In vfp database, i can use the command ' reindex' to index the database
> again.
> for SQL server, can i do that '
> thanks a lot
>
again.
for SQL server, can i do that '
thanks a lotCan you explain what such a "reindex" is supposed to achieve? It is possible
that you are looking
for the DBCC DBREINDEX or DBCC INDEXDEFRAG (2000) or for 2005 ALTER INDEX wi
th either the REBUILD or
the REORGANIZE options.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Agnes" <agnes@.dynamictech.com.hk> wrote in message news:e$dcsir9GHA.2268@.TK2MSFTNGP05.phx.g
bl...
> In vfp database, i can use the command ' reindex' to index the database ag
ain.
> for SQL server, can i do that '
> thanks a lot
>|||Hi Agnes
You don't give the version of SQL Server you are using check out DBCC
DBREINDEX and INDEXDEFRAG in Books Online if you are using SQL 2000 also DBC
C
SHOWCONTIG which has an example script to only reindex indexes that are
fragmented by a certain amount. If using SQL 2005 check out the ALTER INDEX
statement and the sys.db_index_physical_stats view.
John
"Agnes" wrote:
> In vfp database, i can use the command ' reindex' to index the database
> again.
> for SQL server, can i do that '
> thanks a lot
>
>|||sp_MSforeachtable 'dbcc dbreindex(''?'')'
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Agnes" <agnes@.dynamictech.com.hk> wrote in message
news:e$dcsir9GHA.2268@.TK2MSFTNGP05.phx.gbl...
> In vfp database, i can use the command ' reindex' to index the database
> again.
> for SQL server, can i do that '
> thanks a lot
>
Index corrupted
We need to know, how to reorganize or reindex on sql-ce?
With SQL CE 2.0, the only mechanism to re-calculate index stats and repair corruption is to call the Compact method on the SqlCeEngine object. If you want to assess the degree of selectivity of your current index, you can open your SQL CE database in Pocket Query Analyzer and run sp_show_statistics_steps 'table', 'index'
Check the SQL CE Books Online for an example of using the Compact operation.
-Darren
Subscribe to:
Posts (Atom)