Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question

3 Answers 3

will this invalidate Foreign Keys from other tables pointing to the table I am rebuilding, or will the links be preserved?

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 sys.dm_db_index_usage_stats. Also, Foreign Key constraints are DISABLED.

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.

share|improve this answer

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.

share|improve this answer

Rebuilding an index only rebuilds the given index. Nothing else is affected.

If you rebuild a clustered index, the table itself is rebuilt since "the index is the table" for clustered indexes.

Foreign keys are not affected either way.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.