Are the posted questions getting worse?
-
Brandie Tarvin (6/3/2016)
Hugo Kornelis (6/1/2016)
Lynn Pettis (6/1/2016)
Just looking at the diagrams here does anyone else think a few of them may be wrong? It could just be me, but I need to do some testing to put my mind at ease.The diagrams are correct, but I do not like the use of outer join + IS NULL to replace what should be a simple NOT EXISTS plus correlated subquery.
The lower right one looks odd, I cannot imagine ever needing that in a real situation.
Actually, I use the outer join + IS NULL quite frequently usually because that is the simpler way of doing the query in my workplace.
Care to explain how outer join + is null is simpler than not exists?
Perhaps it's an acquired taste because you are now fully used to it?
I can't see using the FULL OUTER at all, either configuration, in a real situation. But I agree the lower right one looks odd.
Full outer join has its uses. Rarely, but it happens.
(Just had to write one yesterday)
Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
Visit my SQL Server blog: https://sqlserverfast.com/blog/
SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/
-
Hugo Kornelis (6/3/2016)
Brandie Tarvin (6/3/2016)
Hugo Kornelis (6/1/2016)
Lynn Pettis (6/1/2016)
Just looking at the diagrams here does anyone else think a few of them may be wrong? It could just be me, but I need to do some testing to put my mind at ease.The diagrams are correct, but I do not like the use of outer join + IS NULL to replace what should be a simple NOT EXISTS plus correlated subquery.
The lower right one looks odd, I cannot imagine ever needing that in a real situation.
Actually, I use the outer join + IS NULL quite frequently usually because that is the simpler way of doing the query in my workplace.
Care to explain how outer join + is null is simpler than not exists?
1) NOT EXISTS doesn't always work. Even the ones that SQL auto-generates don't always work when it comes to dropping objects. SSMS says "command successful" but the object is still there.
2) Most of the queries in question are joins with multiple tables or large WHERE clauses. The IS NULL often comes up in the ON clause of these queries. Writing a NOT EXISTS / correlated sub-query (I disagree that there's actually a use for correlated sub-queries) takes more effort and doubles the coding in the specific use cases for our business. So, no. I don't agree that this is the simple way of doing things.
Perhaps it's an acquired taste because you are now fully used to it?
Wow. That's a little condescending, isn't it?
-
Brandie Tarvin (6/3/2016)
1) NOT EXISTS doesn't always work. Even the ones that SQL auto-generates don't always work when it comes to dropping objects. SSMS says "command successful" but the object is still there.Got a connect item with a repo? Because if you've found a reproducible example of NOT EXISTS not working correctly, it's a bug and should be files as such so that it can be fixed.
Wow. That's a little condescending, isn't it?
To suggest that your preferred method is the one that you're most familiar and comfortable with? Why would that be condescending?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
Brandie Tarvin (6/3/2016)
Steve Jones - SSC Editor (6/1/2016)
SSMS is decoupled from SQL. You need to install that separately.Wait. Client tools are no longer an option to install with the rest of SQL Server?
That sucks.
No, no, no. It's excellent. Marvelous even. You download and install SSMS all on it's own. No more accidently installing a server locally when all you need is the tools. This is a very good thing.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
-
Grant Fritchey (6/3/2016)
Brandie Tarvin (6/3/2016)
Steve Jones - SSC Editor (6/1/2016)
SSMS is decoupled from SQL. You need to install that separately.Wait. Client tools are no longer an option to install with the rest of SQL Server?
That sucks.
No, no, no. It's excellent. Marvelous even. You download and install SSMS all on it's own. No more accidently installing a server locally when all you need is the tools. This is a very good thing.
Exactly. Microsoft is making it easier to find and download SSMS and SQL Server Data Tools.
-
Brandie Tarvin (6/3/2016)
1) NOT EXISTS doesn't always work.Agree with what Gail writes: please post or point us to a repro.
I have never experienced a NOT EXISTS not working as it should and I use it all the time, so if there's a potential bug there please let me know!
Even the ones that SQL auto-generates don't always work when it comes to dropping objects. SSMS says "command successful" but the object is still there.
I've seen lots of mistakes in the auto-generated code. My confidence level in that part of the product is fairly low. So yeah, even though I personally never saw this I 100% believe that this may be the case. Not because of errors in NOT EXISTS, but because of incorrect usage of NOT EXISTS.
2) Most of the queries in question are joins with multiple tables or large WHERE clauses. The IS NULL often comes up in the ON clause of these queries. Writing a NOT EXISTS / correlated sub-query (I disagree that there's actually a use for correlated sub-queries) takes more effort and doubles the coding in the specific use cases for our business. So, no. I don't agree that this is the simple way of doing things.
If you have queries with outer joins and need a modified version to find only the non-matching rows, then yeah I agree that in that specific case just adding WHERE xxx IS NULL to the query is far more efficient than rewriting as a NOT EXISTS.
Perhaps it's an acquired taste because you are now fully used to it?
Wow. That's a little condescending, isn't it?
Not intended as such, and my apologies if it came across that way.
I have been using the outer join / where is null method for some time because I had been told that this is more efficient. Once I learned that this is a myth I decided to switch to NOT EXISTS, and I still remember how that construction looked weird and unfamiliar at first. So that comment was actually from personal experience.
Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
Visit my SQL Server blog: https://sqlserverfast.com/blog/
SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/
-
Grumpy DBA (6/3/2016)
Grant Fritchey (6/3/2016)
Brandie Tarvin (6/3/2016)
Steve Jones - SSC Editor (6/1/2016)
SSMS is decoupled from SQL. You need to install that separately.Wait. Client tools are no longer an option to install with the rest of SQL Server?
That sucks.
No, no, no. It's excellent. Marvelous even. You download and install SSMS all on it's own. No more accidently installing a server locally when all you need is the tools. This is a very good thing.
Exactly. Microsoft is making it easier to find and download SSMS and SQL Server Data Tools.
As well as upgrade them independently of Service Packs and Cumulative Updates. I for one used to always forget to make sure I ran those on machines that only had client tools. I'm seeing this as nothing but wonderful.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
-
Grant Fritchey (6/3/2016)
As well as upgrade them independently of Service Packs and Cumulative Updates. I for one used to always forget to make sure I ran those on machines that only had client tools. I'm seeing this as nothing but wonderful.Mmmmm :ermm:
Should I be using this now even though I am still on 2008R2 :unsure:
Far away is close at hand in the images of elsewhere.
Anon. -
Just tried installing 2016 Dev on a W7 machine, only to get this:
I hadn't realised that W7 was not supported. So now ... W10 upgrade on a Friday ... why not?
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
-
David Burrows (6/3/2016)
Grant Fritchey (6/3/2016)
As well as upgrade them independently of Service Packs and Cumulative Updates. I for one used to always forget to make sure I ran those on machines that only had client tools. I'm seeing this as nothing but wonderful.Mmmmm :ermm:
Should I be using this now even though I am still on 2008R2 :unsure:
I don't know. I haven't tested it with 2008R2. Nor have I seen in the docs if it's supported or not. I'm clueless. More so than usual.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
-
Brandie Tarvin (6/3/2016)
Hugo Kornelis (6/1/2016)
Lynn Pettis (6/1/2016)
Just looking at the diagrams here does anyone else think a few of them may be wrong? It could just be me, but I need to do some testing to put my mind at ease.The diagrams are correct, but I do not like the use of outer join + IS NULL to replace what should be a simple NOT EXISTS plus correlated subquery.
The lower right one looks odd, I cannot imagine ever needing that in a real situation.
Actually, I use the outer join + IS NULL quite frequently usually because that is the simpler way of doing the query in my workplace.
I can't see using the FULL OUTER at all, either configuration, in a real situation. But I agree the lower right one looks odd.
Actually, I have found (as a result of this diagram) a better method of finding data that exists in database or another but not both. I was using nested excepts/union all but going to full outer join with null or between them reduced the number of scans on the tables.
-
Grant Fritchey (6/3/2016)
Grumpy DBA (6/3/2016)
Grant Fritchey (6/3/2016)
Brandie Tarvin (6/3/2016)
Steve Jones - SSC Editor (6/1/2016)
SSMS is decoupled from SQL. You need to install that separately.Wait. Client tools are no longer an option to install with the rest of SQL Server?
That sucks.
No, no, no. It's excellent. Marvelous even. You download and install SSMS all on it's own. No more accidently installing a server locally when all you need is the tools. This is a very good thing.
Exactly. Microsoft is making it easier to find and download SSMS and SQL Server Data Tools.
As well as upgrade them independently of Service Packs and Cumulative Updates. I for one used to always forget to make sure I ran those on machines that only had client tools. I'm seeing this as nothing but wonderful.
Me too, for all the reasons already stated. Grant, I can tell you that you aren't the only one who doesn't match SSMS on workstations.
-
Wow, I'm back. Five months in the wilderness and there was a lot of The Thread to catch up on.
I've learned a valuable lesson - when I'm not happy I don't communicate very well, which is not a good thing.
Hopefully the new role will suit myself better. I've certainly learned a whole load of new questions to ask at interviews.
I have to say I have missed this place, but it was self-inflicted.
-
The end of this week is almost here. This is a major milestone week at our company. We have been working for over 2 years on implementing a new ERP system to replace our hideously out of date RPG system. Some you know the system we are going to and others do not. Suffice it to say that I am not a fan of the decision that was made to use the new system we chose. In fact, in many ways it is not any improvement at all and in other cases it is a MAJOR step backwards.
Be that as it may the decision was made by people way further up in the pecking order than I am. We are going live on Monday with this new system at one of our remote locations. The rest of the company will be coming online over the next 6-9 months or so. It is quite nerve wracking to make such a complete systemic change while still attempting to maintain the business. My portion of the work and deployment has been done for awhile now but there are many people scrambling to finish everything up. Here is hoping that my phone doesn't ring in the middle of the night Sunday (our remote location is 6 hours ahead of me) and that Monday ends sometime before the rest of the world gets to next weekend.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/ -
Sean Lange (6/3/2016)
The end of this week is almost here. This is a major milestone week at our company. We have been working for over 2 years on implementing a new ERP system to replace our hideously out of date RPG system. Some you know the system we are going to and others do not. Suffice it to say that I am not a fan of the decision that was made to use the new system we chose. In fact, in many ways it is not any improvement at all and in other cases it is a MAJOR step backwards.Be that as it may the decision was made by people way further up in the pecking order than I am. We are going live on Monday with this new system at one of our remote locations. The rest of the company will be coming online over the next 6-9 months or so. It is quite nerve wracking to make such a complete systemic change while still attempting to maintain the business. My portion of the work and deployment has been done for awhile now but there are many people scrambling to finish everything up. Here is hoping that my phone doesn't ring in the middle of the night Sunday (our remote location is 6 hours ahead of me) and that Monday ends sometime before the rest of the world gets to next weekend.
Well, count me as one of those that know where you're going and offer my condolences on the topic.
So, people are scrambling to get things done today and you're supposed to launch one location on Monday? That doesn't sound like sufficient time for UAT or even proper validation. I don't envy the time you might have on Monday. I hope it doesn't happen, but I fear it will. Good luck, Sean.
Viewing 15 posts - 54,436 through 54,450 (of 66,000 total)
You must be logged in to reply to this topic. Login to reply