I am having an issue with Aggregate Pushdown for float/real data types. According to the documentation, Aggregate Pushdown is supported for "Any datatype <= 64 bits" or 8 bytes ():

  • Supported aggregate operators are MIN, MAX, SUM, COUNT, AVG
  • Any datatype <= 64 bits is supported. For example, bigint is supported as its size is 8 bytes but decimal (38,6) is not because its size is 17 bytes. Also, no string types are supported
  • Aggregate operator must be on top of SCAN node or SCAN node with group by

It doesn't work no matter what I do. I tried making column nullable and not null. Grouping and w/o grouping.

We are running on the latest version of the SQL Server 2016 (SP1-CU3). I wonder anyone experienced the same? It seems like a bug to me. Am I missing anything?

If you experience the same issue, please upvote my SQL Server Feedback Request. Now I am faced with a choice to convert float columns to numeric. But operations with numeric types are slower in general. So I may gain in one place and lose in another. I have successfully tested numeric(15,12).

Here is a script illustrating the issue (please enable Actual Execution plan to see the issue):


CREATE TABLE dbo.TestTable 
     cKey             INT               NOT NULL
   , cGroup           INT               NOT NULL
   , cNumeric36_3     NUMERIC(36, 3)    NULL
   , cNumeric18_3     DECIMAL(18, 3)    NULL
   , cNumeric18_9     DECIMAL(18, 9)    NULL
   , cNumeric15_12    DECIMAL(15, 12)   NULL
   , cMoney           MONEY             NULL
   , cFloat53         FLOAT(53)         NULL
   , cFloat53Less1    FLOAT(53)         NULL
   , cFloat24         FLOAT(24)         NULL
   , cReal            REAL              NULL

;WITH _Numbers0 AS (
    SELECT TOP 3000  column_id  FROM  sys.all_columns 
, _Numbers AS (
    FROM _Numbers0 a CROSS JOIN _Numbers0 b
, _Keys AS (
            cKey = n.cKey
          , Divder10    = CONVERT(INT, FLOOR(CHECKSUM(NewId()) % 10))
          , Divder100   = CONVERT(INT, FLOOR(CHECKSUM(NewId()) % 100))
          , Divder10000 = CONVERT(INT, FLOOR(CHECKSUM(NewId()) % 10000))
    FROM _Numbers n
, _RandomValues AS 
        , cGroup        = ABS(CHECKSUM(NewId())) % 100          
        , cNumeric36_3  = CONVERT(NUMERIC(36,3) , CHECKSUM(NewId())           )                         / CONVERT(NUMERIC(36,3) , NULLIF(Divder10000          , 0.00))
        , cNumeric18_3  = CONVERT(NUMERIC(18,3) , CHECKSUM(NewId())           )                         / CONVERT(NUMERIC(36,3) , NULLIF(Divder100            , 0.00))
        , cNumeric18_9  = CONVERT(NUMERIC(18,9) , CHECKSUM(NewId()) % 1000000 )                         / CONVERT(NUMERIC(36,3) , NULLIF(Divder10000          , 0.00))
        , cNumeric15_12 = CONVERT(NUMERIC(15,12), CHECKSUM(NewId()) % 100     )                         / CONVERT(NUMERIC(36,3) , NULLIF(Divder10000          , 0.00))
        , cMoney        = CONVERT(MONEY, CHECKSUM(NewId()))                                             / CONVERT(MONEY         , NULLIF(Divder10000          , 0.00))
        , cFloat53      = CONVERT(FLOAT, CHECKSUM(NewId())) * CONVERT(FLOAT, CHECKSUM(NewId()))         / CONVERT(FLOAT(53)     , NULLIF(Divder10000          , 0.00))
        , cFloat53Less1 = CONVERT(FLOAT, 1.00)                                                          / CONVERT(FLOAT(53)     , NULLIF(CHECKSUM(NewId())    , 0.00))
        , cFloat24      = CONVERT(FLOAT(24), CHECKSUM(NewId())) * CONVERT(FLOAT(24), CHECKSUM(NewId())) / CONVERT(FLOAT(24)     , NULLIF(Divder10000          , 0.00))
        , cReal         = CONVERT(REAL, CHECKSUM(NewId())) * CONVERT(REAL, CHECKSUM(NewId()))           / CONVERT(REAL          , NULLIF(Divder10000          , 0.00))
    FROM _Keys
INSERT INTO dbo.TestTable 
SELECT *       
FROM _RandomValues


    ON dbo.TestTable WITH (MAXDOP = 4);

FROM dbo.TestTable tt

SELECT MAX(tt.cNumeric36_3) FROM dbo.TestTable tt;
SELECT MAX(tt.cNumeric18_3) FROM dbo.TestTable tt;
SELECT MAX(tt.cNumeric18_9) FROM dbo.TestTable tt;
SELECT MAX(tt.cNumeric15_12) FROM dbo.TestTable tt;
SELECT MAX(tt.cMoney) FROM dbo.TestTable tt;
SELECT MAX(tt.cFloat53) FROM dbo.TestTable tt;
SELECT MAX(tt.cFloat53Less1) FROM dbo.TestTable tt;
SELECT MAX(tt.cFloat24) FROM dbo.TestTable tt;
SELECT MAX(tt.cReal) FROM dbo.TestTable tt;
  • We are on SQL Server 2017-CU17 and there is still no support for floats :(. They also removed my Connect bug, so I created a new one on the Azure feedback site. Commented Oct 29, 2019 at 18:56

1 Answer 1


Microsoft has updated the documentation to say that float is not supported*.

The documentation for aggregate pushdown was inaccurate in some places and it doesn't state all of the restrictions. In some testing that I did a week ago I also found that float types are not supported. In addition to that, numeric(10,0) is supported despite requiring nine bytes of storage. Here's the most accurate summary for supported data types that I'm aware of:

All date and time data types are supported except datetimeoffset. All exact numeric data types are supported if they are under 10 bytes.

If you want to troubleshoot more you may be able to uncover something with the query_execution_dynamic_push_down_statistics extended event.

In case it's of interest, you can read about some of the limitations I've found around aggregate pushdown in my blog post here. Paul White has also written about the details of Grouped Aggregate Pushdown.

* Float and real now appear to work with aggregate pushdown for COUNT and COUNT_BIG only in SQL Server 2017 CU 16 (and maybe earlier). Other previously-unsupported types like datetimeoffset and numeric with precision > 18 also work, so long as the data (in batch format) fits in 64 bits at runtime.


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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