SQL Server Performance

add a new varchar(max) field to a big table

Discussion in 'SQL Server 2008 General DBA Questions' started by WingSzeto, Jul 12, 2011.

  1. WingSzeto Member

    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?

  2. mmarovic Active Member

    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?
  3. satya Moderator

    Why you aer using VARCHAR in this case..its better to use CHAR ande create a new table if possible.
  4. shijiandaolo New Member

    How often queries involving that table will return comment column?
  5. preethi Member

    Some comments and questions?
    1. 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.
    2. Can users update the existing rows with the comment? If so, it may lead to page splits and fragmentation
    3. 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 ?)

Share This Page