Preffered which Clustered or nonclustered index for single table

Question

0.00/5 (No votes)

See more:

If i have one table ,i have to use one from clustered or non clustered index to which i should prefered for better performance :

I am thinking about Clustered index since non clustered index uses extra key look up & extra storage space 

Please mentioned ur review about above scenario.

Thanks to all.....

Happy new year 2014 to code projects members

Posted 31-Dec-13 19:21pm

patil.nitin8358

Updated 31-Dec-13 19:23pm

v2

Add a Solution

3 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)

BK 4 code · Answer 1 · 2013-12-31T19:26:00

HI
first of all you need to understand diff between clustered and non clustered index.
Clustered index must be applied on unique key of table and non-clustered index must be applied on any key of that table. and you have only one clustered index in your table and it will be created by default when you create Primary key of that table.

So if you want to improve your select query based on index , you should create non-clustered index with the columns which you used in where clause of that query.and give fill factor about 70-80%.

And use execution plan for your query so it will suggest you index.

FOr more information :

http://technet.microsoft.com/en-us/library/ms190457.aspx[^]

http://technet.microsoft.com/en-us/library/ms189280.aspx[^]

Christian Graus · Answer 2 · 2013-12-31T19:33:00

To add some more detail:

A clustered index stores the ENTIRE row as the index. This means that it's super fast because ALL the data exists on the node where the index lies. This means that your whole table is stored sequentially in the database. What this means is, if you delete a row, all the rows above that row, need to be copied down to maintain the index. So, a clustered index is super fast for reads, can be slow for edits ( depending on if your edit makes the row take up more room than is available for the row when you start ) and very slow for deletes. It's great for archived data that will never change. It's great for stuff you'll read often and need to be super fast, but rarely changes. It's horrible for data that gets changed constantly.

As always with SQL, the answer is, it depends, and there's a reason both options exist, each is sometimes the best choice.

You mention storage space as a concern. Unless you work for Google, it seems unlikely that your DB size is going to run in to significant hardware costs. Make sure you optimise on what matters, not blindly on anything you can think of.

TrushnaK · Answer 3 · 2013-12-31T19:34:00

yes, you know the difference between cluster and non-cluster index.
also you have to clear your senario what you want exactly in your db.

see below example:-
A comparison of a non-clustered index with a clustered index with an example

As an example of a non-clustered index, let’s say that we have a non-clustered index on the EmployeeID column. A non-clustered index will store both the value of the EmployeeID AND a pointer to the row in the Employee table where that value is actually stored. But a clustered index, on the other hand, will actually store the row data for a particular EmployeeID – so if you are running a query that looks for an EmployeeID of 15, the data from other columns in the table like EmployeeName, EmployeeAddress, etc. will all actually be stored in the leaf node of the clustered index itself.

This means that with a non-clustered index extra work is required to follow that pointer to the row in the table to retrieve any other desired values, as opposed to a clustered index which can just access the row directly since it is being stored in the same order as the clustered index itself. So, reading from a clustered index is generally faster than reading from a non-clustered index.

for more detail refer below sites:-
http://stackoverflow.com/questions/18304376/sql-server-when-to-use-clustered-vs-non-clustered-index[^]
Clustered and Non-Clustered Index in SQL 2005[^]

Preffered which Clustered or nonclustered index for single table

3 solutions

Solution 1

Solution 2

Solution 3

Add your solution here

Preview 0