Microsoft KB Archive/928907

= How to improve concurrency in a SQL Server 2005 application by using built-in functions =

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

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.