1

What will be the syntax to create non-clustered index for the query which has is null condition.

Sample query:

select * 
from mytable
where mycolumn is null

Index that I created:

CREATE NONCLUSTERED INDEX [myindexname]
ON [dbo].[mytable] ([mycolumn])

When I execute this query in SQL and see its execution plan, it shows missing index.

enter image description here

Or there any other, alternative possibilities?

2 Answers 2

3

There is no problem creating indexes on nullable columns.
The only thing you can't do with nullable columns is create a primary key.

You can right click the green message and SSMS will give you the option to copy it. Then paste it into the query editor and you will see it contains the create index statement. All you need to do is give it it's name and run the statement.

Following the conversation in the comments to this answer, Ivan Sivak have figured out that this is a bug that SQL Server had in and was fixed in 2012 version. Here is the link to the bug report in Microsoft Connect.

Sign up to request clarification or add additional context in comments.

9 Comments

Yes, i have done exactly the same. The image that i have attached in my question is after the index creation.
Sometimes, after creating the missing index, the optimizer can recommend another missing index. See if it's the same index that you just created.
@Namita What version of SQL Server do you use? Is the suggested index really absolutely identical with your originally created one?
I am using sql server 2008 r2 and ssms version: 10.50.1600.1 and in the suggested index i've updated only name of my index
Also, the column on which i want to create index is "bigint" and it is a nullable column.(Hope this info helps)
|
0

By default, the query plan uses the best index in available in any scenario.

One reason could be that the index is fragmented. You could decrease the fragmentation by running the query

ALTER INDEX [myindexname] ON [dbo].[mytable] REBUILD;

You can read more on this here: https://learn.microsoft.com/en-us/sql/relational-databases/indexes/reorganize-and-rebuild-indexes


The query plan at times doesn't take the best available index for the query. For this, Microsoft has provided table hints.

  1. ForceSeek
    This tells the optimizer to use an index seek operation in any case.
SELECT *
FROM   mytable WITH(FORCESEEK)
WHERE  mycolumn IS NULL 

Caution: while using any Force Plan hint (ForceSeek,ForceScan,etc.) or you might break the query

  1. Index Hint
    This tells the optimizer to use a particular index for seek/scan
SELECT *
FROM   mytable WITH([myindexname])
WHERE  mycolumn IS NULL 

This query will now use myindexname for above query.

  1. You can combine both these hints:
SELECT *
FROM   mytable WITH([myindexname], ForceSeek)
WHERE  mycolumn IS NULL 

This query will now use myindexname with a scan operation for above query.

More on table hints here: https://technet.microsoft.com/en-us/library/bb510478(v=sql.105).aspx

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.