Friday, July 15, 2022

SQL Cursors alternative for Azure Synapse Serverless Database

Problem: 

Using T-SQL loops for serverless SQL pools in Azure Synapse Analytics



Cursors are not supported in Azure Synapse, either dedicated or the serverless pool. 😈

Writing loops over data is then left to creating a temporary table and iterating over the rows in the dedicated SQL Pool.  

For dedicated pools, the solution is listed here Using T-SQL loops - Azure Synapse Analytics | Microsoft Docs

However, for the Serverless pools, creating a temporary table is not supported. 

So I tried a work-around, starting from the solution provided by Microsoft in the article above, and got it working for serverless. 

Instead of temporary tables, I created a view. Here in this example, I wanted to drop all external tables - as a part of a deploy script (since the dacpac approach doesnt work for serverless, more on that later).

The script 
Creates a view from all available external tables,
Gets the counter for a loop over the table records
For each iteration, query that view to get table names & drop them.

The downside with this approach, is with each iteration, the sys.external_tables gets queried.
I would've rather captured the data in a list variable, and iterate over that data. I will try that next. 


 IF EXISTS (SELECT * FROM sys.views where name = 'Tbl_Tables')
    DROP VIEW Tbl_Tables
    GO

    CREATE VIEW Tbl_Tables
    AS SELECT
        ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS MySequence,
        s.name as tablename,
        s.object_id as table_object_id,
        sch.name as schema_nm
    from sys.external_tables s inner join
    sys.schemas sch on s.schema_id =  sch.schema_id;
    GO

    DECLARE @object_count INT = (SELECT COUNT(*) FROM Tbl_Tables), @i INT = 1;
    DECLARE @dynamicstmtdropview VARCHAR(2000);
   
    WHILE   @i <= @object_count
    BEGIN

        DECLARE @table_name VARCHAR(2000) = (
                SELECT tablename FROM Tbl_Tables WHERE MySequence = @i)
        DECLARE @schema_name VARCHAR(100) = (
                SELECT schema_nm FROM Tbl_Tables WHERE MySequence = @i)

        SET @dynamicstmtdropview = 'DROP EXTERNAL TABLE '+@schema_name+'.'+ @table_name
        print @dynamicstmtdropview
       
        exec (@dynamicstmtdropview)

        SET @i += 1
    END