For non-serialized does key lock lock more than one row?

For Sql 2000, for isolation levels not serialized, can a key lock
(especially created by an inserted row), involving a nonprimary key index
end up locking more than one row?
Thanks,
Randy Neall
"Randolph Neall" <randolphneall@.veracitycomputing.com> wrote in message
news:#GWEwT8EHHA.3780@.TK2MSFTNGP02.phx.gbl...
> For Sql 2000, for isolation levels not serialized, can a key lock
> (especially created by an inserted row), involving a nonprimary key index
> end up locking more than one row?
>
Since each key in a non-unique index may relate to multiple rows, a key lock
on a non-unique index typically impacts multiple rows, since The rows
themselves are not locked, but the key lock will be inconsistent with any
other transaction reading or locking that index key. So it may well block
other operations on other rows that share that index key.
David
|||Thanks, David.
Randy
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:OsJQ508EHHA.3780@.TK2MSFTNGP02.phx.gbl...[vbcol=seagreen]
>
> "Randolph Neall" <randolphneall@.veracitycomputing.com> wrote in message
> news:#GWEwT8EHHA.3780@.TK2MSFTNGP02.phx.gbl...
index
> Since each key in a non-unique index may relate to multiple rows, a key
lock
> on a non-unique index typically impacts multiple rows, since The rows
> themselves are not locked, but the key lock will be inconsistent with any
> other transaction reading or locking that index key. So it may well block
> other operations on other rows that share that index key.
> David
>

For non-serialized does key lock lock more than one row?

For Sql 2000, for isolation levels not serialized, can a key lock
(especially created by an inserted row), involving a nonprimary key index
end up locking more than one row?
Thanks,
Randy Neall"Randolph Neall" <randolphneall@.veracitycomputing.com> wrote in message
news:#GWEwT8EHHA.3780@.TK2MSFTNGP02.phx.gbl...
> For Sql 2000, for isolation levels not serialized, can a key lock
> (especially created by an inserted row), involving a nonprimary key index
> end up locking more than one row?
>
Since each key in a non-unique index may relate to multiple rows, a key lock
on a non-unique index typically impacts multiple rows, since The rows
themselves are not locked, but the key lock will be inconsistent with any
other transaction reading or locking that index key. So it may well block
other operations on other rows that share that index key.
David|||Thanks, David.
Randy
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:OsJQ508EHHA.3780@.TK2MSFTNGP02.phx.gbl...
>
> "Randolph Neall" <randolphneall@.veracitycomputing.com> wrote in message
> news:#GWEwT8EHHA.3780@.TK2MSFTNGP02.phx.gbl...
index[vbcol=seagreen]
> Since each key in a non-unique index may relate to multiple rows, a key
lock
> on a non-unique index typically impacts multiple rows, since The rows
> themselves are not locked, but the key lock will be inconsistent with any
> other transaction reading or locking that index key. So it may well block
> other operations on other rows that share that index key.
> David
>

For non-serialized does key lock lock more than one row?

For Sql 2000, for isolation levels not serialized, can a key lock
(especially created by an inserted row), involving a nonprimary key index
end up locking more than one row?
Thanks,
Randy Neall"Randolph Neall" <randolphneall@.veracitycomputing.com> wrote in message
news:#GWEwT8EHHA.3780@.TK2MSFTNGP02.phx.gbl...
> For Sql 2000, for isolation levels not serialized, can a key lock
> (especially created by an inserted row), involving a nonprimary key index
> end up locking more than one row?
>
Since each key in a non-unique index may relate to multiple rows, a key lock
on a non-unique index typically impacts multiple rows, since The rows
themselves are not locked, but the key lock will be inconsistent with any
other transaction reading or locking that index key. So it may well block
other operations on other rows that share that index key.
David|||Thanks, David.
Randy
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:OsJQ508EHHA.3780@.TK2MSFTNGP02.phx.gbl...
>
> "Randolph Neall" <randolphneall@.veracitycomputing.com> wrote in message
> news:#GWEwT8EHHA.3780@.TK2MSFTNGP02.phx.gbl...
> > For Sql 2000, for isolation levels not serialized, can a key lock
> > (especially created by an inserted row), involving a nonprimary key
index
> > end up locking more than one row?
> >
> Since each key in a non-unique index may relate to multiple rows, a key
lock
> on a non-unique index typically impacts multiple rows, since The rows
> themselves are not locked, but the key lock will be inconsistent with any
> other transaction reading or locking that index key. So it may well block
> other operations on other rows that share that index key.
> David
>

FOR and AFTER

Someone pls enlighten me...
I understand that FOR TRIGGERS would be executed concurrently as the table is being updated/inserted/deleted
&
AFTER TRIGGER would only be executed after the UPDATE/INSERT/DELETE operation has been completed...am i right?I never heard of FOR TRIGGERS. I only know about
BEFORE TRIGGER (not supported by MSSQL, as far as I know)
AFTER TRIGGER
INSTEAD OF TRIGGER

Where did you hear about FOR triggers ?|||ok sorry i didn't mean FOR triggers..

what i meant was ..whats the difference between the following 2.

ALTER TRIGGER SendMsgs ON asiapac702_test.dbo.tblCustServiceHistoryHdr FOR UPDATE
AS...

ALTER TRIGGER SendMsgs ON asiapac702_test.dbo.tblCustServiceHistoryHdr AFTER UPDATE
AS...|||Ok, now I get it.

As far as I can understand from the documentation, FOR and AFTER are synonyms, from a syntax point of view.

I did some testing, and found no differences between

create trigger tai_t on t FOR insert as begin select 1 end
create trigger tai_t2 on t AFTER insert as begin select 1 end

Probably this has something to do with being ANSI/ISO or whatever compliant.