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

Coby Lin 0 Reputation points
2023-05-10T08:48:07.89+00:00

I have COUNT_BIG(**) in the script and I got the "SQL71640: COUNT_BIG(a) is required" error.*

Am I missing something here?

[enter image description here

](https://i.stack.imgur.com/oeAob.png)

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

Visual Studio
Visual Studio
A family of Microsoft suites of integrated development tools for building applications for Windows, the web and mobile devices.
4,765 questions
{count} votes

1 answer

Sort by: Most helpful
  1. Ali Soleyman 0 Reputation points
    2023-10-24T03:32:55.9366667+00:00

    I had same issue in my project for Materialized view in Visual Studio 2022 database project. as I understand from error message visual studio needs to have separate count_big for each Summarized column. after I added extra count_big the errors gone.

    SQL script with error in VS:
    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 below code to resolve the 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

    0 comments No comments