Is there any way to properly format the start of a CTAS in Azure Synapse Analytics?

JoshP

SQL Prompt within SSMS gives me:

CREATE TABLE #Staging<br>
WITH (DISTRIBUTION=HASH(BeneficiaryHICNBR), CLUSTERED COLUMNSTORE INDEX) AS SELECT DISTINCT<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;a.BeneficiaryMedicareID,<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;BeneficiaryHICNBR,<br>

And I can't find any setting to bring that AS/SELECT below the WITH. The full SELECT statement just hangs there on the right side of the screen.

Dan_J

Hi @JoshP

Thanks for reaching out to us regarding this!

Would it be possible to see a copy of your active Style file? I can then use this to see if I can achieve the formatting you are after.

JoshP

@Dan_J I've attached the file.  I changed the extension to .txt since this forum doesn't allow .json .

Dan_J

Hi Josh,

Thanks very much for providing your style file!

The option below should force AS below WITH, however from what I can see I think it may require the SELECT statement to be bracketed.

I'm quite sure it is (from looking at the file you provided), however I just wanted to check that this option is definitely 'ticked' at your side?

JoshP

@Dan_J I can confirm that the box is checked.  I can also confirm it's not working, even with brackets:

Dan_J

Hi @JoshP

Thanks for your quick response on this!

I am just going to engage with our development team to get their input on this, I will come back to you as soon as possible.

Dan_J

Hi @JoshP

I am continuing to engage with our SQL Prompt development team on this. We are currently trying to replicate the issue you are seeing here, to aid us in this, would you be able to provide the full/complete SQL example that demonstrates the issue?

JoshP

@Dan_J sure.  Attached as .txt since .sql is disallowed.

Dan_J

Hi @JoshP

Thanks for providing the SQL example!

From what I can see, it doesn't appear to be possible to achieve the formatting you are after. I am going to reach out to our development team to get their input/advise on this.

I will come back to you on this as soon as I can.

Dan_J

Hi @JoshP

I think we may have got to the bottom of this.

It looks as though SQL Prompt is not formatting this because the WITH (DISTRIBUTION=HASH...) syntax is specific to Azure Synapse Analytics. If this is the case, unfortunately we do not currently support Azure Synapse Analytics, and my understanding is that there are plans to support this in the near future either I'm afraid.

I appreciate that this would be a feature you would like to see added to SQL Prompt, and so would you be happy to log a feature request for this on our SQL Prompt User Voice page: https://redgate.uservoice.com/forums/94413-sql-prompt . This way our development team will have visibility of this.