Clustered index on a nullable column

>It has its clustered index on the primary key howerver it is a nullable column.

No, a PK may not include a nullable column!!!  So, leave it alone unless you have another justification.

No you are not understanding my question.    I have a primary key that is nullable. I also have clustered index on that column. I am thinking about moving the Clustered index to a non-nullable field.                  

Please research your situation more carefully.   What am I missing? I can say with certainty that your primary key is not nullable.  It does NOT contain nulls or allow nulls in any way, shape, or form.  

Leave the clustered index alone unless you have another reason.

You are correct. It is not nullable. I was Reading the allow nulls checkbox backwards. Pretty embarsing!  So there is no reason to move the Clustered index.    

The PK field has two indexes .  One clustered and on non-clustered. I am assuming that I sould removed the non-clustered index?

Yes. Remove it. There are some cases when it helpful to have that design but those are extremely rare.

You can run this script, see the usage stats and remove all non-used indexes

select t.name as [Table Name], idx.name as [Index Name]
	,st.* 
from 
	sys.tables t join sys.indexes idx on
		t.object_id = idx.object_id
	left outer join sys.dm_db_index_usage_stats st on
		st.index_id = idx.index_id and idx.object_id = st.object_id
order by 
	t.name, idx.Name
go

Select allOpen in new window

Should I have my clustered index on the primay key or on the most searched field.   I have another table with about 100 million records that gets about 100000 new records inserted daily. I think I should have the clustered index on the pkey since inserting is the most important acion on this tableis an insert. Am I correct?

Primary key is the logical concept. Clustered index is the physical one. Even if SQL creates CI on PK by default, it does not have anything to do with each other.

As for your question - it's hard to say. The CI choice needs to be done after you analyze the system and queries against this table.