asked
Indexed view doesn't allow sub queries
Hi. We have a DB where we store daily records per user. Currently we need to construct reports using the last record of each user every month. In order to improve performance we decided that an indexed clustered view, with the monthly data, would be the best approach. Creating the view worked fine but we couldn't create the clustered index since this doesn't work with views with sub queries. Without the index the view doesn't improve performance and it would make no sense for us. Any idea? The view query is something like this, SELECT idCliente , fecha , valorTotal , navTotal FROM navClientes WHERE fecha IN ( SELECT MAX(fecha) FROM navClientes GROUP BY MONTH(fecha) , YEAR(fecha) ) ORDER BY idCliente , fecha thanks
This is true, Index view has lot of limitations. Subquery, Window function and view are kind of expression and it has to expand them while executing the query. This is internal behavior. SQL does not know the uniqueness of the data so it fails while creating unique clustered index. As you stated that you need "last record of each user every month", even the query given by you would give wrong result as you are grouping based on month and year, then filtering it using fetcha column that seem datetime and there can be a maximum value of date which doesn't exist for any user. In this case, that user will not appear in query result. You can use below query to get result. Also, You can apply filter of userid for which you want to see result and create indexes related to that filter that will give you better performance. With CTE as (SELECT idCliente , fecha , valorTotal , navTotal, Rank() over(partition by idcliente,MONTH(fecha) , YEAR(fecha) order by fecha desc) as Rnk FROM dbo.navClientes nc ) Select idCliente , fecha , valorTotal , navTotal from cte Where Rnk = 1 Thanks, NeerajTripathi
18 People are following this question.
