Here's the scenario... I have a large table with a PK Clustered index on and INT (ID) column I create a View on this table which returns the columns from the table and also a derived rank column by using the ROW_NUMBER() OVER PARTITION ordered by the INT(ID) column When I query the viewed and enter a fixed value for the ID column (example 45) it uses the PK and does an Index Seek which is very fast. However if I put this value in a temp table or normal table and then join this table to the view, the optimizer does an Index Scan. It does not seem to be clever enough to retrieve the value of 45 and the use this value to do a seek It there a way I can force it to do a seek because for a table with millions of rows the query takes a very very long time :-( Is this normal behaviour and if so why? You can reproduce this below.Thanks in advance -- table of data grouped by smallid, with a bigid CREATE TABLE FOO ( smallid int, bigid int, data char(4), primary key (smallid, bigid) ) GO -- populate it with some data DECLARE @i int SET @i = 1 WHILE (@i < 1000000) BEGIN INSERT FOO VALUES (@i, @i%100, 'DATA') SET @i = @i + 1 END -- view involves a partitioned row number over the bigid CREATE VIEW BAR AS SELECT smallid, bigid, ROW_NUMBER() OVER (PARTITION BY smallid ORDER BY bigid) as _rank FROM FOO GO -- just in case... update statistics FOO -- this does a clustered index seek (sweet) select * from BAR where smallid = 45 -- this doesn't..... create table #tmp (id int primary key) insert #tmp values (45) -- clustered index SCAN!!!!! WHY!? select * from #tmp inner join BAR on #tmp.id = BAR.smallid -- or try select * from BAR inner join #tmp on BAR.smallid = #tmp.id
In order to have a SEEK, you need to use indexed column in the WHERE clause rather than joining it with other table
Welcome to the forums. What is the service pack level on SQL 2008 instance?Also what kind of problem you are having by running index scan, it is very clear that there is a very big difference between the query costs when the index is created on the table. In fact, there is a very simple explanation of this behavior in the white paper Improving Performance with SQL Server 2008 Indexed Views. The reason for this behavior is that the query optimizer works when it checks for the index on view and finds that the execution plan of the query is similar to that of the view.