On Microsoft SQL Server 2012, I would like to materialise the result of the row_number
function, because I hope it makes queries running faster. I considered to make a view index, or to add a persisted computed column to the relevant table. I fail with both. How can I achieve what I want?
As example, let us look at an address table with multiple addresses per person.
View index
create view nrview with schemabinding as
select
row_number() over (
partition by person_id
order by valid_from) nr,
address_id,
person_id,
valid_from
from address
create unique clustered index ix
on nrview (
address_id,
nr,
valid_from,
person_id)
This fails with error message 10143:
Cannot create index on view "mydatabase.dbo.nrview" because it contains a ranking or aggregate window function. Remove the function from the view definition or, alternatively, do not index the view.
Computed column
alter table address
add nr as
row_number() over (
partition by person_id
order by valid_from)
persisted
This fails with the error message 4108:
Windowed functions can only appear in the SELECT or ORDER BY clauses.
person_id
value(s)) every time any row is changed, added or deleted. I suggest you optimize elsewhere before assuming this will make queries faster. For example, why not post the table structure, indexes, and an actual post-execution plan where you believeROW_NUMBER()
is the main problem? I bet an index change will solve that better than persisting the result of the expression.row_number
is my main problem because creating a table instead of a view and indexing it, as above, reduces the query time from hours to seconds. (And I don’t this is just a matter of indexes. Also in the original query, only indexes very used, no table scans, and no sorting had to be done in the execution.)