Posted by Erland Sommarskog on 11/29/06 22:50

gobwash@gmail.com (orders@bunchesofbooks.com) writes:
> Do aggregate functions (sum, count, min, max) inherently cause table
> locks?

Not as such.

> More concretely, would the following query typically result in a table
> lock?
>
> select sum(quantity) as total
> from products

Yes. So would "SELECT quanity FROM products". The fact there is an
aggregate has nothing to do with it. What matters is that you access
all rows.

There is one situation where the table would not be locked and that is
if there is a non-clustered index which includes quantity, not necessarily
as the first column. In that case SQL Server will scan the index instead.
I don't know exactly which locks SQL Server takes out in this case. It
appears reasonable that it would lock the index, but I am not sure.
--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Back to original message]