Article ID: 928907
Article Last Modified on 11/20/2007
APPLIES TO
- Microsoft SQL Server 2005 Standard Edition
- Microsoft SQL Server 2005 Standard X64 Edition
- Microsoft SQL Server 2005 Developer Edition
- Microsoft SQL Server 2005 Enterprise Edition
- Microsoft SQL Server 2005 Enterprise X64 Edition
- Microsoft SQL Server 2005 Enterprise Edition for Itanium-based Systems
INTRODUCTION
This article describes how to improve concurrency in a Microsoft SQL Server 2005 application by using built-in functions instead of by using system tables. An example of a built-in function is the OBJECT_ID function. Examples of system tables are the sysobjects system table and the sys.objects system table.
MORE INFORMATION
SQL Server 2005 uses views to access the underlying system tables. These views may select a plan that performs a table scan or a clustered index scan. The scan reduces concurrency when the views access the system table.
For example, you can use the sysobjects system table to drop a table as shown in the following Transact-SQL statement.
if exists (select * from sysobjects where name = 'tblTest1908')
drop table tblTest1908
If you do this, the drop-table process acquires a key lock. The drop-table process holds the key lock until the transaction is committed. This method reduces concurrency in the application.
Instead, you can use the following Transact-SQL statement.
if object_id('tblTest1908') is not null
drop table tblTest1908
This Transact-SQL statement uses the OBJECT_ID function to implement optimistic concurrency control when you drop the table.
Keywords: kbhowto kbinfo KB928907
