How to add external table with computed column in SQL project in Visual Studio

Question

Hi Everyone, SSDT / Visual Studio expert is needed, right now
In Azure SQL Managed Instance in database I've got External Table with Computed Column:

CREATE EXTERNAL TABLE [ExternalTableSchema].[ExternalTableName]
(
	[MANDT] [varchar](3) NOT NULL,
	[COSTING_DATE] [varchar](8) NOT NULL,
	[FILENAME]  AS CAST(filepath() AS NVARCHAR(255))
)
WITH (
	 DATA_SOURCE = [ExternalDataSource]
	,LOCATION = N'ABC/XYZ'
	,FILE_FORMAT = [Parquet]
	,REJECT_TYPE = VALUE
	,REJECT_VALUE = 0)

And in SSMS everything is working as expected:

But when I add it to Visual Studio SQL Project I've got error - "SQL46010: Incorrect syntax near AS.":

I know that for standard table it is possible to add Computed Column in SQL Project but for external table it seems it's creating some errors. So here is my question - DOES ANYONE KNOW HOW TO SOLVE THAT KIND OF ISSUE AND ADD THAT TO REPOSITORY?

Answer 1

External tables, by their nature, are meant to provide a SQL-like interface to data stored outside of the SQL Database or Managed Instance, such as Azure Blob Storage or Azure Data Lake. They are defined in SQL but do not support all the features that regular tables do, such as indexing or computed columns, in the traditional sense. However, your SQL definition for creating an external table with a computed column (FILENAME in this case) is syntactically correct and should work in Azure SQL Managed Instance. The challenge comes when trying to integrate this into a SQL project in Visual Studio, as SSDT might not fully support or expect computed columns in the definition of external tables. My recommendations for solutions you can integrate into SSDT/Visual Studio : Instead of defining the computed column directly within the external table definition, you might handle the computation of such columns in a pre-processing step (example a stored procedure) Although not ideal, manually editing the project files (.sqlproj and related .sql scripts) might bypass some of the validation errors you're encountering. You can use post-deployment scripts in SSDT to execute SQL code that SSDT does not natively understand or support in the design-time environment. This means you could potentially create the external table without the computed column in the SSDT design-time environment but then add the computed column in a post-deployment script.

Answer 2

You would need to slap a view on top of the external table.

Answer 3

Hi @Piotr Siwka,

A little bit same as IIF in condition - SQL46010: Incorrect syntax near ).

You may raise the issue to DC community to check if it is a known issue.

Regards，

Zoe Hui

---

If the answer is helpful, please click "Accept Answer" and upvote it.