Opinions expressed on this site are solely those of Kendra Little of Catalyze SQL, LLC. Content policy: Short excerpts of blog posts (3 sentences) may be republished, but longer excerpts and artwork cannot be shared without explicit permission.
on November 7, 2013
This is a super old command, but it still comes in handy when working with SQL Server.
Want to know your default isolation level in the current database? Run this. (If optimistic locking is turned on in your current database context, your default will be “read committed snapshot”)
Want to know what your ANSI settings are, or how arithabort is set? (Those settings can impact your query results, and determine whether you can successfully use a filtered index, an indexed computed column, or an indexed view.) DBCC USEROPTIONS helps out!
The biggest limitation: this tells you the settings for your current session– but not for anyone else’s session. That might help you figure out why something is slow in the application and fast in SSMS, but you’ve still got to do some legwork to figure out what the other session’s settings are. (Perhaps by using sys.dm_exec_requests?)
