Posted by Gobwash on 11/29/06 23:56

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.se> wrote:
> gobw...@gmail.com (ord...@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 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

[Back to original message]