| Author |
Topic |
|
Abid
110 Posts |
 Posted - 2011-05-20 : 07:12:24
|
| hi, i have a table called Member_Info and its fields areMem_NameMem_StatusMem_Membership_IDMem_Membership_DateMem_Data_Of_BirthMem_Tel_NoMem_Cell_NoMem_EmailMem_Mailing_Addressmy questions are two 1). Do i make Mem_Membership_ID as primary key in this table?2). I personally think that I add a new attribute called S.No, and that will be primary key? the problem is that if I make S.No as primary key so there will be two attributes which can be used for primary key? so what i do now? |
|
|
nigelrivett
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2011-05-20 : 07:37:44
|
| Where does Mem_Membership_ID come from.Is it always known when oyu add an entry and does it never change?If so then it should be the row identifier and the PK.There's nothing to stop you having two candidate keys. One becomes a PK and the other an alternate key with a unique index.==========================================Cursors are useful if you don't know sql.SSIS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
Abid
110 Posts |
Posted - 2011-05-20 : 07:40:56
|
quote:
Originally posted by nigelrivett
Where does Mem_Membership_ID come from.Is it always known when oyu add an entry and does it never change?If so then it should be the row identifier and the PK.There's nothing to stop you having two candidate keys. One becomes a PK and the other an alternate key with a unique index.==========================================Cursors are useful if you don't know sql.SSIS can be used in a similar way.Beer is not cold and it isn't fizzy.
Sir, the company says that every member will have a unique membership_id, which will always be unique forever. and can't be assigned to anyone. now what you recommend that do i select the Mem_Membership_ID as a primary key or do i need to create the new attribute S.No as a primary key? |
|
|
|
nigelrivett
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2011-05-20 : 07:47:13
|
| I would use the Mem_Membership_ID. I take it that it's an int?==========================================Cursors are useful if you don't know sql.SSIS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
lionofdezert
Aged Yak Warrior
885 Posts |
Posted - 2011-05-20 : 07:48:24
|
| If column values of Mem_Membership_ID, will be unique and not null then go ahead and declare it as a primary key. But if mem_membership_id will be a varchar and non sequential then its better to have surrogate key (artificial key) like mem_record_no with datatype INT and make it identity.--------------------------http://connectsql.blogspot.com/ |
|
|
|
Abid
110 Posts |
Posted - 2011-05-20 : 07:56:36
|
quote:
Originally posted by lionofdezert
If column values of Mem_Membership_ID, will be unique and not null then go ahead and declare it as a primary key. But if mem_membership_id will be a varchar and non sequential then its better to have surrogate key (artificial key) like mem_record_no with datatype INT and make it identity.--------------------------http://connectsql.blogspot.com/
oopss.. sir would you pleae tell me that if it will be varchar that what will be problem?and what is surrogate key? |
|
|
|
nigelrivett
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2011-05-20 : 08:05:05
|
| Surrogate key is a unique value that has nothing to do with any real world entities and is usually used for referential integrity. Means that you can change any of the attributes without affecting the system.If it's a varchar it means that maybe the system will be a bit slower. May have to use the code pages to do joins and the index will probably be a bit wider and take up more space and you might not want to cluster on it. It probably won't be detectable unlese the database is large - in which case you will have bigger problems with the implementation anyway is my guess.If it's a character value then it probably hs some sort of meaning - you would need to be very careful about whether this could ever change.==========================================Cursors are useful if you don't know sql.SSIS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
lionofdezert
Aged Yak Warrior
885 Posts |
Posted - 2011-05-20 : 08:06:17
|
| if its varchar then its will not be sequential and resultantly clustered index's fregmantation rate will be high, secondly clustered index key will be part of every non clustered index and if this key is wide then obviously it will take more space. That is why INT columns are considered best candidate for clustered index.--------------------------http://connectsql.blogspot.com/ |
|
|
|
Abid
110 Posts |
Posted - 2011-05-20 : 08:16:57
|
| woooowww its little difficult for me to understand such a difficult concepts. but if i get your devoted assistance so i might get the better new concepts.lionofdezert and nigelrivett would you please tell me that what is this cluster, and clustered index fragmentation and non clustered.... Please assist me. |
|
|
|
nigelrivett
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2011-05-20 : 08:47:35
|
| Have a lok in bol.A clustered index has the leaf level as the data page, a non-clustered index has the leaf level as a copy of the columns indexed (and included).The clustered index columns are included in every non-clustered index.Fragmentation is when the pages aren't sequential - means that when a page is read then it is less likely for the next page needed to be read in the same block. When a row is inserted into a table not in clustered index order then it will need to be inserted between two existing rows. Rather than moving every subsequent row the server will move the rows on that page - if there is no room on the page it will be split and another page created and the rows divided between the too - the new page will probably not be near the old one and so will be fragmented. A similar thing happens with non-clustred indexes.If your system is large enough for these issues to be a concern you should probably get someone in to review what you are doing.==========================================Cursors are useful if you don't know sql.SSIS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
|
Assistance needed in selecting Key?