Data compression and SQL Server

Data compression was made available in SQL server some 12 years ago, with 2005 SP2. Our company uses data compression extensively because:

  1. In most scenarios it provides better performance than uncompressed tabels (that is, for page compression)
  2. It requires less storage

My questions:

  1. When will this veryimportant and valuable option be available in ER/Studio?
  2. Is there an alternative to include it in the DDL?


Example of create statement:


Column_1 char(10) NULL

) with (data_compression = page )

  • Hi! I have something that might help. I modified (I think I did, cannot remember if I found it when googling) one of the macros (Selectively Add MS SQL Permissions to PostSQL). Saved as Compression.bas

    The HTML Clipboard

    'DESCRIPTION:  This macro will add permissions to the PostSQL of
    '              any selected table in an Sybase or MS SQL Server physical model.
    'DATE:  3/12/2015
    Sub Main
        Dim MyDiagram As Diagram
        Dim MyEntity As Entity
        Dim MyModel As Model
        Dim MySelObj As SelectedObject
        Dim MySubModel As SubModel
        Dim sqlcommand As String
        Dim ID As Integer
        Dim ObjectName As String
        Dim MyView As View
        'Set ERStudio variable
        Set MyDiagram = DiagramManager.ActiveDiagram
        Set MyModel = MyDiagram.ActiveModel
        Set MySubModel = MyModel.ActiveSubModel
            'create dialog
        Begin Dialog UserDialog 430,189,"Select Compression" ' %GRID:10,7,1,1
            Text 30,14,140,14,"Compression type",.Text2
            CheckBox 60,42,120,14,"PAGE",.PageChbx
            CheckBox 60,70,110,14,"ROW",.RowChbx
            CheckBox 60,119,120,14,"Empty PostSQL",.EmptyChbx
            OKButton 50,154,140,21
            CancelButton 230,154,140,21
        End Dialog
        Dim dlg As UserDialog
        If Dialog(dlg) = -1 Then
            For Each MySelObj In MySubModel.SelectedObjects
                'make sure selected object is an Entity
                If MySelObj.Type = 1 Or MySelObj.Type = 16 Then
                    ID = MySelObj.ID
                    If MySelObj.Type = 1 Then
                        Set MyEntity = MyModel.Entities.Item(ID)
                        ObjectName = MyEntity.Owner & "." & MyEntity.TableName
                    ElseIf MySelObj.Type = 16 Then
                        Set MyView = MyModel.Views.Item(ID)
                        ObjectName = MyView.Owner & "." & MyView.Name
                    End If
                    'Add PAGE Compression
                    If dlg.PageChbx = 1 Then
                        sqlcommand = ""
                        MyEntity.PostSQL = ""
                        sqlcommand = "ALTER TABLE " & ObjectName & " REBUILD PARTITION = ALL" & vbCrLf & "WITH (DATA_COMPRESSION = PAGE);" & vbCrLf & "GO"
                    End If
                    'Add Row Compression
                    If dlg.RowChbx = 1 Then
                        sqlcommand = ""
                        MyEntity.PostSQL = ""
                        sqlcommand = "ALTER TABLE " & ObjectName & " REBUILD PARTITION = ALL" & vbCrLf & "WITH (DATA_COMPRESSION = ROW);" & vbCrLf & "GO"
                    End If
                    'Empty PostSQL
                    If dlg.EmptyChbx = 1 Then
                        sqlcommand = ""
                        MyEntity.PostSQL = ""
                    End If
            End If
                    If MySelObj.Type = 1 Then
                        MyEntity.PostSQL = MyEntity.PostSQL & sqlcommand
                    ElseIf MySelObj.Type = 16 Then
                        MyView.PostSQL = MyView.PostSQL & sqlcommand
                    End If
                    sqlcommand = ""
        End If 'Dialog
    End Sub
  • In reply to epmoj:

    Just noticed that you need to select all the entities you wish to apply compression on and then EDIT the macro and run it from the edit window. Maybe someone with more knowledge of this coding language can help here. Perhaps this is an issue with old SaxBasic and ERDA version 17?
  • In reply to epmoj:

    Thanks, i will try this it.
    It's a nice workaround, however, these alter statements may be lost or ignored during schema compare. I Guess i'll find out.