dcsimg
Results 1 to 2 of 2

Thread: [RESOLVED] Problems changing column size (sql server 2012)

Hybrid View

  1. #1

    Thread Starter
    Fanatic Member venerable bede's Avatar
    Join Date
    Sep 2002
    Location
    The mystic land of Geordies
    Posts
    1,018

    Resolved [RESOLVED] Problems changing column size (sql server 2012)

    I have a table in my db called 'MyTable'.
    When I run the following against the table :
    Code:
    select sum(max_length)
    from sys.columns
    where object_id = object_id('MyTable')
    I get the follwing result : 2644

    As the page size in sql server is 8k this would indicate to me that I have an extra 5416 bytes to play with.
    I want to increase the size of a char field from 2000 to 6000. To do that I run the following :
    Code:
    ALTER TABLE dbo.MyTable ALTER COLUMN MyCol CHAR(6000);
    The problem is, when I run the query I get the error :

    Creating or altering table 'MyTable' failed because the minimum row size would be 9472, including 14 bytes of internal overhead. This exceeds the maximum allowable table row size of 8060 bytes.

    I dont understand. By my calculations this would add 4000 to the 2644 leaving me plenty of room.
    I realise I could make it VARCHAR but for certain reasons I have been asked to keep it a char.
    Does anyone have any ideas ?

    Parksie

  2. #2

    Thread Starter
    Fanatic Member venerable bede's Avatar
    Join Date
    Sep 2002
    Location
    The mystic land of Geordies
    Posts
    1,018

    Re: Problems changing column size (sql server 2012)

    Sorted.
    It seems that you have to rebuild the primary key after changing the column.
    Will be interested to see if this has any adverse effects on child tables e.t.c.

    Parksie

Posting Permissions

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



Featured


Click Here to Expand Forum to Full Width