Difference between clustered and non clustered index in sql server

Question

1.00/5 (2 votes)

See more:

what is the difference between clustered and non clustered index in sql server. what will we use while designing table in sql server.

Posted 19-Sep-12 22:32pm

Santu Ghosh 7

Updated 19-Sep-12 22:36pm

Andrei Straut

v2

Add a Solution

Comments

Andrei Straut 20-Sep-12 4:38am

Pst! Look what Google whispered to me[^] when I told him your exact question

Vedangi 20-Sep-12 4:40am

YOu can refer this article
http://www.codeproject.com/Articles/173275/Clustered-and-Non-Clustered-Index-in-SQL-2005

4 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Christian Graus · Answer 1 · 2014-01-14T10:47:00

While I agree that the best way to answer this would have been google, I will just add that a clustered index is super fast for searches, and slows down deletes and ( to a lesser degree ) changes to data. If you have a primary key on your table, it is a clustered index by default, so if you want to have another clustered index, your primary key needs to be marked NOT clustered.

rrsoftech · Answer 2 · 2013-01-02T17:05:00

A clustered index is a special type of index that reorders the way records in the table are physically stored.
Therefore table can have only one clustered index and this is usually made on the primary key. The leaf nodes of a clustered index contain the data pages.

A nonclustered index is a special type of index in which the logical order of the index does not match the physical stored order of the rows on disk. The leaf node of a nonclustered index does not consist of the data pages. Instead, the leaf nodes contain index rows.
There can be as many as 249 nonclustered index per table

Tadit Dash (ତଡିତ୍ କୁମାର ଦାଶ) · Answer 3 · 2012-09-19T22:39:00

Solution 1

Please go through
1. Clustered and Non-Clustered Index in SQL 2005[^].
2. Overview of SQL Server 2005/2008 Table Indexing (Part-1)[^]
3. Database performance optimization part 1 (Indexing strategies)[^]
4. Speeding up database access - Part 3: Fixing missing indexes[^]

Posted 19-Sep-12 22:39pm

Tadit Dash (ତଡିତ୍ କୁମାର ଦାଶ)

Member 8236308 · Answer 4 · 2014-04-25T20:09:00

The basic differences between Primary Key and Unique key are as follows.
1) By default Primary Key will generate Clustured Index
whereas Unique Key will Generate Non-Clustured Index.
2) Primary Key is a combination of Unique and NOT NULL Constraints so it can’t
have duplicate values or any NUll
Whereas for Oracle UNIQUE Key can have any number of NULL whereas for SQL
Server It can have only one NULL
3) A table can have only one PK but It can have any number of UNIQUE Key.

Difference between clustered and non clustered index in sql server

4 solutions

Solution 4

Solution 2

Solution 1

Solution 5

Add your solution here

Preview 0