Using the OBJECT_ID function to improve concurrency

If you are in the habit of querying system tables (like sysobjects) in your TSQL code, you might want to switch to using the built-in OBJECT_ID function, like you see below.

— Bad way to check for the existence of an object
— Querying sysobjects can place a key lock, which reduces concurrency
IF EXISTS (SELECT * FROM sysobjects WHERE [NAME] = ‘tblTest’)
    DROP TABLE tblTest

— Better way to check for the existence of an object (this won’t work for DDL triggers)
IF OBJECT_ID(‘tblTest’) IS NOT NULL
    DROP TABLE tblTest

Technorati Tag: SQL Server

This entry was posted in SQL Server 2005. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s