I would like to understand whether the following, very simple select statement would take any locks
It is a common misconception that a
SELECT query running at the default
READ COMMITTED transaction isolation level will always take shared locks to prevent dirty reads.
SQL Server can avoid taking shared row-level locks when there is no danger of reading uncommitted data without them (though higher-level Intent-Shared (IS) locks are still taken).
Even if shared row locks are taken (perhaps because another concurrent transaction has modified the page the row is on) they can be released long before the
SELECT statement completes.
In most cases, the row is 'unlocked' just before the server processes the next row. There are circumstances in which shared locks taken at the default isolation level are held to the end of the current statement, but not to the end of the transaction.
Overriding the current isolation level with the
NOLOCK table hint is almost always a bad idea.
Locking is an implementation detail. SQL Server takes locks when necessary to ensure it meets the semantic guarantees provided by the current isolation level. There are certainly times where it is useful to know a little bit about why locks are taken, but attempting to predict them is very often counter-productive.
SQL Server provides a wide variety of isolation levels; choose the one that provides the guarantees and behaviours your data consumers need.