using 'with nolock' in transaction or when dropping table

Question

I am trying clean a temporal table. the quickest way was to drop or truncate table then rollback and only include required rows. Now my issue is with the 'deadlock' of database. is there a way to use 'with nolock' for the database to not lock

BEGIN TRANSACTION;
drop table audit.Testing with (nolock) ;

rollback transaction

 SELECT *
 from
 (select *
 ,rn = ROW_NUMBER() OVER (PARTITION BY Id ORDER BY Id DESC)
  FROM audit.testing with (nolock)
  ) a
   where rn =1
  order by Id, SysEndTime desc```

Answer 1

You are in a deadlock because WITH (NOLOCK) is the equivalent of using READ UNCOMMITTED READUNCOMMITTED Specifies that dirty reads are allowed. No shared locks are issued to prevent other transactions from modifying data read by the current transaction, and exclusive locks set by other transactions do not block the current transaction from reading the locked data. Allowing dirty reads can cause higher concurrency, but at the cost of reading data modifications that then are rolled back by other transactions. This may generate errors for your transaction, present users with data that was never committed, or cause users to see records twice (or not at all).

READUNCOMMITTED and NOLOCK hints apply only to data locks. All queries, including those with READUNCOMMITTED and NOLOCK hints, acquire Sch-S (schema stability) locks during compilation and execution. Because of this, queries are blocked when a concurrent transaction holds a Sch-M (schema modification) lock on the table. For example, a data definition language (DDL) operation acquires a Sch-M lock before it modifies the schema information of the table. Any concurrent queries, including those running with READUNCOMMITTED or NOLOCK hints, are blocked when attempting to acquire a Sch-S lock. Conversely, a query holding a Sch-S lock blocks a concurrent transaction that attempts to acquire a Sch-M lock microsoft docs