What is the impact if indexed where allow_row_locks = 0 AND allow_page_locks = 0 are deleted?


Your question is phrased oddly. Are you asking what the impact would be to disallow page and row locking on an index?

If you disallow both page and row locking, then it will force an exclusive lock on the table, and either an exclusive or intent-exclusive lock on the index, and will block other sessions from accessing the object, until the transaction that's writing to the table/index completes.

Generally this would not be recommended, since you could end up locking a multi-million row table even if you're only updating a single row, and it would impact concurrency.