OR and index creation-CodePudding

I use SQL Azure and try to create nonclustered index

CREATE NONCLUSTERED INDEX IX_SyncNotNull ON  MyTable 
    (IMEI, Sync1, Sync2, FieldA, FieldB, FieldC)  
    WHERE (Sync1 = 1 OR Sync2 = 1);

But I get an error:

Incorrect syntax near the keyword 'OR'.

I google and found, that no possible to use OR for index WHERE condition, but how to replace it?

I tried

CREATE NONCLUSTERED INDEX IX_SyncNotNull ON  Device 
    (IMEI, Id, Name, DeviceModelId, Status, SyncVerizon, SyncSureMdm, CarrierId, LastConnectionDate)  
    WHERE (1 in (SyncVerizon, SyncSureMdm));

but it's also incorrect statement

ADDED

Found solution like:

WHERE CAST(SyncSureMdm AS INT)   CAST([SyncVerizon] AS INT) > 0

but will be this effectively, with type conversion?

CodePudding user response：

Such expressions are disallowed by the syntax for filtered index.

You could instead create an indexed view, which will behave much like a filtered index, although they do have some restrictions, primarily in your case that they need to be schema-bound.

CREATE OR ALTER VIEW dbo.View_SyncNotNull
WITH SCHEMABINDING
AS
SELECT
  t.IMEI,
  t.Sync1,
  t.Sync2,
  t.FieldA,
  t.FieldB,
  t.FieldC
FROM dbo.MyTable t
WHERE (t.Sync1 = 1 OR t.Sync2 = 1);

CREATE UNIQUE CLUSTERED INDEX IX_SyncNotNull ON View_SyncNotNull
    (Id);

Choose your unique clustering key carefully: in your case it should probably be the primary key of the base table. All other columns in the view will be included automatically, like any other clustered index.

If you are not using Enterprise/Developer Edition, you should modify relevant queries to access this view directly, using the WITH (NOEXPAND) hint. And even on Enterprise Edition this is worthwhile.