How do I Index this View Properly?-CodePudding

I have a table similar to this:

CREATE TABLE dbo.SomeTable (Work_ID VARCHAR(9));

I need to be able to run the following query and unfortunately can not change the data type of the Work_ID column:

SELECT Work_ID
FROM dbo.SomeTable
WHERE WorkID >= 100 AND WorkID <=200

This of course will give me an implicite conversion and cause a table scan (several million rows).

My thought was to put the following indexed view on it.

CREATE VIEW [dbo].[vw_Work_ID]
WITH SCHEMABINDING AS
        SELECT  CAST(q.Work_ID as INT) as Work_ID 
        FROM dbo.SomeTable q
GO
CREATE UNIQUE CLUSTERED INDEX [cl_vw_Work_ID] ON [dbo].[vw_Work_ID]
(
    [Work_ID] ASC
)
GO

When I now run

SELECT Work_ID FROM dbo.vw_Work_ID WHERE WorkID >= 100 AND WorkID <=200
``
I still get IMPLICIT CONVERSION and a table scan.  Any solutions?

CodePudding user response：

Use TRY_CAST instead of CAST to avoid conversion errors. The resultant value will be NULL for invalid integer values. Also, add a NOEXPAND hint so to use the view index:

CREATE TABLE dbo.SomeTable (Work_ID VARCHAR(9));
GO
CREATE VIEW [dbo].[vw_Work_ID]
WITH SCHEMABINDING AS
        SELECT  TRY_CAST(q.Work_ID as INT) as Work_ID 
        FROM dbo.SomeTable q;
GO
CREATE UNIQUE CLUSTERED INDEX [cl_vw_Work_ID] ON [dbo].[vw_Work_ID]
(
    [Work_ID] ASC
);
GO
INSERT INTO dbo.SomeTable VALUES('111');
INSERT INTO dbo.SomeTable VALUES('xxx');
GO
SELECT * 
FROM [dbo].[vw_Work_ID] WITH(NOEXPAND) 
WHERE Work_ID = 0;
GO

CodePudding user response：

We can index on the column using the same WHERE conditions as we want to use in the query. When we check with XML Stastics on we see that the query has been run on the index and has not done a table scan. Please see the dbFiddle link for confirmation of the query plan.

SET STATISTICS XML ON;

CREATE TABLE dbo.SomeTable (Work_ID VARCHAR(9));

SELECT * FROM dbo.SomeTable
WHERE Work_ID >= '100' AND Work_ID <='200';

The query plan shows a full table scan

create index [cl_vw_Work_ID]
on [dbo].[SomeTable](Work_ID)
WHERE Work_ID >= '100' AND Work_ID <='200';

SELECT * FROM dbo.SomeTable
WHERE Work_ID >= '100' AND Work_ID <='200';

The query plan shows an index scan and no table scan

db<>fiddle here

UPDATE following the comment that the values 100 and 200 are not fixed I have tried creating a virtual column casting the Work_ID to int and created an index on it. The query plan is showing a full table scan, even when I insert 4000 rows.

ALTER TABLE dbo.SomeTable
ADD num_work_id AS  CAST(Work_ID AS INT)
;

create index [cl_vw_Work_ID]
on [dbo].[SomeTable](num_work_id)
;

SELECT * FROM dbo.SomeTable
WHERE num_work_id >= '100' AND num_work_id <='200';

Work_ID | num_work_id :------ | ----------:

db<>fiddle here

with 4000 rows db<>fiddle here