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?
Use
TRY_CAST
instead ofCAST
to avoid conversion errors. The resultant value will beNULL
for invalid integer values. Also, add aNOEXPAND
hint so to use the view index: