query results sorted randomly with each execution

Question

Need your advice guys!

I am doing a migration of SQL queries from Oracle to Sql Server and observed a weird behavior of one of them. Each execution of the below query may give me results sorted different way and the wrong calculation of BAR_CURR_CODE column. While the same query run in Oracle always returns the same, stable results.

SELECT  
	PROD_ID,  
	UOM_ID,  
	FIRST_VALUE(BAR_CURR_CODE) over(partition BY prod_id order by  
						  		case   
								when UOM_ID in ('IT') then 1  
								when UOM_ID in ('MP') then 2  
								when UOM_ID in ('BP') then 3  
								when UOM_ID in ('CS') then 4  
								when UOM_ID in ('SW') then 5  
								else 99 end  
								) AS BAR_CURR_CODE  
	, BAR_CURR_CODE AS org_BAR_CURR_CODE  
FROM  
	[BSR_STG_Q].[Cdl_Prod_Uom_Fct]  
WHERE  
	curr_ind  = 'Y'  
	and BAR_CURR_CODE is not null  
	and prod_Id = '00267461'  

Here is the proof. First execution in SQL Server. BAR_CURR_CODE is different than in second execution.

Second execution in SQL Server. Made a couple of seconds after first one. Data were not updated during this time.

Here is the example of the same query from Oracle. The result is always the same with each execution

I do not understand why the behaviour is different in SQL Server and Oracle and how can I fix it.
TIA

Bartek

Answer 1

You're operating on the wrong assumptions. Per definition of ANSI SQL, order of a result set is only defined and created by an explicit ORDER BY clause on the outer SELECT statement or expression of a window function.

Result sets may often have the same order even without ORDER BY statement due to internal implementation details. But there is NO guarantee for this. Thus you've been only lucky.

You're ordering by a CASE expression which returns 99 as constant value. This order is arbitrary per definition as it does not cover a candidate key. Thus add a "failback" order criteria and you're safe:

SELECT PROD_ID ,
       UOM_ID ,
       FIRST_VALUE(BAR_CURR_CODE) OVER ( PARTITION BY prod_id
                                         ORDER BY CASE WHEN UOM_ID IN ( 'IT' ) THEN 1
                                                       WHEN UOM_ID IN ( 'MP' ) THEN 2
                                                       WHEN UOM_ID IN ( 'BP' ) THEN 3
                                                       WHEN UOM_ID IN ( 'CS' ) THEN 4
                                                       WHEN UOM_ID IN ( 'SW' ) THEN 5
                                                       ELSE 99
                                                  END ASC ,
                                                  UOM_ID ASC ) AS BAR_CURR_CODE ,
       BAR_CURR_CODE AS org_BAR_CURR_CODE
FROM   BSR_STG_Q.Cdl_Prod_Uom_Fct
WHERE  curr_ind = 'Y'
       AND NOT BAR_CURR_CODE IS NULL
       AND prod_Id = '00267461';

Answer 2

Hi @braxx ,

The syntaxsql of FIRST_VALUE function is as below:

FIRST_VALUE ( [scalar_expression ] ) [ IGNORE NULLS | RESPECT NULLS ]
OVER ( [ partition_by_clause ] order_by_clause [ rows_range_clause ] )

The order_by_clause determines the logical order in which the operation is performed.

In your query, you only defined the situations when UOM_ID is 'IT' ,'MP' and so on.
But you did not define the specific order of 'B1' ,'B2' and 'E1' and all of these three UOM_ID would be defined as 99. Then the SQL could not identify the order of these.

You could add one more column in order part as suggested by Stefan.

In addition, you could also define these three UOM_IDs like below:

SELECT  
PROD_ID,  
UOM_ID,  
FIRST_VALUE(BAR_CURR_CODE) over(partition BY prod_id order by  
            case   
            when UOM_ID in ('IT') then 1  
            when UOM_ID in ('MP') then 2  
            when UOM_ID in ('BP') then 3  
            when UOM_ID in ('CS') then 4  
            when UOM_ID in ('SW') then 5  
			when UOM_ID in ('B1') then 6  
			when UOM_ID in ('B2') then 7  
			when UOM_ID in ('E1') then 8  
            else 99 end  
            ) AS BAR_CURR_CODE  
, BAR_CURR_CODE AS org_BAR_CURR_CODE  
FROM  
[BSR_STG_Q].[Cdl_Prod_Uom_Fct]  
WHERE  
curr_ind  = 'Y'  
and BAR_CURR_CODE is not null  
and prod_Id = '00267461'  

Finally, you could also consider to add 'rows between unbounded preceding and current row' or 'rows between unbounded preceding and unbounded following' after order part in case of any error appears.

Best regards
Melissa

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 3

How to recreate the "BITMAP" type index in sql server?

SQL Server don't have indexes of type BITMAP like Oracle.

Answer 4

I would like to create the analogical indexes in Sql Server to verify it is causing the issue.

The issue is caused by the fact that the query is non-deterministic and the varying results you have received from SQL Server are all correct from how the query is written.

If you want the query to be deterministic, and be guaranteed to return a certain results, you will need to add WHEN lines like you have above:

              when UOM_ID in ('B1') then 6
              when UOM_ID in ('B2') then 7
              when UOM_ID in ('E1') then 8

If you do not, the result you get is just by chance. This applies both to Oracle and to SQL Server. There is no way around this.

Answer 5

Thank you both for your contribution. Apperciate it.

As I am doing a migration only from Oracle do SQL Server, I would like to avoid adding a new logic like

             when UOM_ID in ('B1') then 6  
             when UOM_ID in ('B2') then 7  
             when UOM_ID in ('E1') then 8  

as it may behave differently in other cases not mentioned here.

What I noticed and what I supposed may be related to the problem is that the Oracle table has indexes created while SQL Server one not.

PK indexes may sort a data differet way I suppose. I would like to create the analogical indexes in Sql Server to verify it is causing the issue. The doubts I have here is how to recreate it properly:

Does Unique Clustered index can be used to recreate a "Normal" type index in Oracle?
How to recreate the "BITMAP" type index in sql server?