Do i add column in existing table or new? | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Do i add column in existing table or new?

Hi, I have table with 60 million records , now i wanted to add 4 more columns in same table.
But i have only 6 million correspond records to previous existing records. 1-
Do i create a separate table to add 6 million records.
2-
Do i alter table to add 4 more columns? If i will add 4 more columns in existing table, will my table occupy space for 34 million records which i am not going to insert? My new columns are col0 varchar(3) default null,
col1 varchar(1) default null,
col2 varchar(2) default null,
col3 varchar(3) default null ________________________________________________
~* Opinions are like a$$holes, everyone got one. *~
Since you are trying to add some detail, which is applicable only for 10% of the total rows, I’d recommend adding a new tale. And a VARCHAR(1) column doesnt make any sense to me. It should be CHAR(1) Roji. P. Thomas
Microsoft SQL Server MVP
http://toponewithties.blogspot.com
Will this 4 new columns frequently be requested and need to be JOINed? I would also go for a CHAR(1). This will always occupy 1 byte. But making it a VARCHAR(1) paradoxically doesn’t save you any storage space. Since it is a variable-length column it has always an entry in the column-offset array (=2 bytes) plus the 1 byte for the data itself (when filled). So, in the "worst case" you need 3x as much storage space when using VARCHAR(1). Btw, why would you create a DEFAULT NULL? —
Frank Kalis
Moderator
Microsoft SQL Server MVP
Webmaster:http://www.insidesql.de
Hi, I was testing with my existing table with 40 million records and there are 3 columns which are NULL after dropping those columns i still can see same space allocated to table. If this is case then if i add 1 column which will take 10% space of all existing records, will it increase table physical space consumption by 10% or 100% (although 90% records are not filled)? Not sure if my concept is correct for 1- char type column always occupy space even there is no value assigned to column.
2- varchar type column only occupy space when some value is inserted. But after looking at Frank calrification seem char is better than varchar. Frank, you are right one of the point to add all columns in existing table is to avoid join when doing query. If i put default value NULL i expect table to not consume space for those records which are not inserted (for my case i only have 10% records against 40 million records), right? ________________________________________________
~* Opinions are like a$$holes, everyone got one. *~
When a fixed-length column is nullable (which it must be for a DEFAULT of NULL anyway), there is no need for the default. SQL Server will insert the NULL marker when you don’t supply a value. And a fixed-length column will always occupy the full space, regardless if there is data stored or not (including NULL). —
Frank Kalis
Moderator
Microsoft SQL Server MVP
Webmaster:http://www.insidesql.de
Thank you, i add columns in my existing table. ________________________________________________
~* Opinions are like a$$holes, everyone got one. *~
]]>