Lock only a row in insert command sql server I have this initial code below to create and insert some values on my table
IF (OBJECT_ID('dbo.Teste_Nolock') IS NOT NULL) DROP TABLE dbo.Teste_Nolock
CREATE TABLE dbo.Teste_Nolock (
    Id INT IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED,
    Nome VARCHAR(100)
)

INSERT INTO dbo.Teste_Nolock
VALUES ('User'), ('Name'), ('DataBase'), ('SQL Server'), ('Power BI'), ('SSRS'), ('SSIS'), ('SSAS'), ('Blog')

BEGIN TRANSACTION

UPDATE dbo.Teste_Nolock
SET Nome = 'Nolock Test'
WHERE Nome = 'User'


When I execute update without a commit or rollback, SQL Server is locking my table and i can't execute the command SELECT * FROM dbo.Teste_Nolock WHERE Nome = 'SQL Server' for example. How can i solve this kind of problem?
Make sure you have an index on the Nome column, allowing SQL Server to &quot;bypass&quot; (not having to read) those rows you exclude in your WHERE clause. I.e.
CREATE INDEX yourindexname ON Teste_Nolock(Nome)