Don’t Abuse the Use Statement

I ran across this article in Visual Studio Magazine that tracks the source of a missing object to an embedded use statement. It’s not wrong to do that, but maybe it should be! For admin scripts, testing, etc, USE is valuable, but in the dev world it’s far better to solve the problem by adding a synonym to make it all look like it’s local (or a view if you’re stuck on SQL 2000). Interestingly if you live in .Net the error would most likely not have happened. ADO.Net and connection pooling work together well, with the default behavior of a connection being to run sp_resetconnection which should restore the catalog to whatever it was when the connection first opened. It can be overridden, but it’s not obvious, and rarely worth doing except for super high volume situations.

For most of us we’ve long since gone past the point of thinking about Use, and that’s good. For beginners though, as in new to SQL Server beginners, it’s often confusing trying to understand context. In some way the UI fails to make clear that databases are the natural container in a way that Word docs seem to do naturally. It’s hard to remember the things we struggled with in the beginning, we attribute it to the learning curve (true), but we learn to make it work whether it’s clear or not.