filepath() and filename() causes syntax error in SQL Server 2025 and external table

Question

We are currently running in SQL Server 2022 and use external tables on top of parquet files in a storage account. All the external table definitions looks something like this

CREATE EXTERNAL TABLE [my_external_data]
(
	[column_a] [varchar](20) NULL,
	[column_b] [varchar](100) NULL,
    [file_name]  AS CAST(filename() AS VARCHAR(1024)),
	[file_date]  AS CAST(filepath(1) AS DATE)
)
WITH (
	DATA_SOURCE = [my_data_source],
	LOCATION = N'/some_directory/*/data.parquet',
	FILE_FORMAT = [parquet_file_format],
	REJECT_TYPE = VALUE,
	REJECT_VALUE = 0
)

and works without issue.

We are currently reviewing the option to upgrade to SQL Server 2025 (in an Azure VM) but this external table definition gives an syntax error Incorrect syntax near 'filename' and removing that computed column definition instead gives the error Incorrect syntax near 'filepath'.

If i remove both computed columns the query creates the external table fine and we can query it without issues.

I have read the documentation, and below are two examples where this is discussed

useful-metadata-functions query-partitioned-data

The second link has an example where CREATE EXTERNAL TABLE is is used with filepath(), and the computed column is later used in that example in the WHERE clause.

Why am I getting syntax errors on the functions filename() and filepath()? According to the documentation they should still be available in SQL Server 2025.

How can I resolve the issue? We rely on those computed columns to filter / partition the files.

Clarification: We are running SQL Server 2022 as a managed instance.

Answer 1

I've made some more investigations, and it seems that this is not a breaking change between SQL 2022 and SQL 2025.

I installed SQL 2022 with Polybase on a local VM, and when I run the CREATE EXTERNAL TABLE script, I get the same error message as on SQL 2025:

Msg 102, Level 16, State 1, Line 23 Incorrect syntax near 'filepath'.

Instead this is a difference between Azure SQL Managed Instance and on-prem. I ran the script on two MIs. One which is tied to SQL 2025 and one that is of the always-up-to-date model. On these two, I got an error that the DATA_SOURCE does not exist, which makes perfect sense, since I had not created any. But I did not get the syntax error, so it seems that I got past that check. Thus, I would expect Jerry's external table to work on an managed instance tied to SQL 2025.

But, no, not on on-prem or in an Azure VM.

It is certainly confusing when Microsoft has feature parity in the general case between different environments, but then there is this odd functionality that only works in one place.

Answer 2

Looks like SQL Server 2025 still supports filename() and filepath() for querying PolyBase external data, but it does not appear to support using them as computed columns directly in the CREATE EXTERNAL TABLE definition in the way SQL Server 2022 did. Try creating the external table without those computed columns and use the functions directly in a view or query:

CREATE VIEW dbo.my_external_data_view
AS
SELECT
    column_a,
    column_b,
    CAST(filename() AS varchar(1024)) AS file_name,
    CAST(filepath(1) AS date) AS file_date
FROM dbo.my_external_data;

You can then filter with file_date or, preferably for file/partition elimination, use the filepath() expression directly in the predicate:

SELECT column_a, column_b
FROM dbo.my_external_data
WHERE CAST(filepath(1) AS date) >= '2026-08-01';

So this would be a SQL Server 2025 compatibility change in how the metadata functions can be used in the external-table definition, not removal of filename() or filepath(). Since you rely on these for file filtering, you might want to test the execution plan and file elimination with the direct filepath() predicate before migrating, because exposing it through a view does not necessarily guarantee the same predicate pushdown behavior you had in SQL Server 2022.

---

If the above response helps answer your question, remember to "Accept Answer" so that others in the community facing similar issues can easily find the solution. Your contribution is highly appreciated.

hth

Marcin

Answer 3

This is a known behavioral change between SQL Server 2022 and 2025, and the documentation is misleading because it shows filepath() used in queries but doesn't clearly call out that the computed column syntax inside CREATE EXTERNAL TABLE is no longer supported.

---

What changed:

In SQL Server 2022, PolyBase allowed filename() and filepath() as computed column expressions directly in the external table DDL. In SQL Server 2025, those functions are still available — but only as runtime functions in SELECT statements, not as persisted computed columns in the table definition itself. The parser now rejects them in that position.

This isn't a bug — it's a (poorly documented) breaking change in how the PolyBase engine handles metadata functions.

---

How to fix it:

Remove the computed columns from the external table definition, then create a view on top that exposes them:

Now point your existing queries at the view instead of the table directly. The filepath() partition elimination still works — the query optimizer pushes the predicate down when you filter on filepath(1) in a WHERE clause, whether it's in a view or an ad-hoc SELECT.

---

For partition pruning, you can still do:

Or query the table directly:

Both will correctly prune files — the optimizer recognizes filepath() in the WHERE clause and only reads matching partitions.

---

In short: the functions aren't gone, they just can't live inside the DDL anymore. A view gives you the same ergonomics your code already expects, with no loss in performance or partition elimination. You'll need to update your deployment scripts but existing query logic stays the same if you point it at the view.