Is there any alternative of CREATE TYPE in SQL as CREATE TYPE is Not supported in Azure SQL data warehouse

Question

I am trying to execute this query but as userdefined(Create type) types are not supportable in azure data warehouse. and i want to use it in stored procedure.

CREATE TYPE DataTypeforCustomerTable AS TABLE(
    PersonID int,
    Name varchar(255),
    LastModifytime datetime
);
GO

CREATE PROCEDURE usp_upsert_customer_table @customer_table DataTypeforCustomerTable READONLY
AS

BEGIN
  MERGE customer_table AS target
  USING @customer_table AS source
  ON (target.PersonID = source.PersonID)
  WHEN MATCHED THEN
      UPDATE SET Name = source.Name,LastModifytime = source.LastModifytime
  WHEN NOT MATCHED THEN
      INSERT (PersonID, Name, LastModifytime)
      VALUES (source.PersonID, source.Name, source.LastModifytime);
END
GO

CREATE TYPE DataTypeforProjectTable AS TABLE(
    Project varchar(255),
    Creationtime datetime
);
GO

CREATE PROCEDURE usp_upsert_project_table @project_table DataTypeforProjectTable READONLY
AS

BEGIN
  MERGE project_table AS target
  USING @project_table AS source
  ON (target.Project = source.Project)
  WHEN MATCHED THEN
      UPDATE SET Creationtime = source.Creationtime
  WHEN NOT MATCHED THEN
      INSERT (Project, Creationtime)
      VALUES (source.Project, source.Creationtime);
END

Is there any alternative way to do this.

Answer 1

You've got a few challenges there, because most of what you're trying to convert is not the way to do things on ASDW.

First, as you point out, CREATE TYPE is not supported, and there is no equivalent alternative.

Next, the code appears to be doing single inserts to a table. That's really bad on ASDW, performance will be dreadful.

Next, there's no MERGE statement (yet) for ASDW. That's because UPDATE is not the best way to handle changing data.

And last, stored procedures work a little differently on ASDW, they're not compiled, but interpreted each time the procedure is called. Stored procedures are great for big chunks of table-level logic, but not recommended for high volume calls with single-row operations.

I'd need to know more about the use case to make specific recommendations, but in general you need to think in tables rather than rows. In particular, focus on the CREATE TABLE AS (CTAS) way of handling your ELT.

Here's a good link, it shows how the equivalent of a Merge/Upsert can be handled using a CTAS:

https://learn.microsoft.com/en-us/azure/sql-data-warehouse/sql-data-warehouse-develop-ctas#replace-merge-statements

As you'll see, it processes two tables at a time, rather than one row. This means you'll need to review the logic that called your stored procedure example.

If you get your head around doing everything in CTAS, and separately around Distribution, you're well on your way to having a high performance data warehouse.

Answer 2

Temp tables in Azure SQL Data Warehouse have a slightly different behaviour to box product SQL Server or Azure SQL Database - they exist at the session level. So all you have to do is convert your CREATE TYPE statements to temp tables and split the MERGE out into separate INSERT / UPDATE / DELETE statements as required.

Example:

CREATE TABLE #DataTypeforCustomerTable (
    PersonID        INT,
    Name            VARCHAR(255),
    LastModifytime  DATETIME
)
WITH
(
    DISTRIBUTION = HASH( PersonID ),
    HEAP
)
GO

CREATE PROCEDURE usp_upsert_customer_table 
AS

BEGIN

    -- Add records which do not already exist
    INSERT INTO customer_table ( PersonID, Name, LastModifytime )
    SELECT PersonID, Name, LastModifytime
    FROM #DataTypeforCustomerTable AS source
    WHERE NOT EXISTS
        (
        SELECT *
        FROM customer_table target
        WHERE source.PersonID = target.PersonID
        )
...

Simply load the temp table and execute the stored proc. See here for more details on temp table scope.

If you are altering a large portion of the table then you should consider the CTAS approach to create a new table, then rename it as suggested by Ron.