OrderBy clause is resulting different result sets when order column having same data

Question

We have a stored proc to return set of records based on Page Number and Page Size. Sorting is being done by a column "CreateDateTime". If value of CreatedDateTime is same for all the records, it is giving the results sets in different orders. The behavior is inconsistent.

Some Portion of Code:

SET @FirstRec  = ( @PageNo - 1 ) * @PageSize 
SET @LastRec   = ( @PageNo *@PageSize + 1 )   

SELECT * 
FROM 
( 
      select ROW_NUMBER() OVER (ORDER BY CreatedDateTime)     
       AS rowNumber,EMPID 
      From Employee
) as KeyList 
WHERE rowNumber > @FirstRec AND rowNumber <  @LastRec

Please provide some inputs on this.

Answer 1

This is "by design"

SQL Server (or any RDBMS) does not guarantee results to be returned in a particular order if no ORDER BY clause was specified. Some people think that the rows are always returned in clustered index order or physical disk order if no order by clause is specified. However, that is incorrect as there are many factors that can change row order during query processing. A parallel HASH join is a good example for an operator that changes the row order.

If you specify an ORDER BY clause, SQL Server will sort the rows and return them in the requested order. However, if that order is not deterministic because you have duplicate values, within each "value group" the order is "random" for the same reasons mentioned above.

The only way to guarantee a deterministic order is to include a guaranteed unique column or column group (for example the Primary Key) in the ORDER BY clause.