| Author |
Topic |
|
golan2
Starting Member
4 Posts |
 Posted - 2007-01-14 : 06:25:22
|
| Hi,I have a table which I would like to index.The table holds info of nurses:T_NURSE=(NurseCode, LName, FName, IDNumber ...)NurseCode => PK+IdentitySince queries will be on LName (and optionally on FName and IDNumber) I created a clustered index with this order: {LName, FName, IDNumber, NurseCode}Questions:1. Is it ok to have the clustered index not the PK?2. If yes - what importance does the PK have here? Looking for a nurse via screen (using:LName,FName,ID) or via source-code (using:NurseCode) is 50%/50%.Which field(s) should have the honor of being a PK?3. If I perform the search using a view (SELECT * FROM vw_Nurse) will it use the index?Thanks,Izik |
|
|
X002548
Not Just a Number
15586 Posts |
|
|
LoztInSpace
Aged Yak Warrior
940 Posts |
Posted - 2007-01-14 : 18:16:27
|
quote:
Originally posted by golan2
Hi,1. Is it ok to have the clustered index not the PK?
Yesquote:
Which field(s) should have the honor of being a PK?
That is a propery of your data model and businsess requirements - it has nothing to do with a particular search function (or honour). PK implies non-changing uniqueness. I would say NurseCode is your natural choice. |
 |
|
|
golan2
Starting Member
4 Posts |
Posted - 2007-02-14 : 02:40:28
|
| As I said this table is used for storing nurses' data.If the user wants to file a complaint about a nurse I save the nurse's PK in T_COMPLAINT.So I need fast search (LName, FName, IDNumber) and also fast access via PK because when showing complaints I show the nurse's name and not PK. Also my business layer objects create CNurse object according to PK. Do you know what SQLServer does behind the scenes? Will it create a HashTable for the PK or something? Thanks,Izik |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-02-14 : 13:19:56
|
| If the number of Nurses (rows in T_Nurse table) is less than, say, 5,000 I don't suppose you will have to do very much to have instant search. If people can search by LName or FName, or ID Number, then just make sure you have an index on each.I would suggest that your Clustered Index should be whatever you most commonly use in JOINS to the T_Nurse table - most likely the NurseCode or ID I expect.Kristen |
|
|
|
golan2
Starting Member
4 Posts |
Posted - 2007-02-15 : 02:17:55
|
| Hi, Tanks to all,Kristen, I understand that the emphasis regarding the clustered goes on the JOIN and not on the search.So I should make the NurseCode be the PK+Clustered and only add indexe(s) on the fields I search by (LName, FName, IDNumber).It is around 70K nurses in the table I hope it will do okThanks a lotIzik |
|
|
|
eyechart
Master Smack Fu Yak Hacker
3575 Posts |
Posted - 2007-02-15 : 02:25:08
|
| since nurscode is and identity and also your PK, my advice would be to create a composite clustered index on lname + nursecode. Lead off the index with Lname, and make sure to make it unique. Do not add the other columns you have added earlier, since they will do nothing for this index.The most important thing for a clustered index is that it be unique. You don't want it totally randomly unique though (like a GUID or something). Give it a shot and see if it works for you.-ec |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-02-15 : 03:53:39
|
| EC Why would you put LName on the Clustered index?Won't most of the lookups to T_Nurse table be a JOIN on the PK?Which then just leaves the initial lookup on LName or FName etc. which I reckon would be handled just fine by an index on each (or a composite if users will be inclined to fill in the LName always and the FName sometimes)Of course if golan2 already has the Nurse names in some sort of file the best bet would be to import them and try it this way and that way!Kristen |
|
|
|
Westley
Posting Yak Master
229 Posts |
Posted - 2007-02-15 : 04:57:47
|
| yeah, I would think that you will put cluster index + PK on NurseCode and non-cluster on both LName and FName (or an index on both if its always goes together). |
|
|
|
eyechart
Master Smack Fu Yak Hacker
3575 Posts |
Posted - 2007-02-15 : 10:26:58
|
quote:
Originally posted by Kristen
EC Why would you put LName on the Clustered index?Won't most of the lookups to T_Nurse table be a JOIN on the PK?Which then just leaves the initial lookup on LName or FName etc. which I reckon would be handled just fine by an index on each (or a composite if users will be inclined to fill in the LName always and the FName sometimes)Of course if golan2 already has the Nurse names in some sort of file the best bet would be to import them and try it this way and that way!Kristen
well, you will still have the PK on the identity column (that is another discussion we won't get into. I hate PKs on identity columns). So joins will still have an index to work with.The clustered index on LNAME will speed the retrieval of the related data. I look at clustered indexes as a way to speed up, or narrow the scope, of a table scan. If many of your queries are going to be specifying LNAME in the WHERE clause then this should work nicely. Pairing LNAME with the identity column and thus making it a unique index prevents SQL Server from having to create a GUID uniqueifier for the clustered index. -ec |
|
|
|
golan2
Starting Member
4 Posts |
Posted - 2007-02-19 : 01:45:19
|
| Hi,Before I give it a shot I prefer understanding why.Why have you suggested LName+NurseCode only?Searches will probably be on IDNumber and LName as well (although the LName is mandatory).Why not adding the FName or at least the IDNumber to the clustered?The IDNumber is unique although it is a string not int so maybe I can put a constraint on this field to prevent SQLServer adding GUID to the clustered.The table is rather static and will be update once in some months and most likely during night-batch-operation so insert-performance is not an issue here I suppose.Best regards,Izik |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-02-19 : 07:57:01
|
| The Query Optimiser will usually only use the first key field to decide if the index is worth using.If the index is used, then if ALL the columns in the query are contained within the index the query is said to be "covered" - so their is no need for the record to be retrieved at all:SELECT LName, IDFROM T_NURSEWHERE LName = 'Bloggs'is "covered" by an Index that has LName and ID as keys.A Clustered Index is very good for single-row-retrieval, and retrieving a range ("Start with AAAA and stop after BBBB")A Non-Clustered Index is good for single-row-retrieval if the Key is unique, or nearly Unique. If the index is multi-key then put the most selective Key first, or consider building the Statistics with FULL SCAN.For a sufficiently "selective" index then the index will be searched for the matching key, and then a "lookup" will be done on the main table. (If there is a Clustered Index then the index will have the Key(s) for the Clustered Index, and will use that for the lookup; if not then the Index will be storing the "address" of the record, and can thus go to it directly)Kristen |
|
|
|
|
Clustered index and PK