Aggregate functions and locking

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,
Scottgobwash@.gmail.com (orders@.bunchesofbooks.com) writes:

Quote:

Originally Posted by

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

Not as such.

Quote:

Originally Posted by

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/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||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...@.sommarskog.sewrote:

Quote:

Originally Posted by

gobw...@.gmail.com (ord...@.bunchesofbooks.com) writes:

Quote:

Originally Posted by

Do aggregate functions (sum, count, min, max) inherently cause table
locks?Not as such.

>

Quote:

Originally Posted by

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

>

Quote:

Originally Posted by

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...@.sommarskog.se
>
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

|||Gobwash (gobwash@.gmail.com) writes:

Quote:

Originally Posted by

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@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx