4 ms·

This is your typical false economy trading a very small performance gain for strongly degraded data integrity. If the indexes are too much to ask you're basica

This is your typical false economy trading a very small performance gain for strongly degraded data integrity. If the indexes are too much to ask you're basically saying that the referred to foreign records are never looked up (they have no key index!) and/or that the foreign records are never gathered up for referring table. If that's the case the problem isn't having superfluous indexes it's that you have superfluous data in your database.

cogman10 3y ago
> a very small performance gain for strongly degraded data integrity. It's not very small. Doing a lookup on every write can be hugely detrimental to performance especially with large numbers of writes. > If the indexes are too much to ask you're basically saying that the referred to foreign records are never looked up (they have no key index!) That does not follow. The lookup for foreign records is not free so avoiding doing it when you don't need to will gain faster performance vs doing it all the time. Indexes make lookups faster, they don't make them free. Further, you have to consider the impact of locks on such a system. Writes to a table that references another will lock the contents of the second table while the write is in flight. So if I wanted to update the foreign record in any way, that task now gets blocked until data integrity check finishes. In MSSQL, that lock is held until the end of the transaction.