mercredi 7 janvier 2015

Materialise row-number column


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.






Aucun commentaire:

Enregistrer un commentaire