And now we get an empty resultset, as expected. The real clincher was the filter on
t.[dbid], but the fact that
OBJECT_NAME() can quietly produce a false positive based on database context makes me that much less willing to use it.
There are other cases where this is not a problem that needs to be solved - consider functions like
DB_NAME()... how often would you expect to need read uncommitted to use these functions? After all, it's not like you can run
ALTER DATABASE in a transaction, or you'd want dirty data during
CREATE DATABASE. And as it turns out, the database-related metadata functions don't seem to be affected at all by isolation - I created a DDL trigger that just ran a
WAITFOR and then rolled back, and while that was running, queries against
sys.databases were blocked (unless I used read uncommitted), but
DB_NAME() returned immediately. Another case is
ALTER LOGIN -
SUSER_SNAME() is blocked even under read uncommitted, but so are queries against
sys.server_principals, so there is no *additional* problem caused by using the function.
So, not ALL metadata helper functions are bad. These are the main ones where I try to write joins instead, primarily to allow read uncommitted *if* it is required at some point. As much as it pains me to write all that code instead of the much more convenient shorthand, using the long-way joins make my code much more consistent (even if a bit more verbose), and when I'm writing code that other people will use, ensures they will not be blocked when otherwise operating under read uncommitted:
Another bonus is that accessing the catalog views works across databases and across linked servers without problem (you just need the (server and) database prefix), whereas some of the metadata functions have some real stumbling blocks. As demonstrated above, you can say
OBJECT_ID(N'database.dbo.name'), but how do you do the inverse (
OBJECT_NAME(<what goes here?>))? If you somehow know the
object_id, you can use the optional parameter for
database_id, and resolve that using yet another metadata function (e.g.
OBJECT_NAME([object_id], DB_ID(N'database'))). But this doesn't work across servers, and not all metadata functions have this optional parameter. So again, like many bad habits, one of the big payoffs can be consistency - where you are always using the joins, instead of only using them when you have to because your "preferred" method works in fewer scenarios.
If you never use read uncommitted when accessing metadata, and are okay with blocking or being blocked, and don't mind having different kinds of hoops to jump through depending on which metadata you're accessing from where, this post isn't for you.
As always, "it depends"
There are some cases where a helper function is the only practical way to achieve the goal. For example, if you want to determine whether a #temp table exists in your session, you do this:
IF OBJECT_ID(N'tempdb.dbo.#tablename') IS NOT NULL
You can't use
tempdb.sys.objects directly, because in that catalog view, the table name is actually something like:
You can't just look for a table named with the pattern
N'#tablename%', because then you'd also include similar temp tables from other users' sessions. You could try some of the methods in Dude, who owns that #temp table?, but these are very heavy-handed for this purpose, and probably still not fool-proof.
Of course, when checking if a #temp table exists, my primary objection to the helper functions is largely irrelevant anyway - you'd have a hard time blocking yourself inadvertently in a self-contained session due to mismatched isolation semantics.