The Joel on Software Discussion Group (CLOSED)

A place to discuss Joel on Software. Now closed.

This community works best when people use their real names. Please register for a free account.

Other Groups:
Joel on Software
Business of Software
Design of Software (CLOSED)
.NET Questions (CLOSED)
TechInterview.org
CityDesk
FogBugz
Fog Creek Copilot


The Old Forum


Your hosts:
Albert D. Kallal
Li-Fan Chen
Stephen Jones

SQL Server giving different row order for same query

SELECT Name from Table

sometimes gives
Smith
Jones

and sometimes
Jones
Smith

(alot more records in the real data)

I know why my SQL server is giving different result row orders but I don't remember the name of the SQL feature that does this.

Basically we have a web farm and the SQL server is using the part of the index that is currently loaded from a query running on the other server as an optimization -- not having to page the data back in. 

What is the MS term for this again?  I am trying to explain to my co-workers why we are having this issue.  I'd rather point them at a good tutorial but my google skills are lacking...
Billy
Wednesday, May 02, 2007
 
 
SQL Server does not guarantee the order of any resultset unless you include an "ORDER BY" clause.
nathan Send private email
Wednesday, May 02, 2007
 
 
Nathan -- I am quite well aware of that fact.  I am looking for the MS term for optimazation of re-using loaded indexes to reduce paging so that I can send my colleages to a web tutorial on the fact instead of spending an hour trying to explain it.
Billy
Wednesday, May 02, 2007
 
 
A couple caveats from SQL Books Online to my previous statement:
"There is no limit to the number of items in the ORDER BY clause. However, there is a limit of 8,060 bytes for the row size of intermediate worktables needed for sort operations. This limits the total size of columns specified in an ORDER BY clause."

"When used together with a SELECT...INTO statement to insert rows from another source, the ORDER BY clause does not guarantee the rows are inserted in the specified order."


SQL Server never guarantees the order of a resultset without using ORDER BY, and sometimes not even then.
nathan Send private email
Wednesday, May 02, 2007
 
 
ah.  gotcha.
nathan Send private email
Wednesday, May 02, 2007
 
 
I don't know what the correct term is either, but that suspiciously sounds like a cache.
TheDavid
Wednesday, May 02, 2007
 
 
>> SQL Server never guarantees the order of a resultset without using ORDER BY, and sometimes not even then.

???
David Aldridge Send private email
Wednesday, May 02, 2007
 
 
>> >> SQL Server never guarantees the order of a resultset without using ORDER BY, and sometimes not even then.

>>???

If you have three columns.  Column 1 has values 1, 1, 2, 2, 3 for the records. 

And Column 2 has 1, 2, 3, 4, 5.  If you Order By Column1, You could get results back like:

1 1
1 2
2 3
2 4
2 5

Or, you could get:

1 2
1 1
2 4
2 3
2 5

You would need to Order By on two columns to get the proper sort order.

And even then, your not guaranteed to get the results back in the same order every time if Column 3 has similar data.
Eric D. Burdo Send private email
Wednesday, May 02, 2007
 
 
David:

Try this:



CREATE VIEW ViewA AS
SELECT  TOP 100 PERCENT *
FROM        TableA
    order by 2
GO


Select * from ViewA

select * from TableA order by 2

Logically these should be the same.  However SQL will not apply the order by in the view...
Billy
Wednesday, May 02, 2007
 
 
Oh, we're definitely talking about two different things.

But to answer your original question, views are really thought of as "temporary tables" that have been optimized for reads at the expense of writes. There's nothing that explicitly in the SQL standard that requires them to obey and preserve ORDER BY clauses during their creation. For example, you can select, count and group specific rows from views. In that context, ordering the literal rows in the views is just as potentially meaningless as ordering the rows in the tables themselves.

I would also presume that because the question was asked, SQL Server does have a database specific way of enabling that functionality. Unfortunately, I don't know what it is, and I think it's probably more along the lines of instructing SQL Server to cache the entire view in memory (instead of grabbing rows as needed). That would preserve the row order.
TheDavid
Wednesday, May 02, 2007
 
 
The buffer pool?

http://msdn2.microsoft.com/en-us/library/aa175282(sql.80).aspx

Anyway, why do you need even an hour long tutorial? Are you just trying to justify that getting different orders is actually a good thing?  It is what it is.  Just like if a sort isn't stable, it isn't stable, and you've got to deal with it.
Grant Send private email
Wednesday, May 02, 2007
 
 
>But to answer your original question, views are really thought of as "temporary tables" that have been optimized for reads at the expense of writes

I have to disagree, because this might legitimize a common misunderstanding of views.

