6 added 1 characters in body
source | link

In general it is not possible to use an index seek on a condition x <> 1 and y <> 1.

With an index on x,y the best you can do is convert it into two range seeks (x < 1 and x > 1) with a residual) with a residual predicate on y predicate<> on1y <> 1` (and this wouldn't be able to use additional index key columns to avoid a sort)

For a bit column as it can only have three values. 0, 1, NULL logically WHERE bit_column <> 1 is equivalent to WHERE bit_column = 0 but seems SQL Server doesn't take advantage of that here and convert the <> to = conditions for you.

Adding a couple of check constraints does the job though even though these are apparently redundant in that they don't actually restrict the allowable values for the datatype in any way (for NULL if a check constraint evaluates to UNKNOWN it counts as passing)

CREATE TABLE MyTable
  (
     Foo     INT,
     IsFlag1 BIT NULL CHECK (IsFlag1 IN (0, 1)),
     IsFlag2 BIT NULL CHECK (IsFlag2 IN (0, 1)),
     SomeId  INT
  );

CREATE NONCLUSTERED INDEX ix
  ON MyTable(IsFlag1, IsFlag2, SomeId)
  INCLUDE (Foo); 

The plan now does show a seek on IsFlag1 = 0 AND IsFlag2 = 0

plan 1

Or alternatively this filtered index also avoids the need for a SORT

CREATE NONCLUSTERED INDEX ix 
           ON MyTable(SomeId) 
           INCLUDE (Foo,IsFlag1, IsFlag2) 
           WHERE IsFlag1 != 1 and IsFlag2 != 1

It does a scan of the filtered index (the qualifying rows ordered by SomeId) with a TOP to stop scanning after the 1,000 rows are retrieved. IsFlag1, IsFlag2 are INCLUDE-d in the index to avoid an unnecessary look up that occurs without this.

plan 2

In general it is not possible to use an index seek on a condition x <> 1 and y <> 1.

With an index on x,y the best you can do is convert it into two range seeks (x < 1 and x > 1) with a residual predicate ony <> 1` (and this wouldn't be able to use additional index key columns to avoid a sort)

For a bit column as it can only have three values. 0, 1, NULL logically WHERE bit_column <> 1 is equivalent to WHERE bit_column = 0 but seems SQL Server doesn't take advantage of that here and convert the <> to = conditions for you.

Adding a couple of check constraints does the job though even though these are apparently redundant in that they don't actually restrict the allowable values for the datatype in any way (for NULL if a check constraint evaluates to UNKNOWN it counts as passing)

CREATE TABLE MyTable
  (
     Foo     INT,
     IsFlag1 BIT NULL CHECK (IsFlag1 IN (0, 1)),
     IsFlag2 BIT NULL CHECK (IsFlag2 IN (0, 1)),
     SomeId  INT
  );

CREATE NONCLUSTERED INDEX ix
  ON MyTable(IsFlag1, IsFlag2, SomeId)
  INCLUDE (Foo); 

The plan now does show a seek on IsFlag1 = 0 AND IsFlag2 = 0

plan 1

Or alternatively this filtered index also avoids the need for a SORT

CREATE NONCLUSTERED INDEX ix 
           ON MyTable(SomeId) 
           INCLUDE (Foo,IsFlag1, IsFlag2) 
           WHERE IsFlag1 != 1 and IsFlag2 != 1

It does a scan of the filtered index (the qualifying rows ordered by SomeId) with a TOP to stop scanning after the 1,000 rows are retrieved. IsFlag1, IsFlag2 are INCLUDE-d in the index to avoid an unnecessary look up that occurs without this.

plan 2

In general it is not possible to use an index seek on a condition x <> 1 and y <> 1.

With an index on x,y the best you can do is convert it into two range seeks (x < 1 and x > 1) with a residual predicate on y <> 1 (and this wouldn't be able to use additional index key columns to avoid a sort)

For a bit column as it can only have three values. 0, 1, NULL logically WHERE bit_column <> 1 is equivalent to WHERE bit_column = 0 but seems SQL Server doesn't take advantage of that here and convert the <> to = conditions for you.

Adding a couple of check constraints does the job though even though these are apparently redundant in that they don't actually restrict the allowable values for the datatype in any way (for NULL if a check constraint evaluates to UNKNOWN it counts as passing)

CREATE TABLE MyTable
  (
     Foo     INT,
     IsFlag1 BIT NULL CHECK (IsFlag1 IN (0, 1)),
     IsFlag2 BIT NULL CHECK (IsFlag2 IN (0, 1)),
     SomeId  INT
  );

CREATE NONCLUSTERED INDEX ix
  ON MyTable(IsFlag1, IsFlag2, SomeId)
  INCLUDE (Foo); 

The plan now does show a seek on IsFlag1 = 0 AND IsFlag2 = 0

plan 1

Or alternatively this filtered index also avoids the need for a SORT

CREATE NONCLUSTERED INDEX ix 
           ON MyTable(SomeId) 
           INCLUDE (Foo,IsFlag1, IsFlag2) 
           WHERE IsFlag1 != 1 and IsFlag2 != 1

It does a scan of the filtered index (the qualifying rows ordered by SomeId) with a TOP to stop scanning after the 1,000 rows are retrieved. IsFlag1, IsFlag2 are INCLUDE-d in the index to avoid an unnecessary look up that occurs without this.

plan 2

5 added 523 characters in body
source | link

In general it is not possible to use an index seek on a condition x !=<> 1 and y !=<> 1. 

With an index on x,y the best you can do is convert it into two range seeks with residual predicates (x < 1 and y != 1 and   x > 1) andwith ya !=residual 1predicate on.y <> 1` (and this wouldn't be able to use additional index key columns to avoid a sort)

For a bit column as it can only have three values. 0, 1, NULL logically WHERE bit_column !=<> 1 is equivalent to WHERE bit_column = 0 but seems SQL Server doesn't take advantage of that here and convert the <> to = conditions for you.

Adding a couple of check constraints does the job though even though these are apparently redundant in that they don't actually restrict the allowable values for the datatype in any way (for NULL if a check constraint evaluates to UNKNOWN it counts as passing)

CREATE TABLE MyTable
  (
     Foo     INT,
     IsFlag1 BIT NULL CHECK (IsFlag1 IN (0, 1)),
     IsFlag2 BIT NULL CHECK (IsFlag2 IN (0, 1)),
     SomeId  INT
  );

CREATE NONCLUSTERED INDEX ix
  ON MyTable(IsFlag1, IsFlag2, SomeId)
  INCLUDE (Foo); 

The plan now does show a seek on IsFlag1 = 0 AND IsFlag2 = 0

plan 1

Or alternatively this filtered index also avoids the need for a SORT

CREATE NONCLUSTERED INDEX ix 
           ON MyTable(SomeId) 
           INCLUDE (Foo,IsFlag1, IsFlag2) 
           WHERE IsFlag1 != 1 and IsFlag2 != 1

It does a scan of the filtered index (the qualifying rows ordered by SomeId) with a TOP to stop scanning after the 1,000 rows are retrieved. IsFlag1, IsFlag2 are INCLUDE-d in the index to avoid an unnecessary look up that occurs without this.

plan 2

In general it is not possible to use an index seek on a condition x != 1 and y != 1. With an index on x,y the best you can do is convert it into two range seeks with residual predicates x < 1 and y != 1 and x > 1 and y != 1.

For a bit column as it can only have three values. 0, 1, NULL logically WHERE bit_column != 1 is equivalent to WHERE bit_column = 0 but seems SQL Server doesn't take advantage of that here and convert the <> to = conditions for you.

Adding a couple of check constraints does the job though even though these are apparently redundant in that they don't actually restrict the allowable values for the datatype in any way (for NULL if a check constraint evaluates to UNKNOWN it counts as passing)

CREATE TABLE MyTable
  (
     Foo     INT,
     IsFlag1 BIT NULL CHECK (IsFlag1 IN (0, 1)),
     IsFlag2 BIT NULL CHECK (IsFlag2 IN (0, 1)),
     SomeId  INT
  );

CREATE NONCLUSTERED INDEX ix
  ON MyTable(IsFlag1, IsFlag2, SomeId)
  INCLUDE (Foo); 

The plan now does show a seek on IsFlag1 = 0 AND IsFlag2 = 0

Or alternatively this filtered index also avoids the need for a SORT

CREATE NONCLUSTERED INDEX ix 
           ON MyTable(SomeId) 
           INCLUDE (Foo,IsFlag1, IsFlag2) 
           WHERE IsFlag1 != 1 and IsFlag2 != 1

It does a scan of the filtered index (the qualifying rows ordered by SomeId) with a TOP to stop scanning after the 1,000 rows are retrieved.

In general it is not possible to use an index seek on a condition x <> 1 and y <> 1. 

With an index on x,y the best you can do is convert it into two range seeks (x < 1 and   x > 1) with a residual predicate ony <> 1` (and this wouldn't be able to use additional index key columns to avoid a sort)

For a bit column as it can only have three values. 0, 1, NULL logically WHERE bit_column <> 1 is equivalent to WHERE bit_column = 0 but seems SQL Server doesn't take advantage of that here and convert the <> to = conditions for you.

Adding a couple of check constraints does the job though even though these are apparently redundant in that they don't actually restrict the allowable values for the datatype in any way (for NULL if a check constraint evaluates to UNKNOWN it counts as passing)

CREATE TABLE MyTable
  (
     Foo     INT,
     IsFlag1 BIT NULL CHECK (IsFlag1 IN (0, 1)),
     IsFlag2 BIT NULL CHECK (IsFlag2 IN (0, 1)),
     SomeId  INT
  );

CREATE NONCLUSTERED INDEX ix
  ON MyTable(IsFlag1, IsFlag2, SomeId)
  INCLUDE (Foo); 

The plan now does show a seek on IsFlag1 = 0 AND IsFlag2 = 0

plan 1

Or alternatively this filtered index also avoids the need for a SORT

CREATE NONCLUSTERED INDEX ix 
           ON MyTable(SomeId) 
           INCLUDE (Foo,IsFlag1, IsFlag2) 
           WHERE IsFlag1 != 1 and IsFlag2 != 1

It does a scan of the filtered index (the qualifying rows ordered by SomeId) with a TOP to stop scanning after the 1,000 rows are retrieved. IsFlag1, IsFlag2 are INCLUDE-d in the index to avoid an unnecessary look up that occurs without this.

plan 2

4 added 196 characters in body
source | link

In general it is not possible to use an index seek on a condition x != 1 and y != 1. With an index on x,y the best you can do is convert it into two range seeks with residual predicates x < 1 and y != 1 and x > 1 and y != 1.

For a bit column as it can only have three values. 0, 1, NULL logically WHERE bit_column != 1 is equivalent to WHERE bit_column = 0 but seems SQL Server doesn't take advantage of that here and convert the <> to = conditions for you.

Adding a couple of apparently redundant check constraints does the job though. even though these are apparently redundant in that they don't actually restrict the allowable values for the datatype in any way (for NULL if a check constraint evaluates to UNKNOWN it counts as passing)

CREATE TABLE MyTable
  (
     Foo     INT,
     IsFlag1 BIT NULL CHECK (IsFlag1 IN (0, 1)),
     IsFlag2 BIT NULL CHECK (IsFlag2 IN (0, 1)),
     SomeId  INT
  );

CREATE NONCLUSTERED INDEX ix
  ON MyTable(IsFlag1, IsFlag2, SomeId)
  INCLUDE (Foo); 

The plan now does show a seek on IsFlag1 = 0 AND IsFlag2 = 0

Or alternatively this filtered index also avoids the need for a SORT

CREATE NONCLUSTERED INDEX ix 
           ON MyTable(SomeId) 
           INCLUDE (Foo,IsFlag1, IsFlag2) 
           WHERE IsFlag1 != 1 and IsFlag2 != 1

It does a scan of the filtered index (the qualifying rows ordered by SomeId) with a TOP to stop scanning after the 1,000 rows are retrieved.

In general it is not possible to use an index seek on a condition x != 1 and y != 1. With an index on x,y the best you can do is convert it into two range seeks with residual predicates x < 1 and y != 1 and x > 1 and y != 1.

For a bit column as it can only have three values. 0, 1, NULL logically WHERE bit_column != 1 is equivalent to WHERE bit_column = 0 but seems SQL Server doesn't take advantage of that here and convert the <> to = conditions for you.

Adding a couple of apparently redundant check constraints does the job though.

CREATE TABLE MyTable
  (
     Foo     INT,
     IsFlag1 BIT CHECK (IsFlag1 IN (0, 1)),
     IsFlag2 BIT CHECK (IsFlag2 IN (0, 1)),
     SomeId  INT
  );

CREATE NONCLUSTERED INDEX ix
  ON MyTable(IsFlag1, IsFlag2, SomeId)
  INCLUDE (Foo); 

The plan now does show a seek on IsFlag1 = 0 AND IsFlag2 = 0

Or alternatively this filtered index also avoids the need for a SORT

CREATE NONCLUSTERED INDEX ix 
           ON MyTable(SomeId) 
           INCLUDE (Foo,IsFlag1, IsFlag2) 
           WHERE IsFlag1 != 1 and IsFlag2 != 1

It does a scan of the filtered index (the qualifying rows ordered by SomeId) with a TOP to stop scanning after the 1,000 rows are retrieved.

In general it is not possible to use an index seek on a condition x != 1 and y != 1. With an index on x,y the best you can do is convert it into two range seeks with residual predicates x < 1 and y != 1 and x > 1 and y != 1.

For a bit column as it can only have three values. 0, 1, NULL logically WHERE bit_column != 1 is equivalent to WHERE bit_column = 0 but seems SQL Server doesn't take advantage of that here and convert the <> to = conditions for you.

Adding a couple of check constraints does the job though even though these are apparently redundant in that they don't actually restrict the allowable values for the datatype in any way (for NULL if a check constraint evaluates to UNKNOWN it counts as passing)

CREATE TABLE MyTable
  (
     Foo     INT,
     IsFlag1 BIT NULL CHECK (IsFlag1 IN (0, 1)),
     IsFlag2 BIT NULL CHECK (IsFlag2 IN (0, 1)),
     SomeId  INT
  );

CREATE NONCLUSTERED INDEX ix
  ON MyTable(IsFlag1, IsFlag2, SomeId)
  INCLUDE (Foo); 

The plan now does show a seek on IsFlag1 = 0 AND IsFlag2 = 0

Or alternatively this filtered index also avoids the need for a SORT

CREATE NONCLUSTERED INDEX ix 
           ON MyTable(SomeId) 
           INCLUDE (Foo,IsFlag1, IsFlag2) 
           WHERE IsFlag1 != 1 and IsFlag2 != 1

It does a scan of the filtered index (the qualifying rows ordered by SomeId) with a TOP to stop scanning after the 1,000 rows are retrieved.

3 added 442 characters in body
source | link
2 added 296 characters in body
source | link
1
source | link