I heard a lot about building index, but they are all theories, some times these thories are in conflict. I wonder in a real world, how a DBA build indexes and maintain them regularly? More specific, is it true that every table must have a clustered index? or every table must have a unique index for intergrity?
Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations Shaun E on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Can anybody write FAQ on how to build good index?
- Thread starter masds
- Start date
mrdenny
Programmer
- May 27, 2002
- 11,595
No every table does not need a clustered index. Although with large tables a clustered index will definetly help things out.
When deciding what columns to put your indexes on look at your queries. The columns that the queries use to query are the columns that need indexes on them.
Every table should have a primary key. This will automatically generate a unique index on the table. So the answer is yes, every table should have a unique index.
When deciding the fill factor this is where the guess work starts to come into play. You'll need to figure out how much new data will be getting placed into the index, and how often the data will be changed.
If the data never changes set the fill factor to 100% (there isn't any need to waist the space on the disk). If the fill factor changes a little start to move towards 90%. If the data changes a lot then move towards 40-50%.
I hope this answeres your questions. If not yet me know.
Denny
MCSA (2003) / MCDBA (SQL 2000)
--Anything is possible. All it takes is a little research. (Me)
![[noevil]](/data/assets/smilies/noevil.gif "[noevil] [noevil]")
(My very old site)
When deciding what columns to put your indexes on look at your queries. The columns that the queries use to query are the columns that need indexes on them.
Every table should have a primary key. This will automatically generate a unique index on the table. So the answer is yes, every table should have a unique index.
When deciding the fill factor this is where the guess work starts to come into play. You'll need to figure out how much new data will be getting placed into the index, and how often the data will be changed.
If the data never changes set the fill factor to 100% (there isn't any need to waist the space on the disk). If the fill factor changes a little start to move towards 90%. If the data changes a lot then move towards 40-50%.
I hope this answeres your questions. If not yet me know.
Denny
MCSA (2003) / MCDBA (SQL 2000)
--Anything is possible. All it takes is a little research. (Me)
(My very old site)
- Thread starter
- #3
mrdenny
Programmer
- May 27, 2002
- 11,595
A unique index or primary key doesn't need to be a single column. You can have a composite key, which is a key made up of more than one column.
If you can not generate uniqueness that way, then yes you could add a column that is a counter to provide uniqueness.
Denny
MCSA (2003) / MCDBA (SQL 2000)
--Anything is possible. All it takes is a little research. (Me)
(My very old site)
If you can not generate uniqueness that way, then yes you could add a column that is a counter to provide uniqueness.
Denny
MCSA (2003) / MCDBA (SQL 2000)
--Anything is possible. All it takes is a little research. (Me)
(My very old site)
- Thread starter
- #5
Performance is a hard thing to determine with indexing because each case is unique. Remember what Denny said about what your most commonly used queries search on, but also remember that if you have too many indexes, it can kill your performance.
As far as combined keys or a single key, this is more dependant on your data structure than it is on performance. For instance, you can have tables that "join" information on two other tables, in which case, the third "join" table might have a combined index or no index at all. The best example of this is something we had at my old job. We had a table called CODE that had Service Codes (Code_ID (the unique id), ServiceCode_NO, ServiceCode_Description, etc.) for our customers. We had a table called AUTO (Auto_ID (unique id for the table), Customer_ID, ServiceCode_ID, SpecialPrice, etc.) which had our monthly Automatically billed charges for our customers. And we had a table called XREF that had the ServiceCode_ID and the AutoCharge_ID in it and nothing else. This table served as a link for the ServiceCode_ID so we could pull in the ServiceCode_Description in reports and on the screens of our application.
Don't base indexes solely on one theory of indexing or another. Do what is best for your application, your queries and most importantly what is best for your data structure. Indexes should suit these three, not the other way around.
Catadmin - MCDBA, MCSA
Beware the error of pre-emptive poultry inventory!
As far as combined keys or a single key, this is more dependant on your data structure than it is on performance. For instance, you can have tables that "join" information on two other tables, in which case, the third "join" table might have a combined index or no index at all. The best example of this is something we had at my old job. We had a table called CODE that had Service Codes (Code_ID (the unique id), ServiceCode_NO, ServiceCode_Description, etc.) for our customers. We had a table called AUTO (Auto_ID (unique id for the table), Customer_ID, ServiceCode_ID, SpecialPrice, etc.) which had our monthly Automatically billed charges for our customers. And we had a table called XREF that had the ServiceCode_ID and the AutoCharge_ID in it and nothing else. This table served as a link for the ServiceCode_ID so we could pull in the ServiceCode_Description in reports and on the screens of our application.
Don't base indexes solely on one theory of indexing or another. Do what is best for your application, your queries and most importantly what is best for your data structure. Indexes should suit these three, not the other way around.
Catadmin - MCDBA, MCSA
Beware the error of pre-emptive poultry inventory!
mrdenny
Programmer
- May 27, 2002
- 11,595
dito.
Denny
MCSA (2003) / MCDBA (SQL 2000)
--Anything is possible. All it takes is a little research. (Me)
(My very old site)
Denny
MCSA (2003) / MCDBA (SQL 2000)
--Anything is possible. All it takes is a little research. (Me)
(My very old site)
- Thread starter
- #8
mrdenny
Programmer
- May 27, 2002
- 11,595
The purpose of the primary key is for database integrity. Indexes are used for performance.
Denny
MCSA (2003) / MCDBA (SQL 2000)
--Anything is possible. All it takes is a little research. (Me)
(My very old site)
Denny
MCSA (2003) / MCDBA (SQL 2000)
--Anything is possible. All it takes is a little research. (Me)
(My very old site)
- Status
Similar threads
- Locked
- Question
- Locked
- Question
- Locked
- Question
- Locked
- Question