Index management and database availability
Hello
I'm working on an application which is mainly used to look up compressors and their data which are held in documents.
This is a simplified representation of the tables of importance:
Code:Document
--------
Id (int, identity) (PK)
Number (nvarchar(50))
Compressor
----------
DocumentId (int) (PK, FK)
Id (int, identity) (PK)
Name (nvarchar(50))
CompressorData
--------------
CompressorId (int) (PK, FK))
Id (int, identity) (PK)
Value (nvarchar(50))
The tables are linked as follows:
Document -> Compressor -> CompressorData
Non c**Banned Word**ered indexes are created on Document.Number and Compresor.Name because these fields are used for querying.
At certain points corrections will be released on compressors which
will result in:
- Creating new documents with new document numbers (note that a non c**Banned Word**erd index exists on Document.Number).
- Copying affected compressors of existing documents into the new documents (note that a non c**Banned Word**erd index exists on Compressor.Name).
- Copying the data of the affected compressors into the new documents.
This can result in creating ten's of new documents and copying hundreds compressors and thousands compressor data records.
My question:
Will the users still be able to query for compressors while corrections are released (thinking about indexes which need to be modified) or will their be so many locks held that the database becomes unusable?
Thanks in advance,
Tan
