Reputation
31,468
Next tag badge:
484/400 score
71/80 answers
Badges
8 79 132
Newest
 Enlightened
Impact
~929k people reached

Oct
23
reviewed Approve Problem with shortest distance calculation in PostGIS
Oct
23
reviewed Reject What is lock escalation?
Oct
22
comment Error when UPDATE all rows
No that won't solve the problem it will just hide it. Whatever your trigger is supposed to be doing won't happen for arbitrary rows. You need to rewrite it to work against a set of rows. e.g. join onto the inserted table rather than doing an = seek on a scalar variable.
Oct
22
comment Error when UPDATE all rows
No. INSERTED is a pseudo table containing all rows affected by the update statement.
Oct
22
comment Error when UPDATE all rows
The error is coming from SET @ID = (SELECT ID FROM INSERTED). You need to rewrite (at least part of) the trigger to take into account that inserted can contain multiple (or zero) rows not just exactly one.
Oct
22
comment Error when UPDATE all rows
Probably your trigger assumes that inserted or deleted tables only has one row and is treating a select from one or other of them as a scalar. The line number presumably refers to a different line than the one you have identified. I can't remember if it includes the CREATE TRIGGER header.
Oct
22
reviewed Approve how to pass url value to index
Oct
22
reviewed Approve Why should pupbld.sql be run as SYSTEM (and not as SYS)?
Oct
22
revised Why does DELETE leave a lingering effect on performance?
Added results for all table types.
Oct
21
awarded  Custodian
Oct
21
revised Why does DELETE leave a lingering effect on performance?
added 139 characters in body
Oct
21
comment Disabling logging on certain tables
Related Connect Item Request
Oct
21
comment Why does DELETE leave a lingering effect on performance?
Well my theory is that it is the allocation of the 10,000+ pages which happens in a much more optimized way and seems to avoid some per page overhead. For smaller inserts I would expect any such difference to be less significant.
Oct
21
comment Why does DELETE leave a lingering effect on performance?
@RichardTheKiwi - The benefit of TRUNCATE over DELETE on its own would also argue for that. I would also rarely consider table variables for large numbers of rows.
Oct
21
revised Why does DELETE leave a lingering effect on performance?
added 1129 characters in body
Oct
21
answered Why does DELETE leave a lingering effect on performance?
Oct
20
reviewed Approve Partition guides and possible solutions?
Oct
20
reviewed Approve Storing application settings with different datatypes
Oct
20
comment Altering nullability of column in SQL Server
@a1ex07 the second example changes the length.
Oct
20
comment Fast XML, slow XML
@JonSeigel - You can also use Trace Flag 4130 if it is the same issue as per the Connect item. I am beginnining to doubt it though as in the issue there the plans are not the same. The slow one gets an unnecessary eager spool to the plan.