How to lock only the lines in the result set ?

For a SELECT on a given table, how to lock only the lines in the result set,
and not all the lines used in the query (I use SET ROWCOUNT to limit the
number of results) ?
I tryed ROWLOCK but it locks all the lines matching the query, even the one
that are discarded by rowcount.
Any workaround for SQL2000 ?
Same problem in 2005 ?Why do you want to do that. You explain your problem so that we will be able
to help you out
--
Regards
R.D
--Knowledge gets doubled when shared
"SoftLion" wrote:

> For a SELECT on a given table, how to lock only the lines in the result se
t,
> and not all the lines used in the query (I use SET ROWCOUNT to limit the
> number of results) ?
> I tryed ROWLOCK but it locks all the lines matching the query, even the on
e
> that are discarded by rowcount.
> Any workaround for SQL2000 ?
> Same problem in 2005 ?
>
>|||> Why do you want to do that. You explain your problem so that we will be
> able
> to help you out
MyTable( id int, dateAdded datetime )
CREATE PROCEDURE test
as
SET ROWCOUNT 100
SELECT
id
FROM
MyTable WITH (UPDLOCK,ROWLOCK,READPAST)
ORDER BY
dateAdded asc
GO
** In a query:
begin tran
exec test
(100 results retreived)
** In another query
begin tran
exec test
(0 results retreived, because READPAST jumped over ALL rows of MyTable,
because ALL rows are locked, because of the ORDER BY)|||If you use a TOP 100 and have an index (preferably a clustered) on DateAdded
it should not block the entire table, just the first 100.
Andrew J. Kelly SQL MVP
"SoftLion" <nospam@.replyToGroup.com> wrote in message
news:%23Gw7w%2372FHA.3292@.tk2msftngp13.phx.gbl...
> MyTable( id int, dateAdded datetime )
> CREATE PROCEDURE test
> as
> SET ROWCOUNT 100
> SELECT
> id
> FROM
> MyTable WITH (UPDLOCK,ROWLOCK,READPAST)
> ORDER BY
> dateAdded asc
> GO
>
> ** In a query:
> begin tran
> exec test
> (100 results retreived)
> ** In another query
> begin tran
> exec test
> (0 results retreived, because READPAST jumped over ALL rows of MyTable,
> because ALL rows are locked, because of the ORDER BY)
>|||> If you use a TOP 100 and have an index (preferably a clustered) on
> DateAdded it should not block the entire table, just the first 100.
I can't use a top 100 because you SQL Server doesn't accept 'select top
@.maxLines' queries.
That's why I'm using a SET ROWCOUNT.
I will try adding a non-clustered index on dateAdded as you suggest (I
already have a clusted index on another more important column).|||> I can't use a top 100 because you SQL Server doesn't accept 'select top
> @.maxLines' queries.
> That's why I'm using a SET ROWCOUNT.
SQL2005 does support a variable on TOP by the way.

> I will try adding a non-clustered index on dateAdded as you suggest (I
> already have a clusted index on another more important column).
Having a clustered index does not render other nonclustered indexes useless.
They supplement each other.
Andrew J. Kelly SQL MVP
"SoftLion" <nospam@.replyToGroup.com> wrote in message
news:Ofy7WL43FHA.632@.TK2MSFTNGP10.phx.gbl...
>
>