subdivided

table

index

view

and can be across servers!

tool to define how rows are to be partitioned

CREATE PARTITION FUNCTION

pfQty (int)

AS RANGE LEFT FOR VALUES (50,100)

so,

Partition1        Partition2      Partition3

Col<=50                col > 50 and <=100      col > 100

FOR RIGHT:

col <=50 loses its = and goes right to col > 100 (partition1 to partition3)

col >50 gains its = from <=100 (partition2)

RESULT: Col < 50  col >=50 and <100  col> =100

once the function is done

mapping is done by a database object

to a set of filegroups

they store data

relational: rows and columns (intersection)

on rows

on columns but

not both at the same time

on data tables

on nonclustered indexes

what is

sp_estimate_data_compression_savings

this is a stored procedure that came with the 2008 version

you can estimate space savings

without having to actually compress a table first

CREATE TABLE

ALTER TABLE

CREATE INDEX

ALTER INDEX

ALTER TABLE mytable REBUILD WITH (DATA_COMPRESSION = ROW);

ALTER TABLE mytable REBUILD WITH (DATA_COMPRESSION = COLUMN);

this is the preferred compression

use this where data to be compressed has a higher % of unique data

Many rows (going down)

This is to reduce redundant data

This is the preferred method

Use when you have repetitive data

Row versus Page

Compression

Use row when you have a lot of unique data to compress

Use Page (many rows) when you have a lot of repetitive data to compress

This setting is used when you have a lot of

Rows that are null for a particular column

if so, make that column SPARSE

must be nullable column

no constraint or ruled column

can't use IDENTITY

can't use ROWGUIDCOL

can't use datatypes GEOGRAPHY, GEOMETRY, TEXT, NTEXT, IMAGE, TIMESTAMP, VARBINARY(MAX), FILESTREAM

no alias data types

cannot be compressed

a total or computed column cannot be sparse

no merge replication

no primary key

no clustered index involvement

use these for sparse columns

sparse columns should have a high percentage of null valued rows