Index Question 1

[romh]

I have table called tblReports.
Fields: 1) ReportNum (int Identity field)(Clustered Index)
2) Date (datetime)
3) etc....
I have a 2nd table called tblReportDetails.
Fields: 1) Autos (int, which is the identity field)
2) ReportNum (foreign key to tblReports)

The above is essentially a Master / Detail relation.

tblReports has Clustered Index ReportNum

Sql server 2000 automatically added Autos from tblReportDetails as a Clustered Index.

This is my question: Since I never do anything with Autos, meaning that i never search by it, or update it (obviosuly since its Identity), does it make sense to eliminate it from beeing a clustered index, and add the clustered index to ReportNum. I do search alot by ReportNum from the tblReportDetails.

Thanks, I tried that, but SQL server is giving me a hard time doing this.

 

[romh]

Some more information:
tblReports will never experience a delete. Once there, it is locked and determined to be archived.
tblReportDetails on the other hand, will experience a delete every time before the insert actually happens. tblReportDetails has about 15 fields, and at most, the amount of records that will be inserted and deleted by 1 user will be 14. This is at one time though.
There could be 100 users simultaneously running the delete and insert process.

I'm beginning to think that having Autos as a Clustered index might not be a very good idea. How about just setting ReportNum in tblReportDetails as a non-clustered index?
The problem with tblReportDetails is that there will be a hign number of inserts, deletes and updates.

 

[Catadmin]

Clustered Indexes are essentially primary keys that are sorted in either ascending or descending order. The question you should be asking yourself isn't "Can I get rid of the one I have", but "Is there another column on the table (or combined columns) that I can do this clustered index on that has and will continue to have in the future completely unique numbers by which I can sort."

If the answer is yes, if ReportNum will ALWAYS be unique, no matter what, then go ahead and delete the index off of Auto and put it on Report. On the other hand, if you have users that input these report numbers, I would add a non-clustered index to ReportNum and leave Auto as it is. Users cannot be depended upon to remember every single last value that was ever inputed and to avoid inputting duplicates.

Does this help?



Catadmin - MCDBA, MCSA
Remember, "Work" is a four letter word. And you know what your mother told you about using four letter words!

 

[romh]

It helps. Your part describing clustered indexes is a good explanation.
To answer your question. ReportNum will not be unique. It is the foreign key of tblReports. Therefore, it could have up to 7 identical numbers. For each record in tblReport, there could be a max of 7 records with the same ReportNum in tblReportDetails.

I guess I should leave Autos as the clustered one. But then again, the rule for assigning clustered indexes, is that they are good for foreign keys. And foreign keys are not unique in 1-Many relationships.

Some more advise would be appreciated.

 

[Catadmin]

Foreign keys don't have to be unique. That's why they are foreign. They are only referencing a table in which they *are* unique.

Case in point, your earlier example. You have tblReports and tblReportsDetails.

On tblReports you have the ReportNum as a Clustered Index / Primary key. Now, if this key will be repeated *on this table*, then you need to kill the Clustered Index here and come up with an Identity field or something else to make it a unique Primary Key. If it will not be repeated on this table, then you are fine as you are.

However, in tblReportsDetail, the Autos field is your Clustered Index/Primary Key for this table. The ReportNum is a foreign key indicating a many-to-one relationship back to tblReports. Many = the ReportNum field in tblReportsDetails and One = the ReportNum field in tblReports. You can try to put a Non-Clustered Index on the tblReportsDetails.ReportNum field.

The big difference between a Non-Clustered Index and a Clustered Index (besides requiring unique values) is that a Clustered Index *literally* resorts your data based on that field. Think of it like a white pages phone book where the Clustered Index is on the LastName field, so everything is alphabetical. A yellow pages phone book has the Clustered Index on BusinessType.

A Non-Clustered index would be like the phone number in that book. The phone company can look up a phone number on their database and find the name attached to it rather easily (if they weren't obeying federal privacy regulations), but their database is sorted by the Clustered Index of LastName.

Lastly, remember that the more indexes (of either type) you have in your database, the worse performance gets. That's not to say you shouldn't apply CI/PKs or Non-CIs as necessary. Just don't go overboard. Apply your Non-CI to your tblReportsDetail.ReportNum field and see if it improves performance. If it doesn't seem to help, then dump it and look to see if there are other problems slowing up things.



Catadmin - MCDBA, MCSA
Remember, "Work" is a four letter word. And you know what your mother told you about using four letter words!

 

[romh]

ok, great explanation. Thanks alot.
To quote your last statement:
"Just don't go overboard. Apply your Non-CI to your tblReportsDetail.ReportNum field and see if it improves performance. If it doesn't seem to help, then dump it and look to see if there are other problems slowing up things."

The database in question is stil not a production/commerical version. So it has very few records. It is blazing fast with the front end in ASP.net.
How would I go about testing the indexes with a huge amnount of records. Can I for example write a loop that would insert 1 or 2 or 3.. million records. And from there, perform some selects, inserts and deletes. To me, this is the only strategy.

Thanks again for your time and response.

 

[Catadmin]

That would be a great way to test it. Make sure you back it up first, even though it is a test DB. That way, if you want to go back to Pre-Insert/Modify status, you have a backup saved to make it easy.

Also, check with your co-workers or other tech friends and see if they have test data you can grab to add to your test DB. Sometimes people keep stuff like that lying around so they don't have to recreate it every time they need to test code or database structures.



Catadmin - MCDBA, MCSA
Remember, "Work" is a four letter word. And you know what your mother told you about using four letter words!