Presents your 
SQL SERVER E-NEWSLETTER for November 12, 2002

<------------------------------------------->

SPECIFY LOCKING WITH SP_INDEXOPTION

The system procedure, sp_indexoption, is used to turn off or on
page-level and/or row-level locking. Under normal operation, SQL Server
automatically makes choices of page-, row-, or table-level locking. It 
is not necessary to set these options manually. However, sp_indexoption is 
provided for expert users who know with certainty that a particular type of 
lock is always appropriate.

The system procedure takes three parameters. The first parameter,
@IndexNamePattern, is a qualified index or table name. If only the 
table name is used and not the qualified index name, all indexes are 
affected.

The second parameter, @OptionName, can be one of the following four
options:

* AllowRowLocks
* DisAllowRowLocks
* AllowPageLocks
* DisAllowPageLocks

The third parameter, @OptionValue, specifies whether the setting is:

* Enabled--True, On, 1
* Disabled--False, Off, 0

You can use the metadata function, INDEXPROPERTY, to find if the options
of sp_indexoption system procedure have been set. Following is sample
script to illustrate the use of this function:

USE NorthWind
GO
-- 0=Page locking is allowed.
SELECT INDEXPROPERTY (
OBJECT_ID('Orders'),'PK_Orders','IsPageLockDisallowed')
-- 0=Row locking is allowed.
SELECT INDEXPROPERTY
(OBJECT_ID('Orders'),'PK_Orders','IsRowLockDisallowed')
GO

Please use the sp_indexoption system procedure with caution. You can
disallow the more granular locking mechanisms on a database causing the
locks to escalate to less granular locking mechanisms as in page-level and
table-level locking. Depending on the size of the table and type of
processing on the table, this could cause significant blocking within 
the database.

Following is a sample script used to demonstrate the use of the
sp_indexoption:

USE NorthWind
GO
SET NOCOUNT ON
GO
EXEC sp_indexoption @IndexNamePattern = 'Orders', @OptionName =
'AllowRowLocks', @OptionValue = 'no'
GO
SELECT 'Orders',
   CASE
   INDEXPROPERTY
(OBJECT_ID('Orders'),'CustomerID','IsRowLockDisallowed')
   WHEN 1 THEN 'Cannot Row Lock  '
   WHEN 0 THEN 'Can Row Lock  '
   ELSE 'Unknown'
   END 'RowLock',
   CASE
   INDEXPROPERTY 
(OBJECT_ID('Orders'),'PK_Orders','IsRowLockDisallowed')
   WHEN 1 THEN 'Cannot Row Lock  '
   WHEN 0 THEN 'Can Row Lock  '
   ELSE 'Unknown'
   END 'RowLock'
GO
EXEC sp_indexoption  @IndexNamePattern = 'Orders.CustomerID',
@OptionName = 'AllowRowLocks', @OptionValue = 'yes'
GO
SELECT 'Orders',
   CASE
   INDEXPROPERTY
(OBJECT_ID('Orders'),'CustomerID','IsRowLockDisallowed')
   WHEN 1 THEN 'Cannot Row Lock  '
   WHEN 0 THEN 'Can Row Lock  '
   ELSE 'Unknown'
   END 'RowLock',
   CASE
   INDEXPROPERTY 
(OBJECT_ID('Orders'),'PK_Orders','IsRowLockDisallowed')
   WHEN 1 THEN 'Cannot Row Lock  '
   WHEN 0 THEN 'Can Row Lock  '
   ELSE 'Unknown'
   END 'RowLock'
GO

----------------------------------------