Post reply

Change type from nvarchar to varchar

  • August 19, 2019 at 6:47 am

    #3672195

    Hi, everyone.

    I have a curious question.

    We have big tables where some columns are nvarchar type, but there will not be unicode never. And we decided to change type to varchar. Steps are:

    1. Delete indexes, constraints, statistics and etc.
    2. Change type from nvarchar to varchar.
    3. DBCC CLEANTABLE ('dbo.tablename', 0)

      WITH NO_INFOMSGS;

      GO

    4. alter table dbo.tablename rebuild PARTITION=ALL;
    5. Add removed indexes, constraints, statistics and etc.

    I did it on six tables. When I did it on TEST, there released about 200GB space. And then I did these steps on PRODUCTION. On three tables I forgot to do steps 3 and 4. Change type and add indexes. When I remember about it, delete indexes and other constraints, did steps 3 and 4 and add indexes repeatedly. But finally I saw that only 140GB released. Can anybody explain me, are these steps dependency on before adding or after adding steps 3 and 4.

  • August 20, 2019 at 7:10 am

    #3672631

    Thanks for posting your issue and hopefully someone will answer soon.

    This is an automated bump to increase visibility of your question.

  • August 20, 2019 at 2:48 pm

    #3672808

    It should still work. Was the data the same? The cleantable should rebuild the table and recover space.

  • August 20, 2019 at 2:49 pm

    #3672809

    The data could be structured differently, different amounts on pages, so reclaiming space might end up looking different later. Check fillfactor and any other config differences as well. Might be something that affects how much space you had used and get reclaimed.

  • August 21, 2019 at 11:53 am

    #3673068

    This is my fault. I forgot about one other step, which release additional steps 🙂

Viewing 5 posts - 1 through 4 (of 4 total)

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