SQL Server single user mode at table level

There is no default option to access a table as a single user mode , but some time we may need to block the other users to access the table till we do some modifications. for E.g. to reset the identity property.

below statement will hold an exclusive lock on the table and will prevent other users to access this table.

begin tran select * from mytable with (holdlock , tablockx) where 1=0

To simulate this ,create 2 sessions in SSMS and in the 1st session execute the insert statements in a continuous while loop using below query

-- session -1 --create table mytable (iid int identity(1,1) , ddate datetime) while 1=1 begin insert into mytable select GETDATE() print GETDATE() continue end

Now , you see the records are inserting into mytable continuously. this is to simulate that this table being updated with transactions.

If you want to lock this table for maintanance purpose then you must block other users to stop modifying this table. to simulate this , open 2nd window and place a lock and once you are done then release the lock by commit the transaction

-- session -2 begin tran select * from mytable with (holdlock , tablockx) where 1=0 -- <modify the table and execute the below line> --commit tran

open the first window and see , the insert execution will be blocked and waiting for the 1st windows to release the lock.

Once you are done with your maintanance work on the mytable then commit the tran

-- session -2 --begin tran --select * from mytable with (holdlock , tablockx) where 1=0 -- <modify the table and execute the below line> commit tran