Results 1 to 3 of 3
  1. #1
    Join Date
    Apr 2003
    Posts
    176

    Unanswered: Decreasing the varchar column sizes in a table

    I have a table in my database and the table has almost 45 columns and the rowsize is 10468 bytes.in that most of the colums have varchar datatypes and and i think coz of poor knowledge of the data most of the columns with varchar data were given more column length. Now i want to decrease the size of those columns and to see the row size would be around 8k Bytes.If i do this now, does it affect the table performance much....Infact can i do this as there is lot of data (almost 2 million rows) in the table.If it is possible is there anything to be taken care before changing the column lenghts.

    Thanks.

  2. #2
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Before doing anything, identify all NON-character fields, sum their storage up, and subtract the result and 8000 from 10468. What you get is the total number of characters that you would have to shrink your character-based fields by.

    Next, do a SELECT [field_name Width]=max(datalength(field_name))... on all character-based fields, sum the result across all those fields, and see if you get 8000 or less after addint the sum of NON-character-based fields storage sizes to it. If the result is higher, - you will have to decide if you want to truncate data in some of your character fields.

    If the latter is what you get, - consider normalizing the table. For example, if only some of the records contain values for a specific field, take the record key and that field and create a different table using the key as FK to the original (I hope you have a key).
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  3. #3
    Join Date
    Sep 2003
    Posts
    364
    Here's a proc that might help you analyze your char columns.
    Attached Files Attached Files

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •