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.

score 2 · Answer 1 · answered Jan 20 '21 at 12:57

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

answered Jan 20 '21 at 12:57

Dan Guzman

43,250
3
46
71

Seems like the documentation requires an update. :) – Thom A Jan 20 '21 at 12:59
That's annoying... Also, you would think it is not deterministic unless it had `WITHIN GROUP (ORDER BY...`, but no. – Charlieface Jan 20 '21 at 13:19
@Larnu, yea, I just submitted a pull request. – Dan Guzman Jan 20 '21 at 13:42
@DanGuzman thank you for the elegant answer, but it doesn't get me closer to solve my problem - is there any replacement (as in the case of AVG() for example)? – r34 Jan 20 '21 at 13:58
Can't help with `STRING_AGG` but `AVG` can be expressed as `SUM(YourColumn)/COUNT_BIG(*)`. Indexed views have a number of restrictions baked into the product for various reasons. – Dan Guzman Jan 20 '21 at 14:08

SMor · Answer 2 · 2021-01-20T12:35:58.343

-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.

edited Jan 20 '21 at 12:35

answered Jan 20 '21 at 12:30

SMor

2,830
4
11
14

Additionally, `STRING_AGG` is a disallowed aggregate function for an indexed view, albeit not yet documented. – Dan Guzman Jan 20 '21 at 12:33
1

@SMor - I reference protector_id which is specified in GROUP BY clause? What's wrong? Both id and name are unique, but I need both of them. – r34 Jan 20 '21 at 12:38
*"Don't think string_agg is deterministic"* According to the documentation it *is* in this context: [Deterministic and Nondeterministic Functions](https://learn.microsoft.com/en-us/sql/relational-databases/user-defined-functions/deterministic-and-nondeterministic-functions?view=sql-server-ver15): *"All aggregate functions are deterministic unless they are specified with the OVER and ORDER BY clauses. For a list of these functions, see [Aggregate Functions (Transact-SQL)](https://learn.microsoft.com/en-us/sql/t-sql/functions/aggregate-functions-transact-sql?view=sql-server-ver15)."* – Thom A Jan 20 '21 at 12:44
Though, I would suggest that without an `ORDER BY` `STRING_AGG` is **not** deterministic, as the order in which the rows are aggregated *could* be different each time (though the above point about it then becoming not deterministic still wouldn't apply, as it doesn't use the `OVER` clause). – Thom A Jan 20 '21 at 12:54

SQL Server - indexed view with string_agg

2 Answers2