Indexing Approach for NULL able Column


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.

ken hanseAsked:
Who is Participating?
Scott PletcherSenior DBACommented:
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.
Vikas GargBusiness Intelligence DeveloperCommented:

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 resolverCommented:
You have to instead index on other columns which are not null.
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Vitor MontalvãoMSSQL Senior EngineerCommented:
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 DBACommented:
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.
ken hanseAuthor Commented:
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.


UPDATE table
SET col1 = -1
WHERE col1 is null

I need to improve the performance of this query.
ken hanseAuthor 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.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.