SQL Server lock

I was asked today:What is the minimum lock used by sqlserver to lock a row?
Answer: Row-Level Lock

Check the document and obtain the lock information. (use the syslockinfo table of sql2k, and use the SYS. dm_tran_locks table of sql2k5 ):

Resource Type:
1   = Null Resource (not used)
2   = Database
3   = File
4   = Index
5   = Table
6   = Page
7   = Key
8   = Zone
9   = RID (row ID)
10   = Applications

OK. Now let's take a look at what is the minimum lock that is locked to a row.

1 D: \ Program Files \ Microsoft SQL Server \ 90 \ Tools \ binn > Osql - USA - Pdba123 - Sdragon
2 1 >   Create   Table T_pk (t_id Int   Primary   Key , T_desc Varchar ( 50 ));
3 2 >   Create   Table T_heap (t_id Int , T_desc Varchar ( 50 ));
4 3 >   Go
5 1 >   Insert   Into T_pk Values ( 1 , ' Welcome ' );
6 2 >   Insert   Into T_pk Values ( 2 , ' Nice to meet you ' );
7 3 >   Insert   Into T_pk Values ( 3 , ' Wall Street, stock ' );
8 4 >   Go
9 ( 1 Rows affected)
10 ( 1 Rows affected)
11 ( 1 Rows affected)
12 1 >   Insert   Into T_heap Select   *   From T_pk;
13 2 >   Go
14 ( 3 Rows affected)
15 1 >   Begin   Tran
16 2 >   Update T_pk Set T_desc =   ' Have a nice day '   Where T_id =   3 ;
17 3 >   Go
18 ( 1 Rows affected)
19

Now, I update the t_pk table with the primary key to see what SQL Server locks:

Here, sqlserver adds the IX lock to the t_pk table and the corresponding page, and adds the X lock to the updated row.

Now, try updating the heap table:

1 >   Rollback ;
2 >   Go
1 >   Begin   Tran
2 >   Update T_heap Set T_desc =   ' Have a nice day '   Where T_id =   3 ;
3 >   Go

Check what SQL Server locks the heap table:

Sqlserver 2005 is very clever and only locks one row (I remember that sql2k locks a page on the heap table and will test it again when I find sql2k ).

The locks in these two tests, one is the key and the other is the RID, basically in line with what I mean by row-level locks. But if it is really strong, my answer seems to be insufficient ,:)