Clustered Index, Cursors [Resolved]

Posted by Sriharim under Sql Server on 2/27/2015 | Points: 10 | Views : 548 | Status : [Member] | Replies : 4
Hi Team,
1)
Suppose, a clustered index column have 1 to 100 records, these 100 records are there in 2 pages. Each page has 50 records in the database.
If i insert 50th duplicate record into this column, where these duplicated record will be inserted, whether in
a) after 100th records,that is in 3rd page.
or
b) after 50th original record. In this case 100th will be moved to 3rd page ?
or
c) before 50th original record. In this case also 100th will be moved to 3rd page ?
If answer is (b) OR (c), suppose, i have 1 million records,if i insert in middle of 1 million of records, then all records will reordered ?(because of clustered index)


2)We know Cursor will effect performance, still why cursor are introduced ?
Can i have real time scenario ?

---
Srihari



Responses

Posted by: Sriharim on: 2/28/2015 [Member] Starter | Points: 25

Up
0
Down

Resolved
ignore previous one,
clustered index allows duplicate records, below is example where i tried
--create table tuc(a int,b varchar(50),c varchar(50))
--insert into tuc select 4,'c','a'
--union select 99,'v','b' union select 30,'fg','h' union select 96,'g','gg'
--union select 94,'g','gg' union select 4,'c','a'

--insert into tuc select 4,'c','a' union select 41,'c','a'
--and output for select * from tuc
columns a b c
4 c a
30 fg h
94 g gg
96 g gg
99 v b
4 c a
41 c a
--create clustered index clus_indx on tuc (a)
now see check the table records. after creating clustered index on column a, select * from tuc
so clustered index allows duplicates and now again i am inserting records
--insert into tuc select 4,'c','a' union select 78,'v','c' union select 55,'v','b' union select 94,'g','gg'
--select * from tuc
Below is output and records r reordered automatically
columns a b c
4 c a
4 c a
4 c a
30 fg h
41 c a
55 v b
78 v c
94 g gg
94 g gg
96 g gg
99 v b
------------------
from above example,suppose a clustered index column have 1 to 100 records, these 100 records are there in 2 pages. Each page has 50 records in the database.
If i insert 50th duplicate record into this column, these duplicated record will be inserted in middle, as per records order.
100th record will be moved to the 3rd page.

---
Srihari

Sriharim, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Jitendrasoft09 on: 2/27/2015 [Member] [MVP] Starter | Points: 25

Up
0
Down
Hi,

Clustered index never takes duplicate values if defines UNIQUE. If you need to insert duplicate values then you can go with non clustered index. Cursor makes performance issues but it is depend on how cursor is used in your requirement.

Please refer below link for more details-
http://stackoverflow.com/questions/1251636/what-do-clustered-and-non-clustered-index-actually-mean
http://www.dotnetspider.com/forum/88380-wat-is-cursor-and-use-of-cursor-and-types-give-so.aspx

Jitendra Kumar
If my post helps you, plz mark as an answer.

Sriharim, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Sriharim on: 2/28/2015 [Member] Starter | Points: 25

Up
0
Down
clustere index allows dulicated records, below is example where i tried

---
Srihari

Sriharim, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Jitendrasoft09 on: 2/28/2015 [Member] [MVP] Starter | Points: 25

Up
0
Down
Hi,

I agreed that but a clustered index does not enforce uniqueness unless you specify the keyword UNIQUE.


Jitendra Kumar
If my post helps you, plz mark as an answer.

Sriharim, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response