We are using SQL 2008 std. We have a table holding over 22 million records and is being used frequently. We have a need to add a varchar(max) field to this table and I am wondering if I should create a seperate table for it or just add it to the existing table. This field will be used for any newly created records and not all new records will have value in it. We are guessing maybe 10~20% of the newly created records may utilize this field, which users put comment in it. What are the pros and cons for putting it in an exsting table or creating a new table for it? w
1. How often your queries will use a clustered index? What is the frequency of these queries? How import they are? What percent of overall query execution time for queries involving that table use clustered index? 2. How often queries involving that table will return comment column? 3. How often comment column value will be updated after the initial insert?
Some comments and questions? If you add a new column with varchar and NULL it appends to the existing column list. it will not add any data to the existing rows. That means, no overhead on execution of a statement like this ALTER TABLE table1 ADD comment varchar(max) NULL. Can users update the existing rows with the comment? If so, it may lead to page splits and fragmentation Do you access the data (of the new column) separately or with the rest of the data? (If you keep the comment in a new table, will it lead to additional OUTER JOINs or another DB call ?)