lets take example, generally we use table like below
COUNTRY:
countryid int primary key
countrycode char(2)
contrydescription varchar(30)
EMPLOYEE:
EMPID int primarykey
name varchar(50)
countryid int
As empid and country id is primary key than default cluster index will automatically apply.
Now our more transaction will occur in empid and countryid then we will remove cluster index from empid and apply to country id to boost of the performance
Similarly if we are using join base on countrycode
EMPLOYEE:
EMPID int primarykey
name varchar(50)
countrycode char(2)
than we will remove cluster index from empid and countryid both and will keep on countrycode of both table.
Conclusion is that in which more column are using for join we need to keep cluster index on that to boost of the performance.
Please let me know if require more info.
Mark as answer if it helps.
Please let me know if you need more help.
Please give us feedback no matter whether you get your answer.
Please "Mark as Answer" if it's useful for you.
as you said in order to keep the performance boosting we should use cluster index on columns which are used for joining but in the below mentioned line you are saying remove cluster index from empid and apply to country id. whats point here i am not getting
it(i mean why we should remove cluster index from empid, we can keep for both).
"Now our more transaction will occur in empid and countryid then we will remove cluster index from empid and apply to country id to boost of the performance"
one more Question. If cluster index boost the performance whats use of non cluster index,where should we use it..?
it may look very simple but i am not getting it ,so please clarify me.
You can say it 's a basic rule of thumb that use no clustered indexes when small amounts of data will be returned and clustered indexes when larger result sets will be returned by your query. On-Clustered index is depends upon the query running against the
table. There is no ideal index structure available. It all depends upon the kind of application you have and query you run. You may also do a live search on Covering Indexes.
lmnhand
Member
20 Points
43 Posts
where should we use clustered index and non clustered index and whats difference between them.
Apr 10, 2010 05:09 PM|LINK
where should we use clustered index and non clustered index.i mean in day to day use which on e to choice and how to make decision.
please explain in detail with valid points to choice.
SQL query
Das.Sandeep
Star
10652 Points
1897 Posts
Re: where should we use clustered index and non clustered index and whats difference between the...
Apr 10, 2010 05:30 PM|LINK
use culsture in which you use join
http://www.allinterview.com/showanswers/8105.html
Please give us feedback no matter whether you get your answer.
Please "Mark as Answer" if it's useful for you.
Regards,
Sandeep
lmnhand
Member
20 Points
43 Posts
Re: where should we use clustered index and non clustered index and whats difference between the...
Apr 11, 2010 11:22 AM|LINK
can you give some more explanation, i am not able to understand it(i know the definition ),but i need some hard core real world purpose of using...
Das.Sandeep
Star
10652 Points
1897 Posts
Re: where should we use clustered index and non clustered index and whats difference between the...
Apr 11, 2010 11:40 AM|LINK
lets take example, generally we use table like below
COUNTRY:
countryid int primary key
countrycode char(2)
contrydescription varchar(30)
EMPLOYEE:
EMPID int primarykey
name varchar(50)
countryid int
As empid and country id is primary key than default cluster index will automatically apply.
Now our more transaction will occur in empid and countryid then we will remove cluster index from empid and apply to country id to boost of the performance
Similarly if we are using join base on countrycode
EMPLOYEE:
EMPID int primarykey
name varchar(50)
countrycode char(2)
than we will remove cluster index from empid and countryid both and will keep on countrycode of both table.
Conclusion is that in which more column are using for join we need to keep cluster index on that to boost of the performance.
Please let me know if require more info.
Mark as answer if it helps.
Please give us feedback no matter whether you get your answer.
Please "Mark as Answer" if it's useful for you.
Regards,
Sandeep
lmnhand
Member
20 Points
43 Posts
Re: where should we use clustered index and non clustered index and whats difference between the...
Apr 11, 2010 12:16 PM|LINK
thanks for your quick Response,
please help me on this,
as you said in order to keep the performance boosting we should use cluster index on columns which are used for joining but in the below mentioned line you are saying remove cluster index from empid and apply to country id. whats point here i am not getting it(i mean why we should remove cluster index from empid, we can keep for both).
"Now our more transaction will occur in empid and countryid then we will remove cluster index from empid and apply to country id to boost of the performance"
one more Question. If cluster index boost the performance whats use of non cluster index,where should we use it..?
it may look very simple but i am not getting it ,so please clarify me.
lmnhand
Member
20 Points
43 Posts
Re: where should we use clustered index and non clustered index and whats difference between the...
Apr 12, 2010 04:19 AM|LINK
any one please share some details regarding cluster and non cluster index and there appropriate use.
jassu107
Participant
817 Points
243 Posts
Re: where should we use clustered index and non clustered index and whats difference between the...
Apr 12, 2010 04:54 AM|LINK
please check these links
http://msdn.microsoft.com/en-us/library/ms190639(SQL.90).aspx
http://msdn.microsoft.com/en-us/library/ms179325(SQL.90).aspx
Mark as Answer if it helps you...
jassu107
Participant
817 Points
243 Posts
Re: where should we use clustered index and non clustered index and whats difference between the...
Apr 12, 2010 04:55 AM|LINK
please check these links
http://msdn.microsoft.com/en-us/library/ms190639(SQL.90).aspx
http://msdn.microsoft.com/en-us/library/ms179325(SQL.90).aspx
Please remember to mark the replies as answers if they help
Mark as Answer if it helps you...
wmec
Contributor
6195 Points
3214 Posts
Re: where should we use clustered index and non clustered index and whats difference between the...
Apr 12, 2010 05:09 AM|LINK
see this
http://www.sql-server-performance.com/articles/per/index_data_structures_p1.aspx
HuaMin Chen
ahsan_kabir_...
Member
4 Points
2 Posts
Re: where should we use clustered index and non clustered index and whats difference between the...
Jan 20, 2013 06:05 PM|LINK
Hi ,
You can say it 's a basic rule of thumb that use no clustered indexes when small amounts of data will be returned and clustered indexes when larger result sets will be returned by your query. On-Clustered index is depends upon the query running against the table. There is no ideal index structure available. It all depends upon the kind of application you have and query you run. You may also do a live search on Covering Indexes.
Click on below link :
http://msdn.microsoft.com/en-us/library/ms190639(SQL.90).aspx
http://msdn.microsoft.com/en-us/library/ms179325(SQL.90).aspx