資料壓縮可以依不同 table 不同 Partition 定義不同的壓縮方式

CREATE DATABASE [test] ON  PRIMARY
( NAME = N’test’, FILENAME = N’C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\test.mdf’),
FILEGROUP [fg2]
( NAME = N’test2′, FILENAME = N’C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\test2.ndf’),
FILEGROUP [fg3]
( NAME = N’test3′, FILENAME = N’C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\test3.ndf’)
LOG ON
( NAME = N’test_log’, FILENAME = N’C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\test_log.ldf’)
GO

USE [test]
GO
CREATE PARTITION FUNCTION [fn_PartFn](varchar(50)) AS RANGE LEFT FOR VALUES (N’F’,N’L’)

CREATE PARTITION SCHEME [fn_PartSc] AS PARTITION [fn_PartFn] TO ([PRIMARY], [fg2],[fg3])

CREATE TABLE [dbo].[tb](
    [c1] [int] NOT NULL,
    [c2] [char](10) NULL,
    [c3] [varchar](50) NOT NULL
    PRIMARY KEY (c3,c1)
)ON [fn_PartSc]([c3])

CREATE TABLE [dbo].[tb2](
    [c1] [int] NOT NULL,
    [c2] [char](10) NULL,
    [c3] [varchar](50) NOT NULL
    PRIMARY KEY (c3,c1)
)ON [fn_PartSc]([c3])

ALTER TABLE [dbo].[tb] REBUILD PARTITION=ALL
WITH
(DATA_COMPRESSION = NONE ON PARTITIONS(1),
DATA_COMPRESSION = ROW ON PARTITIONS(2),
DATA_COMPRESSION = PAGE ON PARTITIONS(3)
)

ALTER TABLE [dbo].[tb2] REBUILD PARTITION = 1 WITH(DATA_COMPRESSION = ROW )
ALTER TABLE [dbo].[tb2] REBUILD PARTITION = 2 WITH(DATA_COMPRESSION = PAGE )
ALTER TABLE [dbo].[tb2] REBUILD PARTITION = 3 WITH(DATA_COMPRESSION = NONE )

insert tb values(1,’a’,’a’),(2,’g’,’g’),(3,’m’,’m’)
insert tb2 values(1,’a’,’a’),(2,’g’,’g’),(3,’m’,’m’)

SELECT $PARTITION.fn_PartFn(c3) AS Partition,
COUNT(*) AS [COUNT] FROM tb
GROUP BY $PARTITION.fn_PartFn(c3)
ORDER BY Partition ;

SELECT
p.partition_number AS [PartitionNumber],
p.data_compression AS [DataCompression],
prv.value AS [RightBoundaryValue],
fg.name AS [FileGroupName],
CAST(p.rows AS float) AS [RowCount],
OBJECT_NAME(idx.Object_ID) ObjectName
FROM
sys.tables AS tbl
INNER JOIN sys.indexes AS idx ON idx.object_id = tbl.object_id
INNER JOIN sys.partitions AS p ON p.object_id=CAST(tbl.object_id AS int) AND p.index_id=idx.index_id
INNER JOIN sys.indexes AS indx ON p.object_id = indx.object_id and p.index_id = indx.index_id
LEFT OUTER JOIN sys.destination_data_spaces AS dds ON dds.partition_scheme_id = indx.data_space_id and dds.destination_id = p.partition_number
LEFT OUTER JOIN sys.partition_schemes AS ps ON ps.data_space_id = indx.data_space_id
LEFT OUTER JOIN sys.partition_range_values AS prv ON prv.boundary_id = p.partition_number and prv.function_id = ps.function_id
LEFT OUTER JOIN sys.filegroups AS fg ON  fg.data_space_id = dds.data_space_id or fg.data_space_id = indx.data_space_id
ORDER BY
[PartitionNumber],idx.object_id ASC

USE master
DROP DATABASE test

發表迴響

在下方填入你的資料或按右方圖示以社群網站登入:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / 變更 )

Twitter picture

You are commenting using your Twitter account. Log Out / 變更 )

Facebook照片

You are commenting using your Facebook account. Log Out / 變更 )

Google+ photo

You are commenting using your Google+ account. Log Out / 變更 )

連結到 %s

%d 位部落客按了讚: