2

I will short my question. (SQL-Server 2008)

Is it a good idea , when crating a new table, to create an index for each column?

Why i'm saying this?

Today I was creating a database, and my boss was behind me, and he said:

"Oh, it's good to create an index, for every column" (and then he showed me the graphic way to do this).

I'm studying indexes now and I'm not sure if this is a good idea, since every index will be updated everytime time there will be an insert\update on the table.

Some advices?

| improve this question | |
  • 4
    Looks very similar to the question you already asked some time ago: dba.stackexchange.com/questions/94409/… What can we add to that? Did you also check the duplicate questions? – spaghettidba Mar 11 '15 at 12:00
  • 3
    All I can say is indexes are for speeding up data retrieval. Only create index which would speed up your query. – Shanky Mar 11 '15 at 12:02
  • @spaghettidba yes i saw that question. But this is why i'm asking this. that question was about a table with 60+ indexes ( that was fixed with dba stackexchamge help). I'm not sure about creating and index for every collumn, and then create other usefull indexes. Why can't I create an index with the most important collumns? I would like to have a solid answer to my boss. – Racer SQL Mar 11 '15 at 12:10
  • No, it's not a good idea -- assuming we're talking about an OLTP system here, and not a datawarehouse. – Colin 't Hart Mar 11 '15 at 12:14
  • Voting to close - please clarify if you are talking about an OLTP or an OLAP type system. – Vérace Mar 11 '15 at 13:36
6

Would you find it useful to have the phone book, only having it as a list of Surnames with phone numbers, plus a list of Firstnames with phone numbers, and a list of towns with phone numbers? Because essentially, that's what you're getting if you index each column individually.

| improve this answer | |
  • Nice example. this is what i Was thinking, but couldn't fine a good example. – Racer SQL Mar 11 '15 at 12:35
1

Absolutely NO! Creating index on tables decreases insertion performance and you must create an index only if you really need that (if you include the column in select queries many times).

| improve this answer | |

Not the answer you're looking for? Browse other questions tagged or ask your own question.