![]() 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.
0 Comments
Leave a Reply.AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |