Ttyjukl

Assume I have a table:

-- just for test purposes

CREATE TABLE SomeTable (

    ID INT IDENTITY(1,1) NOT NULL CONSTRAINT PK__SomeTable__ID PRIMARY KEY CLUSTERED

    ,SomeColumn1 NVARCHAR(50) NULL

    ,SomeColumn2 DATETIME NULL

    );



-- populate table with some rows

INSERT INTO SomeTable DEFAULT VALUES;

GO 1000

Because a third-party application there is a view converting ID column of a table from INT to NVARCHAR (assume it's a must):

CREATE VIEW ThirdPartyView AS

SELECT

    ID = CAST(ID as NVARCHAR(10))

    ,C1 = SomeColumn1

    ,C2 = SomeColumn2

FROM SomeTable;

Then when I access one row by ID, I get an INDEX SCAN:

SELECT *

FROM ThirdPartyView

WHERE ID = N'1'

I understand why.

What can I do to get INDEX SEEK outside of a query?

EDITORIAL / Circumstances:

• base table (SomeTable) definition could NOT be changed (cannot add columns)


• view has to have the same columns (cannot add columns)


• define any indexes is possible


• I can make the view an indexed view but prefer to avoid that option

In general you could create either an indexed view or a new index on the base table referencing a computed column but both seem quite suboptimal compared with removing the CAST from the view so the existing index can be seeked.

Computed Column

You could create a computed column on SomeTable with definition CAST(ID as NVARCHAR(10)) and then index that.

ALTER TABLE SomeTable ADD strID AS CAST(ID as NVARCHAR(10));    



CREATE INDEX IX ON SomeTable (strID ) INCLUDE (ID, SomeColumn1, SomeColumn2);



SELECT *

FROM ThirdPartyView

WHERE ID = N'1';

Indexed view

Given the restrictions in the question the computed column idea seems ruled out. An alternative will be to create an indexed view but likely you will need to change the query text to get this to work.

• In all editions except Enterprise Edition the NOEXPAND hint will be needed to get the indexed view to be matched.


• If you are on Enterprise Edition the automatic matching in principle would work whether you indexed the original view or created a copy...


• ... but index view matching will only be considered at later stages of optimisation. If the query is cheap enough optimisation will end before it gets to that point. In your case in particular you will also need to fight against trivial plan. I added a million rows of data to the table and the indexed view still wasn't hit - as the plan was below the cost threshold for parallelism and it didn't proceed to further optimisation phases beyond trivial.

Why don't you alter the 3rd party view, or rename it and create your own, so that it doesn't perform this silly convert? If you can add an index to the view without breaking your support agreement, surely you can change the definition of the view.

ALTER VIEW dbo.ThirdPartyView

AS

SELECT ID

    ,C1 = SomeColumn1

    ,C2 = SomeColumn2

FROM SomeTable;

Thanks to data type precedence rules, your query doesn't have to change to get a seek, but you should change it anyway:

SELECT ID, C1, C2

FROM dbo.ThirdPartyView

WHERE ID = N'1';

Plan: