EXCEPT
-
Comments posted to this topic are about the item EXCEPT
-
I ran the query and see zero rows only, please verify your answer.
-
For debug, I used this query to return all rows inserted or changed in all open transaction.
How to reproduce the scenario:
Start a transaction in one session, insert or modify data in the table Sorder. In another session, run the query of the example, you'll see all records inserted or modified. To see deleted record you should move the WITH(NOLOCK) clause to the second query.
- Palani K wrote:
I ran the query and see zero rows only, please verify your answer.
If the query returns no row, it means that there are not uncommitted data (inserted or modified).
-
I would argue that in principle the explanation is correct. However, in the example given, this would result in zero rows, unless a very specific set of conditions are met. With the (NOLOCK) hint, the first half of the query (the "LEFT" part) might return rows that have been inserted or updated, but not yet committed, whereas these would cause the second half (the "RIGHT" part) to block. If the transaction owning the changed rows is rolled back, the second SELECT would not return these rows, and as a consequence, the EXCEPT would return the "ghost" rows. But this will probably depend on the ISOLATON_LEVEL, and also on how this actually gets resolved in SQL Server. If the second SELECT blocks due to open transactions, will that cause the first SELECT to not resolve which rows are returned until the locks are released?
I don't know, and this just enforces the old adage "Don't use (NOLOCK)!"
The bit about the second session that modifies the data in the table wasn't given in the question, so to bring that up later is a bit silly, in my humble opinion.
Just because you're right doesn't mean everybody else is wrong. -
invalid question - and adding rules afterwards to explain why your desired good answer is right is really not a good thing.
and if there are locking transactions then the query would hold out until such transactions are finished - unless you (again not mentioned on the question) were running with a database with RCSI enabled.
-
A very valid question I thought, but one that's more about NOLOCK than EXCEPT, and the explanation should have included the comments later added.
-
Of course the isolation level of the database should be as in the image
- Carlo Romagnano wrote:
Of course the isolation level of the database should be as in the image
Of course! That's obviously clear from the question😋
Just because you're right doesn't mean everybody else is wrong. -
*** sorry for the spoilers; but my comment didn't make sense to me without them ***
I debated with myself between "Zero records" and the more comprehensive "Returns distinct rows from the left input query that aren't output by the right input query".
I chose zero thinking this was a likely trick question and I just wasn't seeing the trick, so the thinking went "the comprehensive answer must be the trick here"... no (doh! next time stay in the facts not trying to get in the questioners head).
Technically both answers are correct, the answer marked as correct is more often correct than the zero records answer because of all the what if conditions that could apply that would invalidate "Zero records" as an answer. However the question itself didn't specify that any of the what if's applied. The NOLOCK hint could have lead to an assumption but that's still a what if that wasn't specified. IMHO this could have used a bit more detail, a sample data set and scenario explanation that depicts changes by an open INSERT/UPDATE while the question query is running or a sample with some duplication to highlight the distinct aspect of EXCEPT etc.
I like the trickery and misdirection in the question, keeps us on our toes and thinking, so not trying to be discouraging. Just pointing out that given the information we had available both "Zero records" and "Returns distinct rows from the left input query that aren't output by the right input query" are correct. One is more often correct but both are correct given the question content.
Thanks for coming up with and contributing a QoD; keep it up, it's helpful.
-
- Jason- wrote:
*** sorry for the spoilers; but my comment didn't make sense to me without them ***
I debated with myself between "Zero records" and the more comprehensive "Returns distinct rows from the left input query that aren't output by the right input query".
I chose zero thinking this was a likely trick question and I just wasn't seeing the trick, so the thinking went "the comprehensive answer must be the trick here"... no (doh! next time stay in the facts not trying to get in the questioners head).
Technically both answers are correct, the answer marked as correct is more often correct than the zero records answer because of all the what if conditions that could apply that would invalidate "Zero records" as an answer. However the question itself didn't specify that any of the what if's applied. The NOLOCK hint could have lead to an assumption but that's still a what if that wasn't specified. IMHO this could have used a bit more detail, a sample data set and scenario explanation that depicts changes by an open INSERT/UPDATE while the question query is running or a sample with some duplication to highlight the distinct aspect of EXCEPT etc.
I like the trickery and misdirection in the question, keeps us on our toes and thinking, so not trying to be discouraging. Just pointing out that given the information we had available both "Zero records" and "Returns distinct rows from the left input query that aren't output by the right input query" are correct. One is more often correct but both are correct given the question content.
Thanks for coming up with and contributing a QoD; keep it up, it's helpful.
No, "Zero records" is not correct as answer. You should consider any possible scenario.
It's the same to guess the order of rows of the following query: "SELECT 1 UNION SELECT 2", you can run it 1000 times it returns 1,2; but without the ORDER BY CLAUSE the result is unpredictable (it's a time bomb).
-
if zero records is not a valid answer then kindly explain why the following code returns zero records and why, according to you, it SHOULD return something.
if object_id('dbo.Sorders') is not null
drop table dbo.Sorders;
create table dbo.Sorders
( order_dat date
, description varchar(200)
)
create clustered index CI_Sorders on dbo.Sorders
(order_dat
)
insert into dbo.Sorders
(order_dat
, description
)
values (convert(date, '20220503', 112), 'row 1')
, (convert(date, '20220503', 112), 'row 2')
, (convert(date, '20220503', 112), 'row 3')
select *
from Sorders with (nolock)
where order_dat >= '20220503'
except
select *
from Sorders
where order_dat >= '20220503' - Carlo Romagnano wrote:Jason- wrote:
*** sorry for the spoilers; but my comment didn't make sense to me without them ***
I debated with myself between "Zero re ...
No, "Zero records" is not correct as answer. You should consider any possible scenario. It's the same to guess the order of rows of the following query: "SELECT 1 UNION SELECT 2", you can run it 1000 times it returns 1,2; but without the ORDER BY CLAUSE the result is unpredictable (it's a time bomb).
;TLDR I guess I'm not suggesting that "Zero records" is correct (it is the word I used, incorrectly... irony :-), just reasonable to assume it's correct given the details that were available. Important lesson depicted by this QoD and also reinforcing, slowing down and paying attention to detail typically pays off; good job.
Without more detail in the question that specifies scenarios, Zero records is, or more accurately is likely to be, a valid answer. I stated there are what if's that would invalidate Zero records but those what if's were not specified in the question so it is a reasonable answer... it is still the wrong answer in some scenarios, but it is reasonable. [Just to be clear, "Returns distinct rows from the left input query that aren't output by the right input query" is always correct while "Zero records" is only sometimes correct... I get that]
We all know in real life that we don't get all the info up front and some of the details we do get can be subtly misleading, like a QoD labeled as being about EXCEPT when it's really about EXCEPT and optimistic locking. That's the reason I think this is a good question; it is depicting a real life scenario where we don't start with all the details or the same understandings and discussions reveal more things that get us from one conclusion (valid and reasonable) to another (factual and consistently correct).
So it's still my opinion that the question could benefit from more detail to avoid any confusion or bad assumptions. No surprise though, that's also my opinion in real life scenarios. When a user reports, "It's broken", or "I'm getting an error when I do x" or whatever, I always try to get more detail to get a better understanding, to uncover the scenarios that are invalidating mine or other developer's assumptions.
-
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply