• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 944
  • Last Modified:

Creating an indexed view using an aggregate (MAX)

I need to create an indexed view like the following

Create view v1
With SchemaBinding
Select EmployeeID, max(EmployeeLoginDate)
From dbo.Employee
Where Employee.Status = 1
Group by EmployeeID

Create Unique Clustered Index ix_v1 on dbo.v1 (EmployeeID)

I can create the view, but when I create the index, I get an error that says "Cannot create view.  It contains one or more disallowed constructs."

Any ideas on possible workarounds?  I am using this info as part of a query that takes forever to run.
1 Solution
Scott PletcherSenior DBACommented:
I'm not sure you'll be able to create an index on a view with MAX().  

But you could create an index on EmployeeId and Login Date; hopefully, that should solve the original query quickly enough, although if there are LOTS of rows per employee per day maybe not.
I'm not aware that you can create indexes on views...
(SQLServer v7)
you can only create indexes on the base tables

create index ix_v1 on dbo.employee (employeeid,status,employeelogindate)

but what indexes exist on the table anyway?

i'd have thought that an existing employeeid index would
give decent results anyway...


Dre_The_DBAAuthor Commented:
With SQL2K, you can create indexes on views.  

I am not actually using an employee table, but I just created a simple table for a simple example to show what I'm trying to do.  The actual view is a lot more complex, joining different tables, etc....
Scott PletcherSenior DBACommented:
You can create indexes on some views, but not necessarily on every view.  I thought that the MAX() prevented that view from being indexed ... but I can't 100% for sure that it does.
This old question needs to be finalized -- accept an answer, split points, or get a refund.  For information on your options, please click here-> http:/help/closing.jsp#1 
Post your closing recommendations!  No comment means you don't care.

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now