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.

Continue reading at SQLStudies.com

Continue reading on SQLStudies.com.