Will utilize the Rowlock only if ShipperID is indexed?


SQL Server, if a table donnot have a cluster index,
it implement a default cluster index.

If you mark indexed sets, the problem is simple,
but with not indexed sets you can have not immediate results:

-- Phone have 3 distinct values
-- session 1
use Northwind
begin tran
select * from dbo.Shippers (UPDLOCK,ROWLOCK)  where  Phone='(503) 555-9831'

-- session 2 is locked
use Northwind
begin tran
select * from dbo.Shippers (UPDLOCK,ROWLOCK)  where  not  Phone='(503) 555-9831'

but you can read the free rows if you skip the Phone, and use the primary index:

-- session 2 is not locked
use Northwind
begin tran
select * from dbo.Shippers (UPDLOCK,ROWLOCK)  where  not ShipperID=1'

Then, where SQL Server put the lock ?

Fabio