Visual Studio Database Project MATERIALIZED VIEW Error SQL71640: COUNT_BIG(a) is required when using this tool to create a materialized view

Question

I can't find a solution to this one.

I have COUNT_BIG() in the script and I got the "SQL71640: COUNT_BIG(a) is required" error. But when I changed my script to COUNT_BIG(1), the error will be changed to COUNT_BIG() is required...

Am I missing something here?

SQL71640: COUNT_BIG(a) is required when using this tool to create a materialized view that has SUM(a) in the SELECT list.

Sample script:

    CREATE MATERIALIZED VIEW [EDW_DM].[MVW_Fact_Transaction] WITH ( DISTRIBUTION = HASH (H_PlayerHashKey)) AS 
SELECT
   some columns 
   SUM(CAST (ISNULL([Column x], 0) AS MONEY)) AS [Column x],
   SUM(CAST (ISNULL([Column x], 0) AS MONEY)) AS [Column x],
   SUM(CAST (ISNULL([Column x] * [Column x] * 0.0001, 0) AS MONEY)) AS Column x,
   COUNT_BIG(*) AS cb 
FROM
   [EDW_DM].[Fact_Table] 
GROUP BY
    some columns

Answer 1

I tried to reproduce the issue in my environment with your code I got the same error:

I update my Vs2019 VS2019 to the latest version with Visual Studio Installer. After installation I waited for some time and try to run the code it worked successfully without any error. For more information you can refer this.

Answer 2

I had same issue in my project for Materialized view in a Visual Studio 2022 database project. As I understand from the error message, Visual Studio needs to have a separate count_big for each Summarized column. After I added the extra count_big, the error is gone.

SQL script with error in Visual Studio:

CREATE MATERIALIZED VIEW [dbo].[test]

WITH ( DISTRIBUTION = HASH (a))

AS
Select a, b, sum(c) as sum_c, sum(d) as sum_d, count_big(*) as cb

From mytable
group by a, b

When I compile above code, I got this error:

SQL71640: COUNT_BIG(a) is required when using this tool to create a materialized view that has SUM(a) in the SELECT list.

I changed the script to the below code to resolve that error message:

CREATE MATERIALIZED VIEW [dbo].[test]

WITH ( DISTRIBUTION = HASH (a))

AS
Select a, b, sum(c) as sum_c, sum(d) as sum_d, count_big(*) as cb, count_big(c) as cb2, count_big(d) as cb3

From mytable
group by a, b