0

Ok, I'm trying to make an indexed view that is against a simple table that stores the results of what people think is good/bad for a post. This is the results of a thumbs up / thumbs down, voting on posts.

So here's my pseduo fake table :-

HelpfulPostId INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
PostId INT NOT NULL,
IsHelpful BIT NOT NULL,
UserId INT NOT NULL

So a user can only have one vote per post. It's either a 1 (helpful) or 0 (unhelpful) <-- not sure of a better way to handle that, if there is a better way.

Ok. What i'm trying to do is get a view that looks like the following.

HelpfulPostId INT IDENTITY(1,1) NOT NULL PRIMARY KEY,

PostId INT NOT NULL,
IsHelpfulCount COUNT_BIG (WHERE IsHelpful = 1)
IsNotHelpfulCount COUNT_BIG (WHERE IsHelpful = 0)

And finally, i'll need to make it schemabindable so i can add an index on the PK and then an index on the PostId.

I have no idea about the sql to make the view. Any suggestions?

Cheers :)

Pure.Krome
  • 84,693
  • 113
  • 396
  • 647

1 Answers1

2

Thoughts:

  • You can't use COUNT(*) in an indexed view
  • You can't aggregate bit fields

There are other limitations of indexed views

CREATE VIEW dbo.Example
WITH SCHEMABINDING
AS
SELECT
    PostId,
    SUM(CAST(IsHelpful AS bigint)) AS IsHelpfulCount,
    SUM(CAST(1-IsHelpful AS bigint)) AS IsNotHelpfulCount,
    COUNT_BIG(*) AS Dummy   --Used to satisfy requirement
FROM
    dbo.bob
GROUP BY
    PostId
GO
CREATE UNIQUE CLUSTERED INDEX IXC_Test ON dbo.Example (PostId)
GO

Edit: Removed the Identity field, which was accidently added to the original question/post.

Edit 2 (gbn): I forgot that any aggregate in an indexed view also needs a COUNT_BIG(*). So, simply add one as a dummy column. I've tested this.

If the view definition uses an aggregate function, the SELECT list must also include COUNT_BIG (*).

gbn
  • 422,506
  • 82
  • 585
  • 676
  • Um .. that's not going to work :( How does it know what the count of IsHelpfulCount is?? that's what i need to define, in this view :( Can that be a subquery? – Pure.Krome Jun 22 '09 at 05:12
  • With a little tweaking, it's now good. I had to drop the Indentity field (i accidenly posted that as a result .. which is impossible when i'm doing aggregations :P). I checked against a quick select with 2x subqueries (doing a count) and it's all good :) I'm lucky i'm using 1&0.. so the SUM would work :) – Pure.Krome Jun 22 '09 at 06:00
  • :( when I try to index this view, it doesn't work. Saying i need a COUNT_BIG(*) ??? – Pure.Krome Jun 22 '09 at 06:30
  • .. but it now works (I can add a unique clustered index) when i add the following, to the select statement.. => COUNT_BIG(*) AS TotalCount – Pure.Krome Jun 22 '09 at 07:16
  • cheers. Confirmed also. Works great :) cheers mate for following through with this. – Pure.Krome Jun 22 '09 at 07:31