SQL Server 2014 CTP returns different query result from SQL Server 2012 - by Yi Feng, Li

Status : 

  By Design<br /><br />
		The product team believes this item works according to its intended design.<br /><br />
		A more detailed explanation for the resolution of this particular item may have been provided in the comments section.

Sign in
to vote
ID 840453 Comments
Status Closed Workarounds
Type Bug Repros 0
Opened 3/25/2014 2:35:28 AM
Access Restriction Public


execute the following sql in SQLServer2014 CTP will return a dataset with different order which we got from  
SQLServer2012 and early.

exec sp_executesql N'SELECT [PropertySet].[LogicId], [PropertySet].[Name], [PropertySet].[Owner], [PropertySet].[Revision], [PropertySet].[Id], [PropertySet].[IsOverridden], [PropertySet].[Sequence], [PropertySet].[Control] 
FROM [PropertySet] left join Object on PropertySet.Owner = Object.Id 
Where (0 <> Object.Direction or 0 <> Object.PresentationType or Object.Id is NULL) AND [PropertySet].[Parent] = @P1 and [PropertySet].[Format] = @P2  
ORDER BY [PropertySet].Sequence',N'@P1 uniqueidentifier,@P2 smallint','0BE0E009-F7B8-11DF-9D71-005056964503',4

The sample databases are attached
Sign in to post a comment.
Posted by Yi Feng, Li on 3/25/2014 at 7:00 PM
Thanks Jos,

Since the behavior of SQL2012 and SQL2014 are different, the results make trouble on our application. Is there any workaround to make the result to be constant?

Posted by Microsoft on 3/25/2014 at 9:34 AM
Thanks for submitting this feedback.

We do not give any guarantees about the sort order of query results beyond the ORDER BY statement. In the absence of an ORDER BY, the order of query results can change at any given time due to changes in the query plan.

Jos de Bruijn - SQL Server PM