Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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)

share|improve this question
    
What's the exact version? Seems like a bug. I get a normal syntax error for the first example on 10.50.1600. Aside from that, though, I don't see the point -- what are you trying to do with this syntax? –  Jon Seigel Jul 26 '13 at 23:16
    
@JonSeigel I want ((picture_id - min(picture_id)) % 4 = 0 ). I want to get at every forth picture with the first picture inserted being the starting point. –  Evan Carroll Jul 26 '13 at 23:23
2  
The order by clause for aggregates wasn't implemented until SQL Server 2012. Not sure what the PDW extensions are. –  Martin Smith Jul 26 '13 at 23:44
2  
@JonSeigel It sets the order of rows for the window frame. It does not affect the way MIN works of course, but it does change the rows in the frame over which MIN is performed. –  Paul White Jul 27 '13 at 5:04
1  
@JonSeigel - The default if not specified is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. So the window only includes values with picture_id values less than or equal to that in the current row. –  Martin Smith Jul 27 '13 at 13:08

3 Answers 3

up vote 9 down vote accepted

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:

Books Online extract

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:

Aggregate verification

This check immediately fails with a parser error:

Parser error

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

share|improve this answer
    
even 2012 has the caveat "Depending on the ranking, aggregate, or analytic function used with the OVER clause, and/or the may not be supported." –  Evan Carroll Jul 27 '13 at 20:47
    
@EvanCarroll OVER ... ORDER BY is supported for aggregates in SQL Server 2012. –  Paul White Jul 27 '13 at 22:17
    
Yea, to whatever degree "may not be supported" means "it is supported" –  Evan Carroll Jul 29 '13 at 19:00
    
@EvanCarroll It's just imprecisely worded. All the ranking, aggregate and analytic functions support OVER ... ORDER BY in SQL Server 2012, where it makes logical sense. GROUPING, GROUPING_ID, and CHECKSUM aren't windowing functions; PERCENTILE_CONT and PERCENTILE_DISC use a WITHIN GROUP ... ORDER BY syntax instead. Have you found an true ranking, aggregate, or analytic function that doesn't support OVER ... ORDER BY? Or are you just trying to defend the word 'significant' in your answer? –  Paul White Jul 29 '13 at 23:46
1  
@EvanCarroll Nothing wrong with being skeptical, but support in 2012 really is much better (though we can always ask for more, of course). Books Online is written by technical authors, not the marketing people AFAIK :) Like any work, it does contain errors, large and small. –  Paul White Jul 30 '13 at 0:15

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.

share|improve this answer
    
Can these be enabled through a hack? Or, are they not shipped with the product? I'm just wondering if they seriously pulled that rudimentary feature? It's not that complex for DB use. –  Evan Carroll Jul 27 '13 at 4:39
3  
@EvanCarroll No, ORDER BY with windowed aggregates cannot be enabled in SQL Server 2008. The feature was not 'pulled', it was not released for non-PDW SQL Server until the 2012 release. –  Paul White Jul 27 '13 at 5:06

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.