• 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
As
Select EmployeeID, max(EmployeeLoginDate)
From dbo.Employee
Where Employee.Status = 1
Group by EmployeeID
Go

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

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.
0
Dre_The_DBA
Asked:
Dre_The_DBA
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.
0
 
LowfatspreadCommented:
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...


 

0
 
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....
0
 
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.
0
 
CleanupPingCommented:
Dre_The_DBA:
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 
EXPERTS:
Post your closing recommendations!  No comment means you don't care.
0

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