Eh, there is less control in SQL Server over locking than there is in other RDBMSs, and it is infamous for escalating locks to the page or table level even when you ask for lower level locks. It's rare that it happens, but it's not unheard of. The fact that the system uses optimistic locking and there's no good equivalent to SELECT ... FOR UPDATE is also somewhat problematic.
It's greatly mitigated in 2005+ by using read committed row versioning (MVCC) and/or snapshot isolation, but those are database level options and you may need to specifically request the right isolation levels with your code. The biggest problem is that you have to remember to use the feature; it's just always on with Oracle from what I hear (I haven't used it since I was in school).
There's a mountain of documentation (and videos!) from Microsoft on all this. The greatest thing about SQL Server is the extremely high quality of the documentation. It's a joy to learn about compared to IBM's DB2 documentation (but then, anything is better than IBM documentation), and Books Online is a step ahead of Oracle, MySQL, and PostgreSQL.