0) Ai schimbat textul iniţial al răspunsului tău? Era interesant.

1) În mod implicit, o restricţie PRIMARY KEY (ID INT IDENTITY PRIMARY KEY) pp. crearea unui index unic clustered.
Toate interogarile de mai sus folosesc (Clustered Index Scan) un index:
SELECT    i.name, i.index_id, i.type, i.type_desc
FROM    sys.indexes i
WHERE    i.object_id = OBJECT_ID('dbo.Test');
Rezultate:
name                       index_id type type_desc
-------------------------- -------- ---- ------------
PK__Test__3214EC275CC5B545 1        1    CLUSTERED

Să pp. că prin folosirea unui index înţelegem Seek.

2) Conform (http://msdn.microsoft.com/en-us/library/ms189849.aspx), SQL Server nu menţine lock-uri la nivel de coloană, nivel maxim de detaliere a lock-urilor fiind cel de înregistrare: RID lock (tabele heap) sau KEY lock (indecşi).

3) Dacă foloseşti nivelul implicit de izolare a tranzacţiilor (READ COMMITTED) lock-urile U vor fi ridicate imediat după ce SQL Server decide că înregistrarea citită nu tb. modificată. Cu alte cuvinte, în acest caz, lock-urile U (care nu sunt convertite in lock-uri X) nu sunt menţinute pâna la finalul tranzacţiei lucru care ar putea bloca alte tranzacţii care doresc lock-uri X, doar le-ar "încetini".

4) Chiar şi în situaţia sugerată (anterior) de către tine apar lock-uri U, chiar mai multe lock-uri de tip U deoarece sunt implicaţi doi indecşi (unul clustered şi altul nonclustered folosit pt. seek Col1 = 2):

SET NOCOUNT ON;

CREATE DATABASE CocoJambo;
GO

USE CocoJambo;
GO

CREATE TABLE dbo.Test
(
ID INT IDENTITY PRIMARY KEY,
Col1 INT NOT NULL,
Col2 INT NOT NULL,
Col3 INT NOT NULL,
Col4 VARCHAR(5000) DEFAULT 'A' NOT NULL
);
GO

INSERT  dbo.Test(Col1, Col2, Col3)
VALUES  (1,11,111), (2,22,222), (2,33,333), (2,44,444), (3,55,555);
GO

PRINT 'CREATE INDEX IN_Test_Col1 ...';
CREATE INDEX IN_Test_Col1
ON dbo.Test(Col1);
GO

SET SHOWPLAN_TEXT ON; --Afiseaza planul de executie; Datele nu sunt modificate.
GO
UPDATE    Test
SET        Col2 = Col2 * 10
WHERE    Col1 = 1;
GO
SET SHOWPLAN_TEXT OFF;
GO

PRINT 'DROP INDEX dbo.Test.IN_Test_Col1'
DROP INDEX dbo.Test.IN_Test_Col1;
GO

PRINT 'CREATE INDEX IN_Test_Col1_#_Col2 ...'
CREATE INDEX IN_Test_Col1_#_Col2
ON dbo.Test(Col1)
INCLUDE(Col2);
GO

SET SHOWPLAN_TEXT ON;
GO
UPDATE    Test
SET        Col2 = Col2 * 10
WHERE    Col1 = 2;
GO
SET SHOWPLAN_TEXT OFF;
GO

--Test (Doar pe un server de test)

