I have 60 columns in a table.
1). I want to add one more column to that table. Will there be any impact on performance?
2). How many columns can I add?
3). any idea for the avoid recursion. [I have no idea here - annakata]
+3
A:
Yes, but one more column is less of a problem than the fact that you already have 60.
I bet most of them are nullable?
With very wide tables (many columns) it becomes harder to write maintainable SQL. You are forced to deal with lots of exceptions due to the NULLS.
See also this post which asks how many fields is too many?
Ed Guiness
2009-02-18 08:50:54
A:
If I were you I would be less concerned with the fact that you have to add one more column, and more concerned with deciding whether 60 columns is appropriate.
MrEdmundo
2009-02-18 08:53:24
Number of columns has little bearing on normalization - it would be quite easy to have 60 independent variables that are unique for a given row. I'd wait until we see what's in those columns before passing judgment.
paxdiablo
2009-02-18 09:39:25
Fair point. I guess I was making an assumption based on how poorly written his question was.
MrEdmundo
2009-02-18 10:54:39
I strongly disagree - it can be valid, but a large number of columns is definitely a DB smell. It's unlikely even a 60 field object can't be normalised to child tables based on read/write frequency.
annakata
2009-02-18 10:58:39
This is NOT an answer for Lalchand Saini's question. It lacks info, but Lalchand might have a specific reason for asking this question.
Sung Meister
2009-02-21 05:45:58
A:
Will there be any impact on performance?
If you are adding a "Notes" type TEXT column, or a Blob storing the Image of a User, and most/many of your queries are SELECT * FROM MyTable then you will definitely be creating a performance issue.
If you always explicitly only name the columns your qurey needs, like: SELECT Col1, ColX, ColN FROM MyTable, then adding a new column will have little to no impact on performance - but wider rows mean fewer records per data page, so there is SOME impact, and if you are adding an Index to the new column then that index has to be maintained too - but if your application needs it then that is a necessary "cost".
We have plenty of tables with > 60 columns. However, I would like to think that that is By Design, rather than because the table has just grown willy-nilly.
Kristen
2009-02-18 11:02:21