I am trying to speed up an Azure SQL Database View and I have read that I should start by using
WITH SCHEMABINDING, however I always get the same error:
Parse error at line: 1, column: 28: Incorrect syntax near 'SCHEMABINDING'. I get the same error on both views below.
We store our data in Azure Data Warehouse ( which I think is called now Azure Synapse Analytics) and I am wondering if this is some sort of limitation that exists and I am unaware of.
I have created non clustered indexes on the tables the view reads and they seem to speed it up, however I am also a bit unsure on that one. I do not know if they automatically kick in or if I need to type WITH (NOEXPAND).
Thanks
CREATE VIEW dbo.vKey1 with SCHEMABINDING AS SELECT KeyUser, Name FROM dbo.Test
SET ANSI_NULLS ON GO
SET QUOTED_IDENTIFIER ON GO
CREATE VIEW [dbo].[email campaigns data test] with SCHEMABINDING AS SELECT j.emailname AS 'Email Name',j.emailsubject AS 'Email Subject',COALESCE(l.centre_name,'Non Centre Specific Campaign') AS Centre,j.Category,s.JobId,s.BatchId,s.eventdate_utc As 'Sent Date',COUNT(s.subscriberkey) AS 'Sends',COUNT(b.subscriberkey) as 'Bounce',COUNT(o.subscriberkey) as 'Open',COUNT(c.subscriberkey) as 'Click' FROM salesforce_marketing_cloud.sent s JOIN [salesforce_marketing_cloud].[job] j on j.jobid=s.jobid LEFT JOIN salesforce_marketing_cloud.bounce b on s.jobid=b.jobid AND s.listid=b.listid AND s.batchid=b.batchid AND s.subscriberkey=b.subscriberkey LEFT JOIN salesforce_marketing_cloud.opens o on s.jobid=o.jobid AND s.listid=o.listid AND s.batchid=o.batchid AND s.subscriberkey=o.subscriberkey LEFT JOIN salesforce_marketing_cloud.click c on o.jobid=c.jobid AND o.listid=c.listid AND o.batchid=c.batchid AND o.subscriberkey=c.subscriberkey LEFT JOIN salesforce_marketing_cloud.location mcl on j.fromname=mcl.fromname LEFT JOIN [location].[location] l ON l.salesforce_marketing_cloud_location_id = mcl.id AND l.[type] = 'Centre' WHERE s.eventdate_utc>='1/03/2020' AND (o.IsUnique=1 OR o.IsUnique is Null) AND (c.IsUnique=1 OR c.IsUnique is Null) GROUP BY j.emailname,j.emailsubject,COALESCE(l.centre_name,'Non Centre Specific Campaign'),j.Category,s.JobId,s.BatchId,s.eventdate_utc; GO
