SQL Server - indexed view with string_agg

Question

I try to define an indexed view to create full text search index on it. The view itself is created correctly:

CREATE OR ALTER VIEW dbo.my_view WITH SCHEMABINDING AS
    SELECT p.id as protector_id,
           p.name as protector_name,
           string_agg(cast(c.name as nvarchar(max)), ', ') as crops_names,
           count_big(*) as count_big
FROM dbo.protectors p
    INNER JOIN dbo.protectors_crops pc on p.id = pc.protector_id
    INNER JOIN dbo.crops c on pc.crop_id = c.id
GROUP BY p.id, p.name

But when I try to create an index:

CREATE UNIQUE CLUSTERED INDEX my_view_index ON dbo.my_view (protector_id)

i get an error:

[S0001][10125] Cannot create index on view "dbo.my_view" because it uses aggregate "STRING_AGG". Consider eliminating the aggregate, not indexing the view, or using alternate aggregates. For example, for AVG substitute SUM and COUNT_BIG, or for COUNT, substitute COUNT_BIG.

Documentation doesn't state anything about STRING_AGG, neither I can find any solution to replace it.

Answer 1

Read the documentation again.

• If the view definition contains a GROUP BY clause, the key of the unique clustered index can reference only the columns specified in the GROUP BY clause

Don't think string_agg is deterministic - so that is likely another issue. I would skip the inclusion of the name in the view to avoid the extra join and additional overhead. Is Name unique as well or is ID the only guaranteed unique row in your first table? As it stands now, you the tuple <id, name> is unique for your statement.

Answer 2

Although STRING_AGG is not currently listed as a disalowed element in the current documentation, it is indeed not allowed since it is called out explicitly in the error message. Minimal example:

CREATE TABLE dbo.test_agg(
     id int
    ,col varchar(10)
)
GO

CREATE VIEW dbo.vw_test_agg
WITH SCHEMABINDING
AS
SELECT
      id
    , STRING_AGG(col, ',') AS col
    , COUNT_BIG(*) AS CountBig
FROM dbo.test_agg
GROUP BY id;
GO

Msg 10125, Level 16, State 1, Line 21 Cannot create index on view "tempdb.dbo.vw_test_agg" because it uses aggregate "STRING_AGG". Consider eliminating the aggregate, not indexing the view, or using alternate aggregates. For example, for AVG substitute SUM and COUNT_BIG, or for COUNT, substitute COUNT_BIG.

Also, note STRING_AGG is a deterministic function so it's not disallowed for that reason:

SELECT
      name
    , COLUMNPROPERTY(OBJECT_ID(N'dbo.vw_test_agg'), name, 'IsDeterministic') AS IsDeterministic
FROM sys.columns AS c
WHERE
    object_id = OBJECT_ID(N'dbo.vw_test_agg')
    AND name = N'col';

name	IsDeterministic
col	1