Aggregate functions and locking

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

gobwash@gmail.com - 29 Nov 2006 21:18 GMT

Do aggregate functions (sum, count, min, max) inherently cause table
locks?

More concretely, would the following query typically result in a table
lock?

select sum(quantity) as total
from products

Thanks,
Scott

Reply to this Message

Erland Sommarskog - 29 Nov 2006 22:50 GMT

> 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.

Signature

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

Reply to this Message

Gobwash - 29 Nov 2006 23:56 GMT

Thanks for the fast response. I just threw that out as a sample query,
but if a where clause were present, what behavior should be expected?

Scott

> gobw...@gmail.com (ord...@bunchesofbooks.com) writes:
> > Do aggregate functions (sum, count, min, max) inherently cause table
[quoted text clipped - 18 lines]
> Books Online for SQL Server 2005 athttp://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books...
> Books Online for SQL Server 2000 athttp://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

Reply to this Message

Erland Sommarskog - 30 Nov 2006 08:12 GMT

> Thanks for the fast response. I just threw that out as a sample query,
> but if a where clause were present, what behavior should be expected?

It depends on the WHERE clause. If there is an index that can be used to
seek the rows, SQL Server will take out share locks. If there is no useful
index, so that the table will be scanned, there will be a table lock.

Again, aggregate or not has nothing to do with it.

Signature

Reply to this Message