Say I have parent->child tables setup. Call them Shipment and Widget.

Is there anyway to create an Indexed view that will have the PK of the parent (ShipmentId) and the created when of the newest child (Widget.CreatedWhen).

This is the query I am looking for:

SELECT  widg.ShipmentId, MAX(widg.CreatedWhen) NewestWidget
FROM    dbo.Widget widg            
GROUP BY widg.ShipmentId

Obviously this query cannot be an Indexed View because it has MAX in it.

But is there some other way via "Indexed View Legal" SQL to get a similar result set?


There isn't a way to do this currently, as finding the "latest" widget date will require an aggregate (grouping), ranking function, subquery, or apply; none of which the indexed view will allow in its definition.

However - is there a particular reason you want to have an indexed view? Do you have appropriate indexing on the underlying Widget table? I'm assuming that the reason you want to index the view is so you can pull out the values for a particular Widget ID.

I would index the underlying table on (WidgetID, CreatedWhen DESC) and implement your query as a normal view - or perhaps a inline table valued function if you always want to get the date for a single Widget.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.