Clustered index on a nullable column

I have an inhertied table that is about 20 GIG. It has its clustered index on the primary key howerver it is a nullable column. I am planing on moving the clusted index to a column that does not allow nulls. Is this avisable or should I leave it on the primary key?
soccerman767Asked:
Who is Participating?
 
dwkorCommented:
Clustered index can be created on nullable column but primary key constraint cannot be.

As for your question - you need to design CI based on other criteria than nullable/not null column. For the table of such size you have to make it in the way, that benefits your most critical queries.

And there are a few other considerations:
1. It should be unique. Otherwise SQL adds 4 extra byte uniquefier to the index
2. It should be as narrow as possible because CI values would be part of every non-clustered index rows. Obviously you have to take queries in mind - bigger row size that benefits the queries is better than narrow indexes that don't do anything
3. It should static - if you update CI, data would be physically moved to the different place.
0
 
dqmqCommented:
>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.
0
 
soccerman767Author Commented:
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.                  

0
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
dqmqCommented:
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.
0
 
soccerman767Author Commented:
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?
0
 
dwkorCommented:
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

Open in new window

0
 
soccerman767Author Commented:
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?
0
 
dwkorCommented:
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.
0
 
dqmqCommented:
The clustered index determines the order that records are physically stored.  You only get one for each table, so choosing the best CI calls for careful consideration.

The most searched field should have an index, but it needn't be clustered. And it probably shouldn't be clustered if it's a wide index, like a name or other field > then 4 bytes.  It's often more effective to put the CI on an index that keeps records together that are used at the same time.  


If insert performance is a concern, then put the CI on a key that is ever-increasing (as in an identity column).  That way, successive records are added at the end, you don't need a lot of padding in your index pages, and you will seldom encounter expensive page splits resulting from inserts.

 
0
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.