CODEWITHSUNDEEP
sql-server
ceth
ceth

Reputation: 45295

Indexing View on Aggregate Field

I am just wondering if there is any way to create index on agregate field?

CREATE TABLE test2 (
id INTEGER,
name VARCHAR(10),
family VARCHAR(10),
amount INTEGER)

CREATE VIEW dbo.test2_v WITH SCHEMABINDING 
AS
SELECT id, SUM(amount) as amount, COUNT_BIG(*) as tmp
FROM dbo.test2 
GROUP BY id

CREATE UNIQUE CLUSTERED INDEX vIdx ON test2_v(amount)

I have next error message with this code:

Cannot create the clustered index "vIdx" on view "test.dbo.test2_v" because the index key includes columns that are not in the GROUP BY clause. Consider eliminating columns that are not in the GROUP BY clause from the index key.

Upvotes: 0

Views: 301

Answers (2)

Chris Diver
Chris Diver

Reputation: 19842

One of the restrictions when creating an unique clustered index on a view is.

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.

http://msdn.microsoft.com/en-us/library/ms188783.aspx

So you firstly need to create a UNIQUE CLUSTERED index on your key, then you can create a NONCLUSTERED index on your amount column. 

CREATE UNIQUE CLUSTERED INDEX vIdx ON test2_v(id)
CREATE NONCLUSTERED INDEX ix_test2_v_amount ON test2_v(amount)

Upvotes: 1

gbn
gbn

Reputation: 432311

Your index needs to include ID most likely. Amount won't be unique enough either

As per the error message

...the index key includes columns that are not in the GROUP BY clause....

Upvotes: 1

Related Questions