5

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'

enter image description here

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
  • The other side expects the data type of the ID column is NVARCHAR. I have to meet this conditions, unfortunately. Or I've been told to meet them. Probably their application would fail if it's not.
9

The issue is that your query through the view is the same as doing

SELECT *
FROM SomeTable
WHERE CAST(SomeTable.ID as NVARCHAR(10)) = N'1'

Pretty much any CAST of a column in a predicate will render that predicate unsargable. The only exceptions that I am aware of are a CAST of datetime column to date and VARCHAR to NVARCHAR under some collations.

There is no such exception for NVARCHAR to INT.

You might hope that it would do something like

WHERE SomeTable.ID = TRY_CAST('Your search string' as int)

but it is not as simple as that. If your search string is '1' the two would return the same results but for the search string '¹' (superscript 1) the casting to int fails but the string comparison compares equal under some collations. Conversely for the search string ' 1' (with leading space) the int cast and comparison would discard the leading space and compare equal but the string comparison would compare unequal (and similarly with empty string which is cast to 0 when converted to int)

Possible solutions

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';

enter image description here

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.
  • Many thanks. May I circumvent NOEXPAND problem with view over view? NOEXPAND clause will be inside "inner" while "outer" view will refer to "inner" one. Will that works? I could test, thought, but you know...it's easier to ask :-) – jerik1 Apr 29 at 18:21
  • 1
    The NOEXPAND would need to be next to the name of the view that is indexed. So would need to be in the outer view assuming the inner one was the indexed view. But if you can add a wrapper view of a different name I don't understand why this still needs to call the problem view with the CAST – Martin Smith Apr 29 at 18:25
4

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:

enter image description here

If the application absolutely requires that the data type of the ID column in the view is reported as NVARCHAR (perhaps for binding purposes) you could modify the view definition further so that ID in the select list is defined as CAST(ID AS NVARCHAR(10)) AS ID:

ALTER VIEW dbo.ThirdPartyView
AS
SELECT
    ID = CONVERT(nvarchar(10), ID),
    C1 = SomeColumn1,
    C2 = SomeColumn2
FROM SomeTable;
  • 1
    Well, sometimes you just can't change 3rd party SW and their documentation where they rely on pre-defined / kinda "universal" structure and data types of the views. – jerik1 Apr 29 at 18:11
  • 2
    @jerik1 Of course, but adding an indexed view will also require changing the view definition, so... if that was your concern it wouldn't be a consideration in your question. And you pay this 3rd party, right? This is brain-dead basic stuff and they should be giving you some justification for why they are doing this absolutely wrong, or they should fix their folly. – Aaron Bertrand Apr 29 at 18:13
  • The definition (SELECT) of the view is in my hands but the structure and data types not. May I circumvent NOEXPAND problém with view over view, thought? – jerik1 Apr 29 at 18:18
  • @jerik1 If the definition of the view is in your hands, then as my answer suggests, all you have to do is remove the unnecessary CAST. You are overthinking the problem. – Aaron Bertrand Apr 29 at 18:21
  • But the other side expects the data type of the ID column is NVARCHAR. I have to meet this conditions, unfortunately. Or I've been told to meet them. Probably their application would fail if it's not. Otherwise I would not ask this question at all. – jerik1 Apr 29 at 18:26

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.