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

Hi @braxx ，

Thanks for your update.

We recommend that you could post CREATE TABLE statements for your table together with INSERT statements with sample data, the creation of non-clustered index ,enough to illustrate all angles of the problem. We also need to see the expected result of the sample after executing your query.

In SQL Server, there are clustered index and non-cluseted index and there are not Normal index or Bitmap index like in Oracle. But it has a feature called Bitmap Filters, which are different but similar. You could have a deep read on it and find out whether it is what you are looking for.

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.