![]() My suggestion might seem a little controversial - after all, I'm pitching my own training here - but it's free, and it's even open source, licensed under the MIT license for folks to reuse. I use pages from the Stack Overflow database to explain how SQL Server delivers your query results. You're asking a great curiosity question here, so I'd suggest watching my free SQL Server training video series, How to Think Like the SQL Server Engine. Now for some gentle career advice: because these concepts are new to you, I would humbly suggest that rather than forcing SQL Server to do your bidding, thinking that you're somehow going to tune it to go faster, that you take a few steps back. ![]() Note the resemblance is between the phone book and the index, not the table (except when the table has a clustered index, in which case the table is the clustered index, so the table/clustered index has a fixed logical order). Taking our phone book example, if I asked you to group everyone together by first name, you would have to sort them by first name in order to accomplish that task. In order to do grouping, you may have to sort data. Suddenly, the results might seem sorted by first name - but that's just a coincidence because there's a new index available to satisfy my query.įor example GROUP BY this,that ends up with the data in order of that, which is weird, especially if there is no index on it. If later, someone creates an index just on first name, SQL Server is smart enough to use that for my first-name-only query because it'd be the narrowest/smallest object available to satisfy my query. (Our phone book's white pages are ordered by last name, first name, so if you just read from there, the first names will be all over the place.) At first, when we only have the white pages, you might use those white pages and yell the answer out to me - but they wouldn't seem sorted. Note that I didn't ask for the names to be sorted. You can't predict when you're going to get it, either.Īnother scenario - say that I only ask you for the first names in our city, not the last names: SELECT FirstName FROM dbo.PhoneBook That's called merry-go-round scanning, and SQL Server Enterprise Edition does that by default, and you can't turn it off. You would start where they already were (say the M's), and then when that other person reached the end of the phone book (the Z's), you'd circle back to the A's and read the part that you'd missed. You might choose to follow along with them, reading from the same area of the phone book. For example, say you walk over to the phone book to read the last names for me, and someone else is already reading through the white pages. Thing is, it's not guaranteed, and several scenarios can change that. That's why SQL Server's results can seem sorted by default. You wouldn't have to sort them - they'd just come out that way because that's how they were already stored. You would usually read them out to me in last name order. If I asked you to get that phone book and read out the names: SELECT FirstName, LastName FROM dbo.PhoneBook Let's pretend you've got the white pages of the phone book - remember, that thing Grandpa kept by the fridge so he could call his friends from the war. It also makes it harder to demonstrate to students that data is not automatically ordered.Ĭan anyone give me a simple demo script that shows the order of the results changing from execution to execution? Is that true?Īnd is there some kind of global setting that disables this behavior? Is there a way to get SQL Server to stop ordering data? Now it seems to me that ordering the data when not asked is an extra unnecessary step which wastes processing time. Is there something fundamentally different about SQL Server which causes it to order results for some queries even without an ORDER BY? I haven't observed this behavior in other databases such as MySQL, PostgreSQL and SQLite. If I use GROUP BY, the result set appears to be in order of the last column.įor example GROUP BY this, that ends up with the data in order of that, which is weird, especially if there is no index on it.By default, data appears to be ordered in primary key order, even if the primary key is nonclustered.This is a problem because I'm concerned that my students will become confused on this point. I've observed that SQL Server seems to return ordered results even when ORDER BY is not present in the query. I'm teaching a class in which I want to demonstrate to my students that queries are not guaranteed to return results in a specific order without an explicit ORDER BY.
0 Comments
Leave a Reply. |
Details
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |