We help IT Professionals succeed at work.

Indexing Approach for NULL able Column

Hi,

Please share with me your expert on indexing a NULL column with  few data values and others are null.

will this improve the speed of the query

My requirement here is the improve the update process  for a table on a SSIS package for the  NULL values on that table.

thanks
Comment
Watch Question

Vikas GargAssociate Principal Engineer
Top Expert 2014
Commented:
Hello,

Index generally speed up the process of fetching the data and in update it will not.

If your column which is having most nulls is used in join condition or in where clause then it can increase the performance otherwise not.
HuaMin ChenProblem resolver
Commented:
Hi,
You have to instead index on other columns which are not null.
Vitor MontalvãoIT Engineer
Distinguished Expert 2017
Commented:
You should index columns that has a large variety of values. Columns that like to have mostly the same value shouldn't be indexed. A good example is the Boolean columns (only two values - True or False). Those kind of columns shouldn't be indexed.
Scott PletcherSenior DBA
Most Valuable Expert 2018
Distinguished Expert 2019
Commented:
In that case, I suggest you do index, but limit the index entries to only non-NULL values.  Since most of the values in the table are NULL, indexing on the NULLs wouldn't gain you anything.
My requirement is to improved the update statement, that got a where clause to find out "NULL" and update with some values, at the moment it is taking longer to scan through all the null values of the table.

So, it is clear to you my requirement is to improve  the UPDATE query performance.

Eg.

UPDATE table
SET col1 = -1
WHERE col1 is null

I need to improve the performance of this query.
Senior DBA
Most Valuable Expert 2018
Distinguished Expert 2019
Commented:
You didn't show the query before.  If most of the values are NULL, it will be very difficult to speed up that query.  Good luck with it.

Author

Commented:
Thanks for all participant,  it was not enough details to answer from my question.

thanks for every body's contribution.

Thanks ScottPletcher finally  gave conclusion.