We help IT Professionals succeed at work.

Non clustered primary key

Wizilling
Wizilling asked
on
1,060 Views
Last Modified: 2013-11-05
I did a sp_helpindex on some of me SQL 7 tables
the results I got for the primary key are "nonclustered, unique, primary key located on PRIMARY"
and there are many tables with primary keys but they are not clustered .

How is this possible?
How can we change it to be Clustered. ?
Comment
Watch Question

Database Developer
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
AneeshDatabase Consultant
CERTIFIED EXPERT
Top Expert 2009
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
Doug BishopDatabase Developer

Commented:
dqmq has a good point about there only being able to be one clustered index per table.

Check all your indexes on each table that does not have a clustered primary key and see if another index is clustered. Most people just assume that the primary key should be a clustered index without giving concern to any other parameters behind creating indexes, how they will be used, etc.
Racim BOUDJAKDJIDatabase Architect - Dba - Data Scientist
CERTIFIED EXPERT
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
AneeshDatabase Consultant
CERTIFIED EXPERT
Top Expert 2009

Commented:
>How can we change it to be Clustered.
Better ou give us the table design and any perfomance issue you are facing
Racim BOUDJAKDJIDatabase Architect - Dba - Data Scientist
CERTIFIED EXPERT
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
Racim BOUDJAKDJIDatabase Architect - Dba - Data Scientist
CERTIFIED EXPERT

Commented:
<<Better ou give us the table design and any perfomance issue you are facing >>
I agree with that approach.  The choice of nonclustered vs clustered index should be done according to performance considerations...

Author

Commented:
these particular SQL Server (version 7) has been having performance issues mostly.. i noticed that these were with at least 5-6 large tables and these were usually joined together in views or select statements... then i found out that these major tables didnt have any Clustered indexes -- just non clustered ones.

leaving the space issue aside -- I know clustered will take heaps of space
SInce the tables are joined with Mostly INNER JOINS then will a clustered index help in these cases or will it give a similar result as non-clustered indexes?
Doug BishopDatabase Developer

Commented:
A clustered index does not have a lot more overhead than non-clustered. The difference is that the index is stored on the data pages along with the data. Building a clustered index can take some time, especially with a lot of data already in your database. Compare (like most do) a clustered index with a telephone box. The data is arranged alphabetically in the book according to the index. So, find Smith in the 'index' and you are on the page containg Smith. A non-clustered index is like the actual index in a book. You find the entry in the index and it directs you to the page containing the data.

It was recommended you provide the DDL for your tables and get some help determining the indexes and types that would benefit you the best.
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.