I’ve you’ve done much work with the system views (DMVs for example) then you’ve had to translate an object_id into a schemaname and objectname and vise versa. To do that you’ve either used the functions OBJECT_ID, OBJECT_SCHEMA_NAME, and OBJECT_NAME or sys.objects and sys.schemas.
So what’s the difference? Well, each version has its pros and cons.
Functions (OBJECT_NAME, OBJECT_ID, etc)
Pros
- Quick and easy. Very few characters, no join required.
- Usually (all I’ve looked at) can either be passed the database_id as a seperate parameter or the database name as part of a fully qualified object name. This means if you are querying sys.dm_exec_procedure_stats (for example) you can collect data across every database at once.
Cons
- Requires certain locks even in READ UNCOMMITTED (NOLOCK) so can be blocked/cause blocking.
Views (sys.objects, sys.schemas, etc)
Pros
- Follows the transaction isolation level of the session and/or you can change the isolation level for the specific view. So if you are using NOLOCK then the whole query will actually use NOLOCK.
- There is a lot of information available beyond just the name.
Cons
- The code is longer/requires more typing.
- These are specific to a single database. Unless you create a master view you are going to have a problem if your query hits data across multiple databases.
So which should you use? Well, like most things in the database world, it depends. Do you need to cross databases? Do you need to use NOLOCK for a diagnostic query? Do you need additional information available in one of the views that isn’t available in one of the helper functions?
In the end, both are useful, so learn both. Learn how to use them, learn the pros and the cons, and don’t get so stuck on one or the other that you forget to use the one that’s most helpful in your situation.
Filed under: Microsoft SQL Server, SQLServerPedia Syndication, System Functions and Stored Procedures, T-SQL Tagged: best practices, microsoft sql server, system functions, system views, T-SQL 
