Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Clustered index on a nullable column

Posted on 2011-05-02
9
Medium Priority
?
514 Views
Last Modified: 2012-06-27
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?
0
Comment
Question by:soccerman767
  • 3
  • 3
  • 3
9 Comments
 
LVL 42

Expert Comment

by:dqmq
ID: 35505901
>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
 

Author Comment

by:soccerman767
ID: 35506042
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
 
LVL 42

Expert Comment

by:dqmq
ID: 35506414
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 13

Accepted Solution

by:
dwkor earned 1000 total points
ID: 35506677
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
 

Author Comment

by:soccerman767
ID: 35506939
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
 
LVL 13

Expert Comment

by:dwkor
ID: 35507005
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
 

Author Comment

by:soccerman767
ID: 35507246
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
 
LVL 13

Expert Comment

by:dwkor
ID: 35507375
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
 
LVL 42

Assisted Solution

by:dqmq
dqmq earned 1000 total points
ID: 35507409
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

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

by Mark Wills PIVOT is a great facility and solves many an EAV (Entity - Attribute - Value) type transformation where we need the information held as data within a column to become columns in their own right. Now, in some cases that is relatively…
INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
this video summaries big data hadoop online training demo (http://onlineitguru.com/big-data-hadoop-online-training-placement.html) , and covers basics in big data hadoop .
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …

578 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question