Dre_The_DBA
asked on
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.
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.
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,employe elogindate )
but what indexes exist on the table anyway?
i'd have thought that an existing employeeid index would
give decent results anyway...
(SQLServer v7)
you can only create indexes on the base tables
create index ix_v1 on dbo.employee (employeeid,status,employe
but what indexes exist on the table anyway?
i'd have thought that an existing employeeid index would
give decent results anyway...
ASKER
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....
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....
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
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.
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.