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)
- 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.
- Requires certain locks even in READ UNCOMMITTED (NOLOCK) so can be blocked/cause blocking.
Views (sys.objects, sys.schemas, etc)
- 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.
- 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.