Is null and is not NULL in SQL SERVER cause the index to be invalidated?

In fact, there is nothing to say about this problem, today's optimization encountered an SQL statement, because it is more interesting, so I intercepted, simplified the SQL statement, to show you, as shown below

DECLARE @bamboo_Code varchar (3);

Set @bamboo_Code = '-01 ';

SELECT DISTINCT Yarn_lot

From Dbo.rsjob with (NOLOCK)

WHERE Right (ges_no, 3) = @bamboo_Code

As shown above, the SQL column Yarn_log uses the IsNull (Yarn_lot, ') <> ', and I estimate that the person writing the SQL statement should be convinced that "is null and is not NULL will cause the index to fail" This online doctrine , as to where the proposal was spread from, has been unable to verify. Then we practice to verify that is null or is not NULL causes the index to fail.

Table Rsjob is a heap table that has an index yarn_lot on the column yarn_lot. Then we'll test it out.

SELECT DISTINCT Yarn_lot

From Dbo.rsjob with (NOLOCK)

WHERE Yarn_lot is not NULL;

SELECT DISTINCT Yarn_lot

From Dbo.rsjob with (NOLOCK)

WHERE Yarn_lot is NULL

As shown above, either is null or is NOT NULL has gone through the index lookup.

DECLARE @bamboo_Code varchar (3);

Set @bamboo_Code = '-01 ';

SELECT DISTINCT Yarn_lot

From Dbo.rsjob with (NOLOCK)

WHERE Right (ges_no, 3) = @bamboo_Code

SELECT DISTINCT Yarn_lot

From Dbo.rsjob with (NOLOCK)

WHERE Right (ges_no, 3) = @bamboo_Code

And Yarn_lot is not NULL;

In addition, let's take a look at the cost ratio of these two raw SQL execution plans is 52:48, that is, using is isn't null performance is better, the first SQL statement because of the conversion, causing it to go through the index scan, and use is not NULL to go index lookup.

"Is null and is not NULL will result in index invalidation" this deceptive dogma was directly overturned. So people who still believe in this dogma should really do it by themselves.

Below we can verify by experiment, considering that in the real environment, the situation may be more complex. We can build the next few scenarios. In fact, the situation in the real environment will be more complicated. But basically there are some scenarios

Scenario 1: A separate indexed column on a heap table predicate

Use Test;

GO

DROP TABLE TEST;

GO

CREATE TABLE TEST (object_id INT, NAME VARCHAR (12));

CREATE INDEX pk_test on TEST (object_id) INCLUDE (NAME);

DECLARE @Index INT = 0;

While @Index < 10000

BEGIN

INSERT into TEST

SELECT @Index, ' Kerry ' + CAST (@Index as VARCHAR);

SET @Index = @Index +1;

END

INSERT into TEST

SELECT NULL, ' only test1 ' UNION all

SELECT NULL, ' only Test2 '

UPDATE STATISTICS TEST with FULLSCAN;

SELECT * from TEST WHERE object_id is NULL;

SELECT * from TEST WHERE object_id are not NULL;

Delete the index and create the following index. As shown below

DROP INDEX pk_test on TEST;

CREATE INDEX pk_test on TEST (object_id)

This shows that the execution plan of IS null or is isn't NULL is related to the index, and it is also related to the distribution of data.

Scenario 2: No index on heap table predicate

Use Test;

GO

DROP TABLE TEST;

GO

CREATE TABLE TEST (object_id INT, NAME VARCHAR (12));

DECLARE @Index INT = 0;

While @Index < 10000

BEGIN

INSERT into TEST

SELECT @Index, ' Kerry ' + CAST (@Index as VARCHAR);

SET @Index = @Index +1;

END

INSERT into TEST

SELECT NULL, ' only test1 ' UNION all

SELECT NULL, ' only Test2 '

UPDATE STATISTICS TEST with FULLSCAN;

SELECT * from TEST WHERE object_id is NULL;

SELECT * from TEST WHERE object_id are not NULL;

As shown above, if a heap table does not have any indexes, then using is null or is not NULL would definitely take a full table scan, but this is outside of our scope of discussion. Then we look at the index being built on other fields (mainly for the purpose of comparing it to the clustered index table), which still scans the whole table.

CREATE INDEX pk_test on TEST (object_id) INCLUDE (NAME);

INSERT into TEST

SELECT 10000, NULL UNION all

SELECT 10001, NULL;

SELECT * from TEST WHERE NAME is NULL;

SELECT * from TEST WHERE NAME is not NULL;

Scenario 3: Heap Table Union index column

Use Test;

GO

DROP TABLE TEST;

GO

CREATE TABLE TEST (object_id int, NAME VARCHAR (n), age INT);

CREATE INDEX idx_test_n1 on TEST (NAME, age);

DECLARE @Index INT = 0;

While @Index < 10000

BEGIN

INSERT into TEST

SELECT @Index, ' Kerry ' + CAST (@Index as VARCHAR), Floor (rand () *100);

SET @Index = @Index +1;

END

INSERT into TEST

SELECT NULL, ' only Test1 ', the UNION all

SELECT NULL, ' only Test2 ', 24

UPDATE STATISTICS TEST with FULLSCAN;

SELECT * from TEST WHERE NAME is NULL;

SELECT * from TEST WHERE NAME is not NULL;

If the predicate is in the second or later position of the Federated Index in the Federated index, what is the case? From the following we can see that the SQL Walk full table scan.

DROP INDEX idx_test_n1 on TEST;

CREATE INDEX idx_test_n1 on TEST (age,name);

UPDATE STATISTICS TEST with FULLSCAN;

4 Clustered Index Table separate indexed columns

Use Test;

GO

DROP TABLE TEST;

GO

CREATE TABLE TEST (object_id INT, NAME VARCHAR (12));

CREATE CLUSTERED INDEX pk_test on TEST (object_id)

DECLARE @Index INT = 0;

While @Index < 10000

BEGIN

INSERT into TEST

SELECT @Index, ' Kerry ' + CAST (@Index as VARCHAR);

SET @Index = @Index +1;

END

INSERT into TEST

SELECT NULL, ' only test1 ' UNION all

SELECT NULL, ' only Test2 '

SELECT * from TEST WHERE object_id is NULL;

SELECT * from TEST WHERE object_id are not NULL;

If I use is null or is not NULL to query on column name, you will find that the execution plan has changed from clustered index lookup to clustered index scan.

SELECT 10000, NULL UNION all

SELECT * from TEST WHERE NAME is not NULL;

4 Clustered Index Table Federated Index column

Use Test;

GO

DROP TABLE TEST;

GO

CREATE TABLE TEST (object_id int, NAME VARCHAR (n), age INT);

CREATE CLUSTERED INDEX pk_test on TEST (object_id)

DECLARE @Index INT = 0;

While @Index < 10000

BEGIN

INSERT into TEST

SELECT @Index, ' Kerry ' + CAST (@Index as VARCHAR), Floor (rand () *100);

SET @Index = @Index +1;

END

INSERT into TEST

SELECT 10001, ' NULL ', UNION all

SELECT 10002, ' NULL ', 24

CREATE INDEX idx_test_n2 on TEST (name,age);

UPDATE STATISTICS TEST with FULLSCAN;

If the predicate is located in the Union index and not in the first column, is null or is the NOT NULL will not go index it?

As shown above, it changes from an index lookup to an index scan.

Summary: 1: "Is null and is not NULL will result in index invalidation" This dogma is completely crap, and SQL Server's index is a null value, and Oracle's index does not contain a null value. Different database conditions, do not mechanically.

2: If the predicate is indexed, it will basically go through the index, as to whether to go Index lookup or index scan and index type has a certain relationship, also with the field in the Federated index position has a relationship. In addition, the skewed data distribution can cause it to go full table scan without indexing, but this does not mean that the is null and is not NULL cause the index to fail. One thing is very important, it is very unrealistic to infer the execution plan by observing the SQL statement, it is necessary to study the indexes, data distribution and statistic information of the tables involved in the SQL statement, in order to synthesize judgment, and to combine the concrete scenes with popular words.

Is null and is not NULL in SQL SERVER cause the index to be invalidated?