SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 

sys.objects VS helper functions (OBJECT_ID, OBJECT_NAME, etc)

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

 


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

SQLStudies

My name is Kenneth Fisher and I am Senior DBA for a large (multi-national) insurance company. I have been working with databases for over 20 years starting with Clarion and Foxpro. I’ve been working with SQL Server for 12 years but have only really started “studying” the subject for the last 3. I don’t have any real "specialities" but I enjoy trouble shooting and teaching. Thus far I’ve earned by MCITP Database Administrator 2008, MCTS Database Administrator 2005, and MCTS Database Developer 2008. I’m currently studying for my MCITP Database Developer 2008 and should start in on the 2012 exams next year. My blog is at www.sqlstudies.com.

Comments

Leave a comment on the original post [sqlstudies.com, opens in a new window]

Loading comments...