Post reply

ALTER TABLE change NVARCHAR size...

  • October 23, 2012 at 5:38 am

    #265960

    Hi,

    Is it true that when you change a column from NVARCHAR(512) to NVARCHAR(1024) what happens is that SQL Server creates a new column and copies the values and the old column stays in the pages?

    If so, will a index rebuild (the clustered index), remove the old column space from the data pages?

    Thanks,

    Pedro


    If you need to work better, try working less...

  • October 23, 2012 at 8:17 am

    #1551933

    well in an unscientific test ( I increased column sizes in a few tables in a test database ) I saw no change in the table size. I have to admit I don't know what sql server does behind the scenes, I do know many third party tools will rebuild a table if you alter a column.

    A clustered index rebuild should recover any fragmentaion/space but I didn't see any difference in my tables after altering columns.

    Hope that helps

    [font="Comic Sans MS"]The GrumpyOldDBA[/font]
    www.grumpyolddba.co.uk
    http://sqlblogcasts.com/blogs/grumpyolddba/

  • October 23, 2012 at 8:26 am

    #1551939

    According to this post the old data stays in the data pages... probably a rebuild will clean it.

    http://stackoverflow.com/questions/7743080/does-alter-table-alter-column-interrupt-ongoing-db-access

    Pedro


    If you need to work better, try working less...

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply