Search code examples
azure-synapseazure-synapse-pipeline

Synapse pipeline activities: SQL pool stored procedure vs generic stored procedure

In Synapse workspace, in a pipeline, I can add an activity to call a stored procedure in a dedicated SQL pool by using either activity:

  • SQL pool stored procedure
    • references a SQL pool
    • does not reference a linked service
    • Specific to dedicated SQL pool
  • Stored procedure (generic)
    • does reference a linked service, which references a SQL pool
    • not specific to dedicated SQL pool, i.e. can call Azure SQL DB stored procedure

What specifically is gained by using the SQL pool stored procedure activity over the generic one? I get that it's cleaner. In addition, it does not use a linked service, so I think it may immediately generate the connection, but I'd like specifics.

enter image description here

Solution

  • Using the SQL pool stored procedure activity in a Synapse workspace pipeline offers several benefits over the generic stored procedure activity.

    The below are few of them:

    • The SQL pool stored procedure activity is specific to dedicated SQL pool. It provides a simplified and streamlined stored procedure implementation compared to SQL Server
    • The biggest difference compared to SQL Server is that the stored procedure isn't pre-compiled code. When a dedicated SQL pool executes your stored procedure, the SQL statements are parsed, translated, and optimized at run time.
    • The SQL pool stored procedure activity is used to invoke a stored procedure in a dedicated SQL pool
    • The SQL pool stored procedure activity supports the following settings: name, description, type, sqlPool, storedProcedureName, and storedProcedureParameters

    No linked service: As you mentioned, the SQL pool stored procedure activity does not require a linked service. This can simplify your pipeline configuration and reduce the number of objects you need to manage within your Synapse workspace. It also means that you don't need to set up a separate linked service to establish the connection to the SQL pool.

    References: Using stored procedures for dedicated SQL pools in Azure Synapse Analytics

    Transform data by using SQL pool stored procedure activity in Azure Synapse Analytics