Views really are nothing more than, in essence, code snippets. When do you do a "SELECT * FROM [view] WHERE name='bob'", the query analyzer basically creates a compound query incorporating the elements of the query with the referenced view (if the view was SELECT * FROM [table], then the rendered query that is actually run is SELECT * FROM [table] WHERE name='bob'. Unless you materialize the view (rare) by indexing it, there are no temporary tables or read-specific optimizations, and views really are absurdly simple.

The only reason the situation above occurs is because of the TOP 100 PERCENT, which short-circuits the operation.

Here's one link that describes it, albeit not terribly clearly-

http://blogs.msdn.com/queryoptteam/archive/2006/03/24/560396.aspx

The situation the OP is describing usually occurs with parallel execution, where on SMP machines each CPU satisfies a certain part of the query, interleaving the results. Given that the work distribution isn't perfectly equal and coordinated, the results come back out of order. This has bitten a LOT of teams that tested thoroughly on their own box, and then got bitten when they deployed to an SMP prod box (though this is less the situation now with everyone have multiple cores on their desktop).
DF
Wednesday, May 02, 2007
 
 
Err...

I actually lost track of the original question. I think Billy understands the sort order is not guaranteed. He's looking for a good way to explain to his co-workers why its not guaranteed?

Sorry Billy.

The shortest, simplest explaination I can give is that the people who originally conceived of relational databases (I won't give the link because the white paper would be way over their heads), did not want to mandate that the sort order be preserved. Doing so would have placed limits on the different ways the database engine itself can be coded and optimized.
TheDavid
Wednesday, May 02, 2007
 
 
If you're looking for a term, say that without an explicit order, the ordering is non-deterministic.
DF
Wednesday, May 02, 2007
 
 
I think DF's response hints at the difficulty at writing a good explaination for the average reader. For example, I said "views are really thought of as 'temporary tables'..."
(notice the quotes) and didn't indicate at all what's temporary about them.

If you notice, in Microsoft SQL Server, you can actually create views and interact with them through the graphical user interfaces (Enterprise Manager and Query Analyzer) as if they were tables. The views themselves however are implemented as scripts, code snippets, etc etc.

The hard part is finding just the right level of abstraction for your audience.
TheDavid
Wednesday, May 02, 2007
 
 
It's hard to find the right level of abstraction, but when it comes to non-materialized views, temporary table (whether quotes, italicized, or surrounded by happy faces and bubblegum) is just about the worst definition possible.

The reason, as I mentioned in the prior post, is that a lot of people _DO_ think that views render to some sort of cache. While this is true for materialized views, 99.9999999999% of views out there are not materialized, and an amazingly high percentage of developers really do completely misunderstand what a view actually is.

So that's why I felt the need to comment, because your terminology seemed to support a far too common misunderstanding.
DF
Wednesday, May 02, 2007
 
 
I think Billy is ignoring the real question being asked of him.  Let's say the question asked was:

"Why don't I get the same results every time I display this grid?"

His response is:

"Because SQL Server doesn't guarantee that the result set of a SELECT will be sorted the same way every time if there isn't an ORDER BY in the statement that guarantees the uniqueness of the result set."

However, I have a sneaking hunch that the customer was really asking:

"Why doesn't your software return the same results for what to me looks like an identical query, every time?"

The correct answer is, of course:

"Ah.  Looks like there's a bug in our software.  We'll fix that for ya."

And then he rewrites all of his queries to have unique ORDER BY clauses.
Karl Perry Send private email
Wednesday, May 02, 2007
 
 
Karl Perry -- um, yeah thanks for that.  I already did fix the application.  No I need to explain to my co-workers and management what caused it and how not to do it again.
Billy
Wednesday, May 02, 2007
 
 
Indexed Views?

http://www.microsoft.com/technet/prodtechnol/sql/2005/impprfiv.mspx - "Improving Performance with SQL Server 2005 Indexed Views"
Michael Send private email
Wednesday, May 02, 2007
 
 
Who are you replying to, Michael? If you're referring to it in reply to my comment, note that I said-

"Unless you materialize the view (rare) by indexing it"...

A materialized view is an indexed view.
DF
Wednesday, May 02, 2007
 
 
"...an amazingly high percentage of developers really do completely misunderstand what a view actually is."

I'm apparently one of them. How would you describe a view, on the premise that everything at some level consists of code and/or data?

In other words, if a view is simply a snippet of code that excutes a query, what's the difference between that and a stored procedure? I mean if I accepted the code definition alone, I could argue that all stored procedures are simply subsets of views.
TheDavid
Wednesday, May 02, 2007
 
 
"could argue that all stored procedures are simply subsets of views."

Exactly - but it is the other way around - think of views as stored procedures that only allow a SQL statement
Ted
Wednesday, May 02, 2007
 
 
"In other words, if a view is simply a snippet of code that excutes a query, what's the difference between that and a stored procedure?"

Those two are worlds apart.  The stored procedure executes as a seperate unit.  So, an sp is more like the undesireable "temporary table" concept from above.  Think about how you would get the results of a stored procedure, but in a different order.  You would have to execute the procedure, put the results in a temp table and select from the temp table.

Views allow the caller to treat them as a table allowing the caller to join to the data, perform manipulation, or to reorder.  SQL doesn't execute them as two distinct steps.  If your view definition is:

CREATE VIEW view1
AS
SELECT FirstName, LastName FROM Employees

and the statement issued was:

SELECT * FROM view1 WHERE Region='WA'

then, SQL would only perform one step, it would internally transform your query into:

SELECT FirstName, LastName FROM Employees
WHERE Region = 'WA'

There are also structures that blur the line a bit, various types of user defined functions allow you to create views that have a lot of stored procedure like features without all of the costs of a stored procedure.  See http://www.developer.com/db/article.php/3598891

Personally, I would rather see 95% of the "returns records" style stored procedures re-written as Multi-statement Table-Valued Functions.
JSmith Send private email
Wednesday, May 02, 2007
 
 
Thanks all -- I was thinking of the term merry-go-round scans.  I found it in my copy of Inside SQL Server 2000
Billy
Thursday, May 03, 2007
 
 

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics
 
Powered by FogBugz