SQL Server Table with huge data & Columns and not all the columns are unused.


  • Hi,

    We have a table which is of a huge size 300+GB and it is growing at a 25Gb per month. We have identified that there are columns which are not being used.  Is it possible to do below option

    Do a partition based on column and then implement compression based on the unused columns.

    Let me know if it is doable and also if possible any examples




    Thursday, October 18, 2018 5:06 PM


All replies

  • First of all do not raise duplicate threads, I have deleted the other one.  What do you mean by column not being  used, why not remove the columns



    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My TechNet Wiki Articles


    Thursday, October 18, 2018 5:26 PM
  • one way is to define them as sparse columns if they're going to be sparingly used.

    If they're not supposed to be not used at all, then remove them from the table itself

    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Thursday, October 18, 2018 6:11 PM
  • We get these tables from external source and gets replicated(transactional replication) to different other systems. We wanted to have a full copy from external source.

    External------> A(Our servers)------------>B,C

    about  letters are refferred as servers

    I know we can use a filter condition in replication to transfer the data and columns for that i need to do re-initiliaze. Along with that i want to list out a plan if i can use partitioning on the unused columns and compression feature





    Friday, October 19, 2018 6:39 PM
  • You can vertically filter your articles so that only a subset of the columns go to subscribers.

    Here is an example of this.

    Monday, October 22, 2018 2:11 PM
  • Thank you.


    Thursday, November 8, 2018 3:58 PM