Aggregate functions and locking

[gobwash@gmail.com]

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

[Erland Sommarskog]

Re: Aggregate functions and locking

gobwash@gmail.c om (orders@bunches ofbooks.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@sommarsk og.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

[Gobwash]

Re: Aggregate functions and locking

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

On Nov 29, 4:50 pm, Erland Sommarskog <esq...@sommars kog.sewrote:

gobw...@gmail.c om (ord...@bunches ofbooks.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 productsYes. 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, esq...@sommarsk og.se
>
Books Online for SQL Server 2005 athttp://www.microsoft.c om/technet/prodtechnol/sql/2005/downloads/books...
Books Online for SQL Server 2000 athttp://www.microsoft.c om/sql/prodinfo/previousversion s/books.mspx

[Erland Sommarskog]

Re: Aggregate functions and locking

Gobwash (gobwash@gmail. com) writes:

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.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.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