PRINT 'Index clustered -> [locked resourse hash]'
SELECT *, %%lockres%% [locked resourse hash] FROM Test WITH(INDEX=0);
PRINT 'IN_Test_Col1_#_Col2 -> [locked resourse hash]'
SELECT *, %%lockres%% [locked resourse hash] FROM Test WITH(INDEX=IN_Test_Col1_#_Col2);
SELECT i.index_id, i.name, i.type, i.type_desc, i.is_primary_key FROM sys.indexes i WHERE i.object_id = OBJECT_ID('dbo.Test');
DBCC IND('CocoJambo', 'dbo.Test', -1);

DBCC TRACEON(-1, 3604); --Redirectioneaza output-ul de la comenzile DBCC catre client
DBCC TRACEON(-1, 1200); --Afiseaza lock-urile
GO

BEGIN TRANSACTION;
UPDATE    Test
SET        Col2 = Col2 * 10
WHERE    Col1 = 2;
ROLLBACK

DBCC TRACEOFF(-1, 3604);
DBCC TRACEOFF(-1, 1200);

Rezultate:
CREATE INDEX IN_Test_Col1 ... (acest index este parţial şi "nu va putea fi utilizat"(există şi excepţii); PK___Test__... este indexul clustered)

-------------------------------------------------------------------------------------------------------------------------------------------------------------------
  |--Clustered Index Update(OBJECT:([CocoJambo].[dbo].[Test].[PK__Test__3214EC277F60ED59]), SET:([CocoJambo].[dbo].[Test].[Col2] = RaiseIfNullUpdate([Expr1003])))
       |--Compute Scalar(DEFINE:([Expr1003]=[CocoJambo].[dbo].[Test].[Col2]*(10)))
            |--Top(ROWCOUNT est 0)
                 |--Clustered Index Scan(OBJECT:([CocoJambo].[dbo].[Test].[PK__Test__3214EC277F60ED59]), WHERE:([CocoJambo].[dbo].[Test].[Col1]=(1)) ORDERED)

DROP INDEX dbo.Test.IN_Test_Col1
CREATE INDEX IN_Test_Col1_#_Col2 ... (acest index include va putea fi utilizat -seek- de către UPDATE)

-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
  |--Clustered Index Update(OBJECT:([CocoJambo].[dbo].[Test].[PK__Test__3214EC277F60ED59]), OBJECT:([CocoJambo].[dbo].[Test].[IN_Test_Col1_#_Col2]), SET:([CocoJambo].[dbo].[Test].[Col2] = RaiseIfNullUpdate([Expr1003])))
       |--Compute Scalar(DEFINE:([Expr1011]=[Expr1011]))
            |--Compute Scalar(DEFINE:([Expr1003]=[CocoJambo].[dbo].[Test].[Col2]*[@1], [Expr1011]=CASE WHEN CASE WHEN [CocoJambo].[dbo].[Test].[Col2] = [CocoJambo].[dbo].[Test].[Col2]*[@1] THEN (1) ELSE (0) END THEN (0) ELSE (1) END))
                 |--Top(ROWCOUNT est 0)
                      |--Index Seek(OBJECT:([CocoJambo].[dbo].[Test].[IN_Test_Col1_#_Col2]), SEEK:([CocoJambo].[dbo].[Test].[Col1]=CONVERT_IMPLICIT(int,[@2],0)) ORDERED FORWARD)

Index clustered -> [locked resourse hash]
ID          Col1        Col2        Col3        Col4 locked resourse hash
----------- ----------- ----------- ----------- ---- --------------------
1           1           11          111         A    (010086470766)
2           2           22          222         A    (020068e8b274)     <- WHERE Col1 = 2
3           2           33          333         A    (03000d8f0ecc)        WHERE Col1 = 2
4           2           44          444         A    (0400b4b7d951)        WHERE Col1 = 2
5           3           55          555         A    (0500d1d065e9)

IN_Test_Col1_#_Col2 -> [locked resourse hash]
ID          Col1        Col2        Col3        Col4 locked resourse hash
----------- ----------- ----------- ----------- ---- --------------------
1           1           11          111         A    (02006d47cbee)
2           2           22          222         A    (040060eff172)     <- WHERE Col1 = 2
3           2           33          333         A    (050005884dca)        WHERE Col1 = 2
4           2           44          444         A    (0600bcb09a57)        WHERE Col1 = 2
5           3           55          555         A    (080047d78c23)

index_id    name                        type_desc    is_primary_key
----------- --------------------------  ------------ --------------
1           PK__Test__3214EC277F60ED59  CLUSTERED    1
2           IN_Test_Col1_#_Col2         NONCLUSTERED 0

Lista cu paginile folosite de catre indecşii din tabela CocoJambo.dbo.Test ...
PageFID PagePID     IndexID     PageType IndexLevel
------- ----------- ----------- -------- ----------
1       79          1           10       NULL       <- PK__Test__3214EC277F60ED59  CLUSTERED
1       78          1           1        0          <- PK__Test__3214EC277F60ED59  CLUSTERED
1       90          2           10       NULL       <- IN_Test_Col1_#_Col2         NONCLUSTERED
1       89          2           2        0          <- IN_Test_Col1_#_Col2         NONCLUSTERED

Process 55 acquiring IX lock on OBJECT: 10:2105058535:0  (class bit2000000 ref1) result: OK

{Lock IU @ pagina 1:89 din indexul NonClst (vezi lista DBCC IND)}
Process 55 acquiring IU lock on PAGE: 10:1:89 (class bit0 ref1) result: OK

{Lock U @ cheia (040060eff172) din indexul NonClst (vezi IN_Test_Col1_#_Col2 -> [locked resourse hash])}
Process 55 acquiring U lock on KEY: 10:72057594038910976 (040060eff172) (class bit0 ref1) result: OK

{Lock IU @ pagina 1:78 din indexul Clst}
Process 55 acquiring IU lock on PAGE: 10:1:78 (class bit0 ref1) result: OK

{Lock U @ cheia (020068e8b274) din indexul Clst (vezi Index clustered -> [locked resourse hash]])}
Process 55 acquiring U lock on KEY: 10:72057594038779904 (020068e8b274) (class bit0 ref1) result: OK

{Conversie IU -> IX @ pagina 1:78 din indexul Clst}
Process 55 acquiring IX lock on PAGE: 10:1:78 (class bit2000000 ref0) result: OK

{Conversie U -> X @ cheia (020068e8b274) din indexul Clst}
Process 55 acquiring X lock on KEY: 10:72057594038779904 (020068e8b274) (class bit2000000 ref0) result: OK

{Conversie IU -> IX @ pagina 1:89 din indexul NonClst}
Process 55 acquiring IX lock on PAGE: 10:1:89 (class bit2000000 ref1) result: OK

{Conversie U -> X @ cheia (040060eff172) din indexul NonClst}
Process 55 acquiring X lock on KEY: 10:72057594038910976 (040060eff172) (class bit2000000 ref1) result: OK
Process 55 releasing lock reference on KEY: 10:72057594038910976 (040060eff172)
Process 55 releasing lock reference on PAGE: 10:1:89
Process 55 releasing lock reference on KEY: 10:72057594038779904 (020068e8b274)
Process 55 releasing lock reference on PAGE: 10:1:78
Process 55 releasing lock reference on KEY: 10:72057594038910976 (040060eff172)