Yeah, I've seen SINGLE_USER fail too. There is a lag, however tiny, between that GO statement and the next statement in the batch. It only takes one connection to muck you up. We usually use RESTRICTED_USER because only DBA's have it, so we're the only ones that we have to worry about.
According to BOL, setting the database to OFFLINE is "clean" which usually means that transactions are completed or rolled back prior to the change in state. I tried it out (not in production) and it worked pretty well, maybe I'll use this one instead from now on:
ALTER DATABASE [AdventureWorks]
SET OFFLINE
WITH ROLLBACK IMMEDIATE
GO
DROP DATABASE [AdventureWorks]
GO
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning