July 25, 2009 at 9:27 am
create VIEW myview725 with SCHEMABINDING
AS
select t.id3, originalresume, email1, Email2, email3, Email4, Email5, email6, Email7, Email8, Email9, Email10, currentdateout from
(select id3, originalresume, email1, Email2, email3, Email4, Email5, email6, Email7, Email8, Email9, Email10, currentdateout, row_number() over(partition by email1 order by id3) as rowno from dbo.OutputResume3) t
where t.rowno = 1
I have created a View based on the above code. I am unable to create any indexes for ID3, or Email1, or currentdateout due to a derived View error. Is this even possible ?
Thanks
July 25, 2009 at 10:28 am
You can create an indexed view, but it cannot contain a derived table (http://technet.microsoft.com/en-us/library/cc917715.aspx).
I'm not sure how you could create an indexed view here. You might want to create a view of the derived table, but I think that a view of a view doesn't work with indexing because it cannot easily be materialized.
July 25, 2009 at 11:47 am
Thanks
July 26, 2009 at 2:21 am
Even if you break the view up, the window function (ROW_NUMBER) will prevent it from being indexed.
You can't use MIN, MAX, ROW_NUMBER = 1 or similar tricks with indexed views.
There may be a way to achieve what you want, but we would need more details...
Paul
July 26, 2009 at 11:07 am
Paul,
What other information do you need?
Thanks
July 26, 2009 at 3:58 pm
This excellent short article by Jeff Moden will give you a good idea:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
Thanks
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply