Using “cursors” in PDW

Technical Value

PDW v1/v2

Did I say cursor? Isn’t this an evil word? Shouldn’t we try as hard as possible to avoid them in database design and especially in a data warehouse?

Yes, sure. But there might be some patterns which make it useful to loop over a table, for example a configuration table, and do something with each line of the table. Since PDW currently doesn’t support cursors (why should it?), what can we do?

One option is to use the foreach-container in SSIS. It’s a good, reliable and easy way to implement loops.

However, if you need to, you can also do this using SQL. The following example shows a loop over all partitions of the FactSalesHeader table:

  1. create table #FactSalesHeaderPartitions      
  3. AS      
  4. SELECT sp.partition_number, prv.value AS boundary_value, lower(sty.name) AS boundary_value_type, sp.rows,      
  5. row_number() over (order by sp.partition_number) AS RowNr      
  6. FROM sys.tables st JOIN sys.indexes si ON st.object_id = si.object_id AND si.index_id <2      
  7. join sys.partitions sp ON sp.object_id = st.object_id AND sp.index_id = si.index_id      
  8. join sys.partition_schemes ps ON ps.data_space_id = si.data_space_id      
  9. join sys.partition_range_values prv ON prv.function_id = ps.function_id      
  10. join sys.partition_parameters pp ON pp.function_id = ps.function_id      
  11. join sys.types sty ON sty.user_type_id = pp.user_type_id AND prv.boundary_id = sp.partition_number      
  12. where st.object_id = (select object_id from sys.objects WHERE name = 'FactSalesHeader')
  15. declare @part_count int = (select count(*) from #FactSalesHeaderPartitions)
  18. declare @i int = 1
  21. while @i <= @part_count begin
  24.     declare @partition int      
  25.     declare @boundary int      
  27.     select @partition=partition_numnber, @boundary=boundary_value
  28.       from #FactSalesHeaderPartitions where RowNr=@i      
  30.     -- do something with the partition, for example take it out, merge (ctas)      
  31.     -- it with new data and then switch the resulting partition into the      
  32.     -- fact table.      
  34.     set @i+=1      
  35. end
  38. drop table #FactSalesHeaderPartitions

The idea is to put all the rows that you want to create a loop for in a temporary table, including a column with a row number. You can then create a simple while-loop starting with 1 and going up the the number of rows in the temporary table. Since the row number is also included in the temporary table, you can then select the correct values into your variables within the while loop.

Please note that the above SQL code for reading partitions uses PDW v1 AU 3.5 system tables. For other versions the table names may be different.

Neuen Kommentar schreiben

Der Inhalt dieses Feldes wird nicht öffentlich zugänglich angezeigt.


  • Keine HTML-Tags erlaubt.
  • HTML - Zeilenumbrüche und Absätze werden automatisch erzeugt.
  • Web page addresses and email addresses turn into links automatically.
Teilen auf

Newsletter Anmeldung

Abonnieren Sie unseren Newsletter!
Lassen Sie sich regelmäßig über alle Neuigkeiten rundum ORAYLIS und die BI- & Big-Data-Branche informieren.

Jetzt anmelden