Unexpected Locking in SQL Server 2005

I was working a little bit with SQL Server 2005 today and ran across an unexpected (at least to me) situation. From within the Management Studio, I opened up a table to view the contents.  Nothing complicated, although I did modify the default SQL slightly to reorder the results.  Then, in another query tab, I executed a script that did a DROP TRIGGER against the table that I had just opened. Problem is, the DROP statement just hung there. Waiting on a lock.  Specifically, waiting on exclusive access to the table. A lock that it couldn’t get because I had opened the table through Management Studio. Like I said, unexpected.

The solution was simple once I saw who was holding the lock. Close down the tab and everything was hunky-dory.  But the time I spent researching the problem is gone, never to be recovered. I also did a bit of digging into why this was so unexpected. It doesn’t appear to happen in Enterprise Manager. Simply opening a table and retrieve all of the rows does not establish a lock on the records. Doesn’t even appear to if you start to edit a particular record. Chalk it up to one more thing that is new and improved.