December 13, 2016 at 8:56 am
there you go
December 13, 2016 at 9:01 am
zouzou (12/13/2016)
there you go
Comment out the left join, it's completely redundant - as you can see from the plan, it's optimised out anyway.
Can we please have actual rather than estimated plans?
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
December 13, 2016 at 9:09 am
see attached
December 13, 2016 at 9:10 am
Have you tried adding an ORDER BY to compare the results?
December 13, 2016 at 9:18 am
its working ok with ORDER BY and the same results are been returned...
what does that mean though? that you always have to insert ORDER BY ?
December 13, 2016 at 9:31 am
It means that data sets are not ordered.
If you want to be able to compare them, you'll have to sort them.
If you need them to have a certain order for display purposes, you need to explicitly define it. There's no default order and any order that you might see is not guaranteed to happen every time.
December 13, 2016 at 9:42 am
The order of returned results is never guaranteed without an ORDER BY, so if you require a result set with a guaranteed order, then yes, you must use an ORDER BY.
When you tend to get results in a consistent order without an ORDER BY, those are happy accidents based on how the query's being run and the data structures used.
In this case, you're likely seeing the varying results in 2014 and not 2005 because the 2014 query is using parallelism, while the 2005 plan is not.
Parallelism is one thing that can disrupt those happy accidents of consistent order without an ORDER BY.
Cheers!
December 13, 2016 at 9:59 am
PLEASE understand that ORDER BY is a HEAVY TEMP OPERATION and should ABSOLUTELY be avoided unless there is a defined requirement for the particular order (AND it isn't small set of data that can/should be ordered outside of SQL Server to save resources)!!!!
Regarding that last part above, NEVER forget that the fastest thing you can do in SQL Server is NOTHING! 😀 I advise developers to always ask a) does something need to be done at all and b) does it have to be done on SQL Server. Amazing how much more quickly almost every app I have encountered in 20 years as a SQL Server consultant would run if devs adhered to that Guruism! 😎
If the actual data returned, regardless of order, is the same and the data does not HAVE to be ordered, then do NOT add the ORDER BY to the production query. It is of course fine to use as a way to actually validate the proper output. But depending on the size of data you can also do this via cut/paste to Excel and use the EXACT(..) function. 🙂
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
December 14, 2016 at 1:21 pm
its working ok with ORDER BY and the same results are been returned...
what does that mean though? that you always have to insert ORDER BY ?
You are missing some fundamental concepts in RDBMS. The first is that tables are sets, and sets have no ordering by their nature. They are completed wholes. Without seeing anything because you did not post it, I will guess both result sets from both queries were actually identical. The order by clause converts the set into a cursor. A cursor is a sequential file structure, which does have an ordering.
There are many reasons this happens. Different indexing, parallelism, or totally different database architectures. For example, in the old days the way we did groupings was to first sort the result set and then scan it. However, we got hashing later. In products that make heavy use of this, like Teradata, the smaller hash buckets tend to be returned first from the SQL engine. In products that have parallelism, you get the same sort of affect, but it is unpredictable.
I am going to guess your mindset is still locked in sequential files, and not in RDBMS yet. It is kind of like learning a new language with totally different grammar.
Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL
December 17, 2016 at 1:04 pm
CELKO (12/14/2016)
its working ok with ORDER BY and the same results are been returned...
what does that mean though? that you always have to insert ORDER BY ?
You are missing some fundamental concepts in RDBMS. The first is that tables are sets, and sets have no ordering by their nature. They are completed wholes. Without seeing anything because you did not post it, I will guess both result sets from both queries were actually identical. The order by clause converts the set into a cursor. A cursor is a sequential file structure, which does have an ordering.
There are many reasons this happens. Different indexing, parallelism, or totally different database architectures. For example, in the old days the way we did groupings was to first sort the result set and then scan it. However, we got hashing later. In products that make heavy use of this, like Teradata, the smaller hash buckets tend to be returned first from the SQL engine. In products that have parallelism, you get the same sort of affect, but it is unpredictable.
I am going to guess your mindset is still locked in sequential files, and not in RDBMS yet. It is kind of like learning a new language with totally different grammar.
Joe, I (and many others) get really pissed off when people make illiterate/ignorant claims like "an order clause converts a set into a cursor". I've got used to you shouting unhelpfully, but for heaven's sake stop it - I know you're better than that because I haven't seen any of that sort of crap on the odd occasions when we have communicated one to one. A lot of your work has earned respect, but posting piles of nonsense (often offensive and unacceptable nonsense) makes people think much less of you than your numerous helpful contributions to discussion of issues in RDBMS understanding and relational algebra deserve.
Tom
December 18, 2016 at 12:16 pm
, I (and many others) get really pissed off when people make illiterate/ignorant claims like "an order clause converts a set into a cursor".
Illiterate and ignorant? Which one of us served on the ANSI X3H2 SQL standards committee for a decade? This was our intent and that is why the order by clause only appears in cursor declarations not in select statements. How many decades did you spend reading the standards question or writing them? 😛 I think I have a little more authority here ..
The early implementations of SQL, such as Sybase, had not yet escaped history (or legacy or family curse) structures. This is why we had a BIT data type (the fundamental hardware type of bit), the convert () function (for the COBOL programmers were used to doing display work in a monolithic module of code), and adding the order by to a select statement (because result set was presented as sequential storage structures). But that is not what we wanted to do. Later implementations got more sophisticated, such as hashing, columnar stores, the virtual data was just as real as materialize data, etc.
Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL
December 19, 2016 at 6:46 am
CELKO (12/18/2016)
, I (and many others) get really pissed off when people make illiterate/ignorant claims like "an order clause converts a set into a cursor".
Illiterate and ignorant? Which one of us served on the ANSI X3H2 SQL standards committee for a decade? This was our intent and that is why the order by clause only appears in cursor declarations not in select statements. How many decades did you spend reading the standards question or writing them? 😛 I think I have a little more authority here ..
The early implementations of SQL, such as Sybase, had not yet escaped history (or legacy or family curse) structures. This is why we had a BIT data type (the fundamental hardware type of bit), the convert () function (for the COBOL programmers were used to doing display work in a monolithic module of code), and adding the order by to a select statement (because result set was presented as sequential storage structures). But that is not what we wanted to do. Later implementations got more sophisticated, such as hashing, columnar stores, the virtual data was just as real as materialize data, etc.
Mr. Celko,
Illiterate??? Ignorant??? You're really impressing so many, Joe... It continues to amaze me that ANYONE has any respect left for you. It's not that you are actually illiterate, or that you're inherently ignorant. It's that you just don't seem to know when to hold your tongue.
Fortunately or unfortunately, you just don't seem to care much about how others perceive you, and you're doing a great job of convincing the rest of us that perhaps at least one of those two labels actually applies. The simple truth is that the vast majority of those in the business of applying RDBMS technology to real-world problems are NOT at the level necessary to understand where you're coming from, and even those that are, aren't much in favor of you talking down to the rest of the community. You do such a great job of annoying others that I'm amazed anyone even listens any more. Please stop talking down to those that don't have your level of knowledge. It would go a long way towards improving your perception.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
December 19, 2016 at 10:25 pm
those were slanders aimed at me; I might do "ignorant"when I am talking to someone, but I will back it up with evidence. I believe I can present evidence that I am not illiterate (books in print, several thousand published articles, etc.) and an actual history of working on the particular topic were talking about; SQL.
And, yes, you are right. When I do a posting, all I care about is educating the poster. What I want to do is find out why they make mistakes; it was a college professor for decades, as well is a working programmer (1965, FORTRAN for DoD), so I appreciate the reason people make mistakes. Usually it is because they do not have the conceptual foundation.
When I first got involved Internet SQL forums, I tried not explaining anything and just giving answers. When I published my first books, decades ago, I would get emails telling me that because they bought my book I should do their homework for them (have you noticed how many post on SQL forms are still cheating, lying, stupid students asking for someone else to do their homework??).
After 30 years, when I answer someone who is not bothered to even read the forum rules about postings, why do you think I am rude? If I were doing this as a consultant in their company, their company would be paying me big bucks per day to babysit them. Here they are getting it for free because I think it is important to improve the quality of code in the universe. Yeah yeah I know, I know that sounds arrogant, but it is true.
After 30+ years of answering SQL questions, I found the main problem is that people do not know what RDBMS actually is. It is not particular products and their eccentricities, but the fundamentals.
I also found after 30+ years of doing this, that if you simply give an answer, the poster learns nothing. They cut-and-paste your code into their homework, and go on assuming that they now know SQL and have a concept of relational databases. But if you berate them then they will actually read the rest of your response and maybe maybe just maybe go out and read a book and learned something.
The best teachers I ever had were the ones that humiliated me when I was blindly stupid and could not get the basic concepts. They rubbed my nose in it until I learned. Bless them! Look up "keisaku"; my wife is an ordained Soto Zen priest.
Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL
December 20, 2016 at 1:54 pm
The problem Joe is that your so-called "education" is both 30-years old (and thus missing out on new constructs) and MUCH WORSE totally FUBAR in many cases when it comes to SQL SERVER, which is what this forum is all about.
I will reiterate: please stop posting here!!
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
December 21, 2016 at 7:21 am
CELKO (12/19/2016)
The best teachers I ever had were the ones that humiliated me when I was blindly stupid and could not get the basic concepts.
Here you just scare people away. How many times does the OP come back after you have posted one of your rants? This is not a classroom and you are not our teacher. Most people are not here to improve their grades, and the ones that are may end up learning more than their current assignment. Learning to ask for help is critical to success. I would much rather people come here to get good advice than try to build it on their own when they are struggling with the fundamentals.
I understand you think you are improving the SQL Server community, and you certainly have a lot of knowledge that could benefit people here, but I think your arrogant, vitriolic response to people asking for help does more harm than good.
If the content of your responses was good and helpful, you wouldn't have to constantly remind people how long you've been doing this or how many books you've written.
Wes
(A solid design is always preferable to a creative workaround)
Viewing 15 posts - 16 through 30 (of 41 total)
You must be logged in to reply to this topic. Login to reply