Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
davidshq
Posting Yak Master
119 Posts |
 Posted - 2005-12-03 : 19:10:28
|
| I know that its good for a database to reindex it occasionally, but what if you have other tables tied to a field by that index? For example, if record 235 in table1 is referenced by record 20 in table2 and you reindex, record 235 in table1 could be reindexed as table1. The link is then broken. Then what?David.- http://www.gamesecretary.com/- http://www.thehungersite.com/- http://www.grid.org/ |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2005-12-03 : 19:26:44
|
| Reindexing does not change any data in the table. |
 |
|
|
davidshq
Posting Yak Master
119 Posts |
|
|
paulrandal
Yak with Vast SQL Skills
899 Posts |
Posted - 2005-12-04 : 16:56:14
|
| However it does change the location of records as they will be moved onto newly allocated pages as part of the index rebuild process.This is not a problem though because references to index records are logical (i.e. using the index key) and not physical (i.e. using the record location).Now, technically there is a case in SQL Server 2000 where the data will actually change - that is when you rebuild a non-unique clustered index. The automatically generated uniquifiers are regenerated. This doesn't affect the user data but technically is changing one of the columns stored in the row. This doesn't affect relational constraints but will cause non-clustered indexes to be rebuilt as the clustered index uniquifer is used as part of the clustered index key for non-clustered index -> clustered index mappings.For more info on when to rebuild indexes in a database, see the following whitepaper - it's only good to do it if you have queries that will benefit from it.http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspxRegardsPaul RandalLead Program Manager, Microsoft SQL Server Storage Engine(Legalese: This posting is provided "AS IS" with no warranties, and confers no rights.) |
|
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2005-12-04 : 17:40:58
|
quote:
automatically generated uniquifiers
Is that anything like an Illudium Q-36 Explosive Space Modulator?Be One with the OptimizerTG |
|
|
|
davidshq
Posting Yak Master
119 Posts |
|
|
paulrandal
Yak with Vast SQL Skills
899 Posts |
Posted - 2005-12-07 : 21:01:48
|
quote:
Originally posted by TG
quote:
automatically generated uniquifiers
Is that anything like an Illudium Q-36 Explosive Space Modulator?Be One with the OptimizerTG
Sorry - only familiar with the Q-35  Paul RandalLead Program Manager, Microsoft SQL Server Storage Engine(Legalese: This posting is provided "AS IS" with no warranties, and confers no rights.) |
|
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2005-12-08 : 07:35:31
|
| oh well, I guess there's not too many Marvin the Martian fans out there...Be One with the OptimizerTG |
|
|
|
|
|
|
Re-Indexing.