| Author |
Topic |
|
doom
Starting Member
7 Posts |
 Posted - 2005-08-13 : 10:22:01
|
| I am new to this databasing field. I have been trying to design a database for a project. I would like to know how much is too much in case of indexing? I mean is it ok to have a primary key and an index on each table? If the table is going to have approx 6-7 columns and about 100000 records?How much extra space will it require? Can i test the efficiency some how? |
|
|
rockmoose
SQL Natt Alfen
3279 Posts |
Posted - 2005-08-13 : 10:58:46
|
| Depends on database usage.Indexes hurt insert/update/delete performance, and maintenance (reindexing, update statistics) takes longer.And storage space gets bigger.Edit: the above is not strictly true, they help to find the rows affected by the dml(upd,del,ins) statement, once that is done the physical operation takes longer since indexes must be updated as well.So, it's a balance, where eventually You cross the "too much" line.They help a lot when You join and search on indexed columns.You can go easy on the indexes at first, and put indexes where You are sure they will be of use.( known searches, index on fk columns You know will be joined... )Common sense and a some experience can get You pretty far for the initial index set-up.Moitor database performance, with custom logging in Your sprocs / application,And/Or collect query statistics with Sql Profiler.You can use those statistics with the Index Tuning Wizard if You like.Analyze the execution plans of frequently run queries / or ones that take a long time in QA.rockmoose |
 |
|
|
elwoos
Master Smack Fu Yak Hacker
2052 Posts |
Posted - 2005-08-13 : 11:17:59
|
| Rockmoose is right, there are many factors that can affect it but in general terms a primary key and an index on each table is likely to be oksteveAlright Brain, you don't like me, and I don't like you. But lets just do this, and I can get back to killing you with beer. |
|
|
|
rockmoose
SQL Natt Alfen
3279 Posts |
Posted - 2005-08-13 : 11:44:23
|
 cheers.I do:Always have a primary key.Always have clustered index, (on the pk, or other, depends, but usually the pk)Index on FK columns.that's the baseline.Next index on frequently searched columns.If You search with LIKE('%x') statements an index will not help though.After that it's more fine tuning.To find space used by data and indexes You can useEXEC sp_spaceused '<table_name>'rockmoose |
|
|
|
doom
Starting Member
7 Posts |
Posted - 2005-08-13 : 12:38:09
|
| Thanks a lot. I understand what you meant by maintaining a fine line.
I made a similar table and inserted sample data and played with their indexes to see the space and time.
I have one last query, whats the difference between clustered and non clustered indexes? |
|
|
|
Stalker
Yak Posting Veteran
80 Posts |
Posted - 2005-08-14 : 00:41:24
|
| >>whats the difference between clustered and non clustered indexes?why not just to have a look in BOL ? |
|
|
|
doom
Starting Member
7 Posts |
Posted - 2005-08-14 : 01:01:30
|
| What is Bol?
I tried looking up the sql servers help (i feel its very helpful ), but couldn't understand whats the difference in real. |
|
|
|
Stalker
Yak Posting Veteran
80 Posts |
Posted - 2005-08-14 : 01:19:56
|
| Books On-Linethis is mssql help.The basic difference between clustered and non-clustered indexes is that if a table has a clustered index on (for example) field1 - the table is physically ordered on this field (in reality this is not completely true, but for some extent you can think about it this way). This is helpfull for some kind of queries on this table. |
|
|
|
doom
Starting Member
7 Posts |
Posted - 2005-08-14 : 01:41:53
|
| Thanks a lot. I will go through the help completely.
|
|
|
|
|
How much is too much in indexing?