Indexed Views - Group By Redundancy

[ChrisRath]

I have a table that I want to have a precalulcate length on a character field
and group and sum up. Thought I could do this by creating a view with a group
by clause that includes the sum function. Unfortunately, the compiler
complains with:

A clustered index cannot be created on the view 'MyView' because the index
key includes columns which are not in the GROUP BY clause.

Wish I could verbalize the problem a little better, but the following pared
down example should serve as a demonstration:

SET ANSI_WARNINGS ON
SET ANSI_PADDING ON
SET ANSI_NULLS ON
SET ARITHABORT ON
SET CONCAT_NULL_YIE LDS_NULL ON
SET QUOTED_IDENTIFI ER ON
SET NUMERIC_ROUNDAB ORT OFF
GO

CREATE TABLE myTable(
myID INT NOT NULL,
RecNum INT NOT NULL,
TestString VARCHAR(80) NOT NULL)
GO

INSERT INTO myTable VALUES(1, 1, 'a')
INSERT INTO myTable VALUES(1, 2, 'ab')
INSERT INTO myTable VALUES(2, 2, 'abc')
GO

CREATE VIEW dbo.MyView WITH SCHEMABINDING AS
SELECT
myID = myID,
slen = SUM(LEN(TestStr ing)),
recn = COUNT_BIG(*)
FROM dbo.myTable
GROUP BY myID
GO

CREATE UNIQUE CLUSTERED INDEX IX_MyView ON MyView(myID, slen)
-- A clustered index cannot be created on the view 'MyView' because
-- the index key includes columns which are not in the GROUP BY clause.
GO

DROP VIEW MyView
GO

DROP TABLE myTable
GO
Thanks,
Chris Rathman

[Erland Sommarskog]

[posted and mailed, please reply in news]

ChrisRath (ch*******@aol. com) writes:

Wish I could verbalize the problem a little better, but the following
pared down example should serve as a demonstration:
Very good repro, thanks.

As for your problem, I think the cure is simple. Just change:
CREATE UNIQUE CLUSTERED INDEX IX_MyView ON MyView(myID, slen)
to
CREATE UNIQUE CLUSTERED INDEX IX_MyView ON MyView(myID)

Alas, this rewards with a new message:

Server: Msg 8662, Level 16, State 1, Line 1
An index cannot be created on the view 'MyView' because the view
definition includes an unknown value (the sum of a nullable expression).

A little funny, since the expression is not nullable. But alright, change
the definition to:

CREATE VIEW dbo.MyView WITH SCHEMABINDING AS
SELECT
myID = myID,
slen = SUM(coalesce(LE N(TestString), 0),
recn = COUNT_BIG(*)
FROM dbo.myTable
GROUP BY myID
GO

But the message is the same.

At this point I will have to admit defeat, but I ask around with my
MVP colleauages and Microsoft contacts, to find out if this is a bug
or by design (with an improper error message).

--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[Erland Sommarskog]

[posted and mailed, please reply in news]

ChrisRath (ch*******@aol. com) writes:

I have a table that I want to have a precalulcate length on a character
field and group and sum up. Thought I could do this by creating a view
with a group by clause that includes the sum function.

I have yet to get answer if the error message I got was by design or due
to a bug. However, my MVP colleage Steve Kass offered a working solution,
although it requires an auxillary table:

SET ANSI_WARNINGS ON
SET ANSI_PADDING ON
SET ANSI_NULLS ON
SET ARITHABORT ON
SET CONCAT_NULL_YIE LDS_NULL ON
SET QUOTED_IDENTIFI ER ON
SET NUMERIC_ROUNDAB ORT OFF
GO

CREATE TABLE myTable(
myID INT NOT NULL,
RecNum INT NOT NULL,
TestString VARCHAR(80) NOT NULL,
blafs int NOT NULL)
GO
SELECT TOP 8000 Number = IDENTITY(int, 1, 1)
INTO Numbers
FROM pubs..authors t1, pubs..authors t2, pubs..authors t3
go
INSERT INTO myTable VALUES(1, 1, 'a', 9)
INSERT INTO myTable VALUES(1, 2, 'ab', 9)
INSERT INTO myTable VALUES(2, 2, 'abc', 12)
GO

CREATE VIEW dbo.MyView WITH SCHEMABINDING AS
SELECT
myID = m.myID,
slen = SUM(N.Number),
recn = COUNT_BIG(*)
FROM dbo.myTable m
JOIN dbo.Numbers N ON N.Number = LEN(m.TestStrin g)
GROUP BY m.myID
GO

CREATE UNIQUE CLUSTERED INDEX IX_MyView ON MyView(myID)
-- Server: Msg 8662, Level 16, State 1, Line 1
-- An index cannot be created on the view 'MyView' because the view
-- definition includes an unknown value (the sum of a nullable
-- expression).
GO
DROP VIEW MyView
GO
DROP TABLE myTable
GO
DROP TABLE Numbers
GO
--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[ChrisRath]

I've got a pivot table I use for other purposes that will come in handy. Seems
to compile ok. Will run some performance tests on the target to see if I get
the desired boost.

Thanks for the help. And thank your colleague for me for providing a creative
solution. :-)

CREATE VIEW dbo.MyView WITH SCHEMABINDING AS
SELECT
myID = m.myID,
slen = SUM(N.Number),
recn = COUNT_BIG(*)
FROM dbo.myTable m
JOIN dbo.Numbers N ON N.Number = LEN(m.TestStrin g)
GROUP BY m.myID
GO