@PaulWhite That question confused me actually. Martin's comments made me more conscious (or maybe at all conscious) of the difference between atomicity and consistency, but I couldn't quite understand how that applied to the OP's situation

I think that subconsciously I made a further distinction between a statement's consistency and a transaction's visibility

and the issue was the latter, as the answer shows too

@AndriyM I think it's a question that should confuse many people, if they choose to think about it deeply enough. The underlying problem is a common confusion over what the terms in ACID actually mean. Sadly, the words have quite different meanings in a database than in more general programming.

In many contexts, "atomic" implies much more than "all or nothing completion".

In a database, the key word is "isolation".

At the heart of it, I think most people expect serializable semantics for their SQL statements.

Think hard about what number SELECT COUNT(*) FROM Table should return.

bit?

Oh, no sorry. A DateTime

@billinkc RONG!!! NVARCHAR(MAX)

Not what type, what number.

nvarchar? What are you, some kind of bleeding heart commie pinko liberal foreigner?

Damnit, Paul's gotta be all technical and stuff

@billinkc no, but i do like to consume twice the storage for half the rationale

@PaulWhite clearly that number is 42

No I can be silly too, just not right now :)

Very well, carry on with the seriousness. ;)

now that we've distracted from the conversation at hand ... back to cave, troll

Don't want to now :'(

while my optimizer gently weeps

SELECT 'Error' WHERE (SELECT COUNT(*) FROM Table) <> (SELECT COUNT(*) FROM Table)

@PaulWhite are those evaluated separately?

@swasheck It depends.

sunuva ... walked right into that

:-D

@PaulWhite on my default transaction isolation level?

Yes.

so in read uncommitted there's the real possibility of getting different counts

And in read committed, and in repeatable read.

And in snapshot, the numbers might be arbitrarily out of date.

Only serializable gives the semantics most people would intuitively assume.

(at least as these isolation levels are defined in SQL Server)

(confirmed: i suck at transaction isolation levels)

Everyone sucks at concurrency.

@PaulWhite my assumption was very different, though. my assumption was that the SELECT COUNT(*) would only be evaluated once. i think i misunderstand the optimizer more than the other topic

Yes it could do that in principle. It doesn't though, and that wasn't my point anyway.

The simple single COUNT(*) was possibly a better example.

At read uncommitted and read committed, it might count more rows than were ever in the table. At repeatable read, it might count less.

@PaulWhite clearly. but that was where my mind went first. nonetheless, the point is well taken.

@PaulWhite so here's where i show my "dumb" on concurrency. what about if it's done in an explicit transaction?

@swasheck No difference. Every statement is a transaction already.

Surprising huh.

@PaulWhite i think i knew that, but i hoped that there was some magic in the explicit tx

An explicit transaction just groups multiple statements into a unit that must complete or fail as a whole.

sounds like my youth baseball team

mostly the fail part

:)

@PaulWhite Should the issue manifest with the default isolation that a standalone statement runs in?

thanks @PaulWhite, this have been very educational

Can't reproduce it using SQL Fiddle, trying to access remotely my PC at work...

Or how much does it depend if there are 100 or 100,000 rows in the table?

@AndriyM You need a reasonable level of concurrent modifications to the table, but yes, it occurs at the default 'read committed' isolation level. 'Default' includes not using RCSI.

Has anyone ever used Zabbix for monitoring?

It's logically possible with any number of rows in the table. Whether you see it depends on a number of factors. Larger tables make it easier.

@mmarie Not me.

@mmarie I'm going to be at my boy's basketball game this evening so give Tristan my best

@AndriyM The general point is that rows can appear, move, or disappear while either counting operation is in progress.

@PaulWhite Er, so there's no way that statement could return the row if there are no concurrent reads/writes? I guess not... (Sorry, @swasheck, but I'm dumber than you.)

@AndriyM If the table contents are static, no. The count won't change.

@mmarie sounds very elmer fudd-y

@AndriyM negative

That is the topic of our SQL Server User Group meeting tonight. Zabbix: The Free Monitoring Solution

I really want someone to try using Wascally Wabbits for monitoring.

Duck season!

And with that, I bid you all adieu

Adieu.

@swasheck I would prove that if I wasn't so dumb. So yeah, you win.

We're all ignorant.

@PaulWhite, that is very interesting; thought it would be safe at an isolation "higher" than read uncommited

@PaulWhite, is there any articles that highlight the cases in which that might happen?

@Gonsalu Most people would assume the same thing.

@Gonsalu It can happen almost anywhere. Two simple examples: one two

It's much more general though.

@PaulWhite, thanks for the links

I can imagine this happening in huge tables where a scan of the table takes quite a while

@PaulWhite, do you keep a knowledge base of this links or something?

@Gonsalu It depends on a number of factors. It's definitely not limited to scans of large tables.

No, I just remember them on demand :)

@PaulWhite, I'm impressed that any time I ask for some references, you always have them handy haha

I use Google extensively :-)

Alex K has written a number of times about concurrency issues.

:)

Ultimately, people write single-threaded SQL.

I'm lucky that I work mostly with DW scenarios

I imagine I would've encountered these cases by now in OLTP scenarios

If by DW you mean very few concurrent modifications to the data, then yes, I guess so.

Yes; something like daily refreshes or so

In that case, yes, you're essentially dealing with a read-only database.

"Both may fail very frequently. MERGE is the king"

oh boy haha

Yes it's an old post :)

@PaulWhite Even though it's an old one, it's still increased my understanding of the matter a tiny bit.

Am I missing something or he saying that he was mentally preparing to answer before I wrote my question?

@FreshPrinceOfSO As I understand it, he says that he was in the middle of writing his own question similar to yours when he saw your question pop up in the duplicates suggested by the system. Following that he went to your question and wrote his idea as an answer instead.

@AndriyM okay that's what I thought. I'm curious at what point in the 6 months he had the idea

@AndriyM honestly --- i'm more impressed with you than i am with with me

@swasheck That's very kind of you, thanks. Apart from that, I honestly would never expect someone as modest as you to seek to be impressed by himself. So, that statement proves nothing for me, sorry.

I could still be dumber

But hey, @Paul says we are just ignorant. I'm inclined to consent to that. :)