NTILE in Fabric Data Warehouse - Doesn't Work. Bug or Known Limitation?

[PeteSpillane]

The following code just gives a bucket value of 1 for every row.

CREATE TABLE dbo.ntile_demo (
	v INT NOT NULL
);
	
INSERT INTO dbo.ntile_demo(v) 
VALUES(1),(2),(3),(4),(5),(6),(7),(8),(9),(10);

SELECT 
	v, 
	NTILE (5) OVER (
		ORDER BY v
	) buckets
FROM dbo.ntile_demo;

Running this in SQL Server on-premises gives 1 for the first 2 rows, 2 for the next 2, etc as expected.

Is NTILE not working in Fabric DW?

I can't find it listed as a known limitation of the T-SQL surface area...

https://learn.microsoft.com/en-us/fabric/data-warehouse/tsql-surface-area

[v-gchenna-msft · Accepted solution]

Hello @PeteSpillane ,

Thanks for using Fabric Community.
NILE is not listed as supported on the Microsoft documentation: NTILE (Transact-SQL) - SQL Server | Microsoft Learn

The known limitations isn't a exhaustive list.  i.e. RANK is supported but NTILE is not. They need to check the documentation for the specific command/function. 

Hope this is helpful. Please let me know incase of further queries.

[v-gchenna-msft]

Hi @PeteSpillane ,

We haven’t heard from you on the last response and was just checking back to see if your query was answered.
Otherwise, will respond back with the more details and we will try to help .

[v-gchenna-msft]

Hi @PeteSpillane ,

Glad to know that your query got resolved. Please continue using Fabric Community on your further queries.