Thanks to Thava for the question. It usually makes a good question to point out those areas in SQL Server where the actual behavior doesn't match the expected behavior.

(Edited to insert word "Server".)