If Not Exists?

Curious about the above:
If there is a query that says
If not exists(select * from table)
versus one that says
If not exists(select * from table with(NoLOCK))
Is the second statement more correct than the first (considering that there
may be hundreds of hits to that table in a very short space of time -
possible 100's per second)?
Or is the second statement truly no different than the first as the
statement would discover that the record had a count > 0 and thus validate
the "if not exists" statment as false?
Regards,
Jamie
On Mon, 30 Jul 2007 11:02:05 -0700, thejamie wrote:

>Curious about the above:
>If there is a query that says
>If not exists(select * from table)
>versus one that says
>If not exists(select * from table with(NoLOCK))
>Is the second statement more correct than the first (considering that there
>may be hundreds of hits to that table in a very short space of time -
>possible 100's per second)?
Hi thejamie,
The seconds statement is faster, but LESS correct. If the table has no
rows, one user proceeds to insert a row that violates a constraint (so
that the insert will be rolled back) and another user runs the second
version of the query, it will "see" the row inserted by the first user,
even though that row logically never existed. And the revere goes to: if
one user runs a DELETE without WHERE clause that gets rolled back later,
the query with nolock hint will temporarily see no rows, even though
they logically were never gone.
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
|||Yes. LESS correct, unfortunately it is the transaction table - roughly 2 or
three million of these a week - it is hard to touch this table without
conflicts - we sort of gave up and now use the with(nolock) when touching the
table. The issue came up as this particular call to the transactions table
brought up the question of what does the "EXISTS" keyword actually do when it
accesses a table.
For example, does it open the PK index to see if it has a count and shut it
back down again?
or possibly:
Opens the table like a select top 1 call might do,
or worst possibly scenario - "EXISTS" opens the entire index and scans
through from beginning to end to determine the condition as true or false.
What is actually written is " IF NOT EXISTS " which flowed more logically -
from the context - if the table does not exist - which at about 2:30 AM in
the morning when it gets archived to the datawarehouse - it essentially does
not exist from that point until about 5 am when the warehouse starts up -
question remains:
Is the table accessing all the records with the "Exists" keyword, or does it
try to scan the entire index... etc?
Regards,
Jamie
"Hugo Kornelis" wrote:

> On Mon, 30 Jul 2007 11:02:05 -0700, thejamie wrote:
>
> Hi thejamie,
> The seconds statement is faster, but LESS correct. If the table has no
> rows, one user proceeds to insert a row that violates a constraint (so
> that the insert will be rolled back) and another user runs the second
> version of the query, it will "see" the row inserted by the first user,
> even though that row logically never existed. And the revere goes to: if
> one user runs a DELETE without WHERE clause that gets rolled back later,
> the query with nolock hint will temporarily see no rows, even though
> they logically were never gone.
> --
> Hugo Kornelis, SQL Server MVP
> My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
>
|||On Tue, 31 Jul 2007 06:02:05 -0700, thejamie wrote:

>Yes. LESS correct, unfortunately it is the transaction table - roughly 2 or
>three million of these a week - it is hard to touch this table without
>conflicts - we sort of gave up and now use the with(nolock) when touching the
>table. The issue came up as this particular call to the transactions table
>brought up the question of what does the "EXISTS" keyword actually do when it
>accesses a table.
>For example, does it open the PK index to see if it has a count and shut it
>back down again?
>or possibly:
>Opens the table like a select top 1 call might do,
>or worst possibly scenario - "EXISTS" opens the entire index and scans
>through from beginning to end to determine the condition as true or false.
>What is actually written is " IF NOT EXISTS " which flowed more logically -
>from the context - if the table does not exist - which at about 2:30 AM in
>the morning when it gets archived to the datawarehouse - it essentially does
>not exist from that point until about 5 am when the warehouse starts up -
>question remains:
>Is the table accessing all the records with the "Exists" keyword, or does it
>try to scan the entire index... etc?
Hi thejamie,
Both EXISTS and NOT EXISTS will stop processing as soon as the first row
matching the WHERE clause (if any) is found.
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
|||On Jul 30, 1:02 pm, thejamie <theja...@.discussions.microsoft.com>
wrote:
> Curious about the above:
> If there is a query that says
> If not exists(select * from table)
> versus one that says
> If not exists(select * from table with(NoLOCK))
> Is the second statement more correct than the first (considering that there
> may be hundreds of hits to that table in a very short space of time -
> possible 100's per second)?
> Or is the second statement truly no different than the first as the
> statement would discover that the record had a count > 0 and thus validate
> the "if not exists" statment as false?
> --
> Regards,
> Jamie
Suggestion:
Try using Select 1 instead of Select * in this scenario. That is an
old db tuning trick. Let's it use whatever index fits the where
clause and doesn't have to go to the table, retrieve and subsequently
discard all of the data in the found rows.
|||On Wed, 01 Aug 2007 17:12:50 -0700, karlag92 wrote:
[vbcol=seagreen]
>On Jul 30, 1:02 pm, thejamie <theja...@.discussions.microsoft.com>
>wrote:
(snip)
>Suggestion:
>Try using Select 1 instead of Select * in this scenario. That is an
>old db tuning trick.
Hi karlag92,
The trick is, in fact, so old that it doesn't apply anymore. And it
actually never has applied for SQL Server. It was, IIRC, either Oracle
or DB2 that erroneously decided to expand the * to a column list even if
it was part of a [NOT] EXISTS subquery - and that has of course long be
fixed.
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis