Is it helpful to compress strings before placing in database?

I am using C# to populate a database table (SQL Server 2005 or 2008). I want the database table size to remain as smal开发者_运维知识库l as possible because Godaddy only allows 200 MB size databases and I want to get the most out of it. Should I compress my string data as I insert it, or is there a better way of keeping the database size down with some form of compression?

Consider too what you need to do with the strings once they're in the database. Any need to quwry? If so, compression might make that pretty difficult/expensive.

In most of the cases it's of benefit compressing string data. You'd better do the following:

1. Understand data compression https://msdn.microsoft.com/en-us/library/cc280449.aspx
2. Build strategy and planning https://msdn.microsoft.com/en-us/library/dd894051(v=sql.100).aspx
3. Do actions

For step 3. I use the following T-SQL script to compress a database (you can add modifications to it)

/*

  Description: This stored procedure can compress a database or list out estimations for the savings from possible compression. 
  Create the stored procedure on database level.

  Call:        
    USE AdventureWorksDW2014;
    GO

    EXEC dbo.sp_compress_database 
    @dbname = 'AdventureWorksDW2014',            -- Database to compress
    @compression_type = 'PAGE',                    -- Compression type: PAGE, ROW or NONE
    @mode = 1;                                    -- Mode can be: 1 -> compression and 0 -> estimation for compression

  Author: Igor Micev
  Date: 2012-10-25 

*/
SET ANSI_NULLS ON;
GO
SET QUOTED_IDENTIFIER ON;
GO
ALTER PROCEDURE [dbo].[sp_compress_database]
(
 @dbname NVARCHAR(100),@compression_type VARCHAR(10),@mode BIT
)
AS 
BEGIN
    SET NOCOUNT ON;

    IF OBJECT_ID('tempdb..#tables_for_compression') IS NOT NULL DROP TABLE #tables_for_compression;

    CREATE TABLE #tables_for_compression
    (
     [id] INT IDENTITY(1,1) NOT NULL PRIMARY KEY,[object_name] VARCHAR(100),[schema_name] VARCHAR(20),[index_id] INT,[partition_number] INT,[size_before_compression_KB] INT,[size_after_compression_KB] INT,[sample_size_before_compression_KB] INT,[sample_size_after_compression_KB] INT
    );

    IF @compression_type NOT IN('PAGE','ROW','NONE')
    BEGIN
        RAISERROR('Compression type is not valid.',16,1);

        RETURN;
    END;

    DECLARE @dynamic_cmd NVARCHAR(1000);
    DECLARE @tbl NVARCHAR(100);
    DECLARE @schema NVARCHAR(20);
    DECLARE tbl_cursor CURSOR FOR SELECT isc.TABLE_NAME,isc.TABLE_SCHEMA
                                  FROM INFORMATION_SCHEMA.TABLES AS isc
                                  WHERE isc.TABLE_CATALOG=@dbname AND isc.TABLE_TYPE='BASE TABLE';

    OPEN tbl_cursor;

    FETCH NEXT FROM tbl_cursor INTO @tbl,@schema;

--Examine the clustered indexes

    WHILE @@fetch_status=0
    BEGIN
        IF @mode=1
        BEGIN
            SET @dynamic_cmd='USE ['+@dbname+'] ALTER TABLE ['+@schema+'].['+@tbl+'] 
            REBUILD PARTITION = ALL WITH (ONLINE = ON, DATA_COMPRESSION = '+@compression_type+')';
        END;

        BEGIN TRY
            IF @mode=0
            BEGIN
                SET @dynamic_cmd='EXEC sp_estimate_data_compression_savings '''+@schema+''', '''+@tbl+''', NULL, NULL,'''+@compression_type+'''';

                INSERT INTO #tables_for_compression([object_name],[schema_name],index_id,partition_number,size_before_compression_KB,size_after_compression_KB,sample_size_before_compression_KB,sample_size_after_compression_KB)
                EXEC sp_executesql @dynamic_cmd;
            END;

            IF @mode=1
            BEGIN
                EXEC sp_executesql @dynamic_cmd;

                PRINT @schema+'.'+@tbl+' was compressed.';
            END;
        END TRY
                BEGIN CATCH
            PRINT 'Failed command: '+@dynamic_cmd;
        END CATCH;

        FETCH NEXT FROM tbl_cursor INTO @tbl,@schema;
    END;

    CLOSE tbl_cursor;

    DEALLOCATE tbl_cursor;

--Examine the nonclustered indexes. Exclude XML type indexes.

    IF @mode=1
    BEGIN
        DECLARE @ind_name NVARCHAR(100);
        DECLARE ncix CURSOR FOR SELECT ss.name AS [schema],OBJECT_NAME(ddips.object_id) AS table_name,si.name AS index_name
                                FROM sys.dm_db_index_physical_stats(DB_ID(),NULL,NULL,NULL,'SAMPLED') AS ddips
                                JOIN sys.indexes AS si ON ddips.index_id=si.index_id AND ddips.object_id=si.object_id
                                JOIN sys.tables AS st ON ddips.object_id=st.object_id
                                JOIN sys.schemas AS ss ON st.schema_id=ss.schema_id
                                WHERE si.index_id>1 AND si.[type]=2 AND ddips.page_count>64;

--Nonclustered indexes with more than 64 pages

        OPEN ncix;

        FETCH NEXT FROM ncix INTO @schema,@tbl,@ind_name;

        WHILE(@@fetch_status=0)
        BEGIN
            SET @dynamic_cmd='ALTER INDEX '+@ind_name+' ON '+@schema+'.'+@tbl+' 
            REBUILD WITH (ONLINE = ON, DATA_COMPRESSION = '+@compression_type+')';

            BEGIN TRY
                EXEC sp_executesql @dynamic_cmd;

                PRINT 'Index '+@ind_name+' was compressed.';
            END TRY
                    BEGIN CATCH
                PRINT 'Index '+@ind_name+' cannot be compressed. Err.Msg: '+@@error;
            END CATCH

            FETCH NEXT FROM ncix INTO @schema,@tbl,@ind_name;
        END;

        CLOSE ncix;

        DEALLOCATE ncix;
    END

    IF @mode=0
    SELECT *
    FROM #tables_for_compression;

    IF OBJECT_ID('tempdb..#tables_for_compression') IS NOT NULL DROP TABLE #tables_for_compression;
END