mardi 9 octobre 2018

Ntile function not dividing groups evenly when being ran from stored procedure

I have the following code inside a stored procedure.

select 
ID,NTILE(2) OVER (Partition by GroupID order by newID()) as RandomSplit
into #TempSplit
from TableA
where IsUpdated = 1

Update a
set a.SplitColumn = CASE WHEN b.RandomSplit = 1 THEN 'A'
                       WHEN b.RandomSplit = 2 THEN 'B' 
                  END
from Table A a
inner join #TempSplit b
on a.ID = b.ID

Now this code work as expect produces a sample data as below.

  GroupID  SplitColumn

   1       |     A
   1       |     A
   1       |     B
   1       |     B
   2       |     A
   3       |     A
   3       |     B

However when I execute this code from the stored procedure I get the following results

  GroupID  SplitColumn

   1       |     A
   1       |     A
   1       |     A
   1       |     B
   2       |     A
   3       |     A
   3       |     B

This is sample data but basically what is happening is that when I execute from the stored procedure the groups are not distributed evenly even, in my real data is by thousands of records rather than just one. Not sure what is exactly causing this behavior since again if I execute the code manually it comes up with the correct behavior.




Aucun commentaire:

Enregistrer un commentaire