i am looking at speeding up the performance of my database, and thought that Rebuilding some indexes might help. My question is, will this invalidate Foreign Keys from other tables pointing to the table I am rebuilding, or will the links be preserved?
- Anybody can ask a question
- Anybody can answer
- The best answers are voted up and rise to the top
No it wont invalidate foreign keys.
Rebuilding an index drops and re-creates the index thereby removing fragmentation and it reclaims disk space by compacting the pages based on the specified or existing fill factor setting, and reorders the index rows in contiguous pages. FOREIGN KEY constraints do not have to be dropped in advance.
Now if you disable Index then :
The Query optimizer wont be able to use the index and any of your queries that uses index hint referencing the disabled index will FAIL. SQL Server retains the metadata about the index and the usage stats in
Disabling Non-Clustered (NC) Index: This will straight away delete the index pages and thereby freeing up the space in the database.
Disabling a CLUSTERED Index: All related NC indexes and views become unavailable and FK's are also disabled. Any queries that reference the table will fail, as a CLUSTERED index is itself the data. Note that the data will still remain, but will be inaccessible. Only way to again access data is to REBUILD the index.
Disk space wise, it's usually a better idea to reorganize mildly fragmented indexes, and rebuild the heavily fragmented ones. You can browse the net for the typical guidelines concerning this, but I tend to ignore anything below 10-15% fragmentation, reorganize anything between that and 35%, and rebuild anything above that.