In Microsoft SQL Server 2008, syntax generates the error "The Parallel Data Warehouse (PDW) features are not enabled."

Question

I have the following virtual column generated from an aggregate over a sorted partition,

MIN(picture_id) OVER ( PARTITION BY [360_set] ORDER BY picture_id ASC )

However, when I execute that, I get the following.

Msg 11305, Level 15, State 10, Line 12
The Parallel Data Warehouse (PDW) features are not enabled.

This is where it gets interesting though, without a sort order on the partition, it works:

MIN(picture_id) OVER ( PARTITION BY [360_set] )

And, further, ROW_NUMBER() a window function (not an aggregate function) works with an explicit order on the partition.

ROW_NUMBER() OVER ( PARTITION BY [360_set] ORDER BY picture_id ASC )

How come the desired statement doesn't work? Where is this documented? The version information was requested, this is what I in Help → About.

Microsoft SQL Server Management Studio          10.0.5512.0
Microsoft Analysis Services Client Tools        10.0.5500.0
Microsoft Data Access Components (MDAC)         6.1.7601.17514
Microsoft MSXML                                 3.0 6.0 
Microsoft Internet Explorer                     9.10.9200.16635
Microsoft .NET Framework                        2.0.50727.5472
Operating System                                6.1.7601

The result from SELECT @@VERSION is Microsoft SQL Server 2008 (SP3) - 10.0.5512.0 (X64) Aug 22 2012 19:25:47 Copyright (c) 1988-2008 Microsoft Corporation Standard Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1) (VM)

Answer 1

The Parallel Data Warehouse (PDW) features are not enabled.

This is a parser bug that exists only in SQL Server 2008. Non-PDW versions of SQL Server before 2012 do not support the ORDER BY clause with aggregate functions like MIN:

Windowing function support was considerably extended in 2012, compared with the basic implementation available starting with SQL Server 2005. The extensions were made available in Parallel Data Warehouse before being incorporated in the box product. Because the various editions share a common code-base, misleading error messages like this are possible.

If you are interested, the call stack when the aggregate is verified by the parser is shown below. Because the aggregate has an OVER clause with ORDER BY, a check for PDW is issued:

This check immediately fails with a parser error:

Luckily, you do not need an windowed aggregate that supports ORDER BY framing to solve your code problem.

Answer 2

This is going to be a result of the merging of the PDW, Azure and box version's code bases. We'll start to see messages like this or that you aren't on an Azure machine when trying to do stuff that has only been released in Azure.

As for Martin's question about what the PDW extensions are, these would be the features of the T-SQL language which are only implemented in the Parallel Data Warehouse (PDW) product.

Answer 3

Answering this as an element of the error message. As @MartinSmith said above,

The order by clause for aggregates wasn't implemented until SQL Server 2012. Not sure what the PDW extensions are. – Martin Smith

This is mentioned officially here SQL Server 2008 R2 - OVER Clause (Transact-SQL)

When used in the context of a ranking window function, can only refer to columns made available by the FROM clause. An integer cannot be specified to represent the position of the name or alias of a column in the select list. cannot be used with aggregate window functions.

Even more significant is the verbiage in SQL Server 2012 - OVER Clause (Transact-SQL)

Depending on the ranking, aggregate, or analytic function used with the OVER clause, and/or the may not be supported.

So, it looks like it certainly is not available in 2008 -- though that error message is a really obscure way of saying "not implemented", and in 2012 it looks like Microsoft's official stance is your mileage may vary.