July 27, 2016 at 12:13 pm
I got an interesting observation.
A log table (the base table) as an identity column (RowID) as the primary key (clustered). The key definition is "RowID Desc". When I run the query "select top 1000 * from dbo.MyLog", the records are ordered descendingly by RowID.
I create a view to straight output all columns from this table, like "select RowID, Col1, Col2 from dbo.MyLog (nolock)". When I run the query "select top 1000 * from dbo.vw_MyLog", returned records are not ordered by RowID. I am not sure what order it is. Once I removed "nolock", then the returned records have the same order as the base table.
Any explanation for this?
Thanks.
July 27, 2016 at 12:31 pm
The first point to make is that you should never assume the output of a query will be ordered any particular way unless you use an ORDER BY clause (I'm not saying you're making that assumption, but it's a good thing to make clear out of the gate).
As to your results, speaking roughly scans can either follow the leaf-level of an index or the allocation order. There are conditions around when the latter can be used, and using a locking hint like NOLOCK or TABLOCK is one of them.
Most likely your original query was using an ordered scan of the clustered index, in which case you will typically see the results in that order, while the NOLOCK query is using an allocation order scan.
Read http://sqlperformance.com/2015/01/t-sql-queries/allocation-order-scans for a good article on that.
If you expect the results to be guaranteed to be returned in a particular order, ORDER BY must be used.
Cheers!
July 27, 2016 at 2:47 pm
Thanks for the quick reply and right-on explanation.
The link is a great resource.
July 27, 2016 at 4:33 pm
In theory, it's also possible that you could get any 1,000 rows that are already present in buffers and thus don't require physical I/O. Without an ORDER BY, any 1,000 rows are valid.
Also, since SQL now "shares" I/O among multiple query requests if possible, so you could even get non-sequential, i.e. random, rows that just happened to be in the buffers at that moment. Other dbms's have done this for quite some time, SQL Server just started doing it.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
July 28, 2016 at 7:29 am
The NOLOCK hint is one that many people don't fully understand. It is usually not a good idea to use when accuracy is important. A view is probably not a good candidate for that hint for sure. If you are going to use query hints you need to include the WITH keyword. Omitting it has been deprecated. You can read more about that hint here. http://blogs.sqlsentry.com/aaronbertrand/bad-habits-nolock-everywhere/[/url]
Also, keep in mind that even if you use the top 100 percent...order by trick in a view it still does NOT guarantee that selecting from that view will be ordered. The order by must always be in the final query.
_______________________________________________________________
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/
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply