Sql Server 是否在类似数据上压缩数据库大小 [英] Does Sql Server Compress Database size on similar data

问题描述

只是为了找出我在生产环境中的数据库大小，我只是用 150 万行几乎相同的数据(主键除外)填充了我的表.它目前显示 261 MB...

Just to find out my would be DB size on production environment, I just populated my tables with 1.5 million rows of nearly same data (Except Primary key). It currently shows 261 MB...

现在，我是否可以依靠这一点，或者因为数据在所有其他列上几乎相似，SQL 服务器已经压缩了大小.IE.如果每行的值不同，大小会不同吗...

Now, Whether I can rely on this, or since the Data is almost similar on all other columns the SQL server has compressed the size. ie. Will the size be different if the values in each rows are different...

进一步.. 甚至列是否会影响数据库的大小?

Further.. Does even columns will null values contribute to the size of the DB ?

感谢您的时间...

这是我的架构...我也做了一些索引...

Edit : Here is my schema... And I have made some indexes too...

    CREATE TABLE [dbo].[Trn_Tickets](
        [ObjectID] [bigint] IDENTITY(1,1) NOT NULL,
        [TicketSeqNo] [bigint] NULL,
        [BookSeqNo] [bigint] NULL,
        [MatchID] [int] NULL,
        [TicketNumber] [varchar](20) NULL,
        [BarCodeNumber] [varchar](20) NULL,
        [GateNo] [varchar](5) NULL,
        [EntryFrom] [varchar](10) NULL,
        [MRP] [decimal](9, 2) NULL,
        [Commission] [decimal](9, 2) NULL,
        [Discount] [decimal](9, 2) NULL,
        [CashPrice] [decimal](9, 2) NULL,
        [CashReceived] [decimal](9, 2) NULL,
        [BalanceDue] [decimal](9, 2) NULL,
        [CollectibleFrom] [char](1) NULL,
        [PlaceOfIssue] [varchar](20) NULL,
        [DateOfIssue] [datetime] NULL,
        [PlaceOfSale] [varchar](20) NULL,
        [AgentID] [int] NULL,
        [BuyerID] [int] NULL,
        [SaleTypeID] [tinyint] NULL,
        [SaleDate] [smalldatetime] NULL,
        [ApprovedBy] [varchar](15) NULL,
        [ApprovedDate] [smalldatetime] NULL,
        [InvoiceStatus] [char](1) NULL,
        [InvoiceRefNo] [varchar](15) NULL,
        [InvoiceDate] [smalldatetime] NULL,
        [BookPosition] [char](2) NULL,
        [TicketStatus] [char](2) NULL,
        [RecordStatus] [char](1) NULL,
        [ClosingStatus] [char](2) NULL,
        [ClosingDate] [datetime] NULL,
        [UpdatedDate] [datetime] NULL,
        [UpdatedUser] [varchar](10) NULL,
     CONSTRAINT [PK_Trn_Tickets] PRIMARY KEY CLUSTERED 
    (
        [ObjectID] ASC
    )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
    ) ON [PRIMARY]

希望能帮到你

推荐答案

SQL Server 2005 和 2008 Express 不会压缩您的数据.SQL Server 2008 可以使用页面压缩，但仅限于企业版.NULL 列在行中占据一位.

SQL Server 2005 and 2008 Express will not compress your data. SQL Server 2008 can use page compression, but only on Enterprise Edition. NULL columns occupy one bit in the row.

从你的数据描述来看，听起来更像是一个普通的归一化问题.将重复值分离到查找表中，仅存储不同的组合，并加入查找表.这将通过架构设计保存数据，并适用于所有数据库平台、所有版本、所有 SKU.

From the description of your data, sounds more like a problem of ordinary normalization. Separate the repeat values into a lookup table, store only distinct combinations, join agaisnt the lookup table. This will save data by schema design and will work on all DB platforms, all versions, all SKUs.

这篇关于Sql Server 是否在类似数据上压缩数据库大小的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！