Why Should I Care About Partitioned Views SQL

Why Should I Care About … Partitioned Views?

SQL Saturday Madison: Silver Sponsors

SQL Saturday Madison: Gold Sponsor

Frederick (Rick) Lowe Data FLowe Solutions LLC rick@data-flowe. com Data. FLowe https: //dataflowe. wordpress. com/ http: //data-flowe. com/

I’m Not Here to Start the Retrolution • Partitioned tables are awesome for almost all partitioning cases • Historical takeaway from this talk – you’re not doomed if you have pre-2016 Standard Ed. • Also, there a couple of cases where views make sense even in EE

First … Why Are Partitioned Tables So Exciting (review)? • Appears to be a normal table • Table is broken into multiple partitions based on the value of a single column • Migrating a partition in or out is a metadataonly operation (i. e. basically instantaneous) • Select statements can eliminate partitions

Working With Partitioned Tables (review) • Create a partition function and scheme • Build clustered and nonclustered indexes on the partition scheme • Views, CRUD etc reference a partitioned table pretty much like any other table • SQL Server does all the heavy lifting

Partitioned Table (review) Update/Insert/Select My. Table 2005? 2006? 2007? 2008? Partition Scheme Associated With Table P 0 P 1 P 2 P 3 … … …

Working With Partitioned Views Build individual tables Typically, create check constraints on tables View unions together all underlying tables *Create procedure determines which table to insert the data into • *Update/Delete also need to either determine which table to work on or operate on all * Indicates operations can actually sometimes be done on view • •

Partitioned View Worst Case Update. My. Table 2005? Insert Select Create. My. Table vw. My. Table 2006? 2007? 2008? Data Boundaries Coded Into View / Procs My. Tbl_05 My. Tbl_06 My. Tbl_07 My. Tbl_08 … … …

Partitioned View If You’re Lucky Update. My. Table 2005? Insert Select Create. My. Table vw. My. Table 2006? 2007? 2008? Data Boundaries Coded Into View / Procs My. Tbl_05 My. Tbl_06 My. Tbl_07 My. Tbl_08 … … …

Working With Partitioned Views

Defining a Partitioned View CREATE VIEW Sales. vw. Sales. Order. Header AS SELECT Order. Date, Sales. Order. ID, … FROM Sales. Order. Header_2005 UNION ALL SELECT Order. Date, Sales. Order. ID, … FROM Sales. Order. Header_2006 UNION ALL SELECT Order. Date, Sales. Order. ID, … FROM Sales. Order. Header_2007 UNION ALL SELECT Order. Date, Sales. Order. ID, … FROM Sales. Order. Header_2008; CREATE PROCEDURE Sales. Order. Header_Create … AS INSERT INTO Sales. Order. Header_2008( Order. Date, Sales. Order. ID, …. ) VALUES( …. )

Adding A New Table to View • • • Create table with appropriate structure Add check constraints Import data, if appropriate Create indexes Add new UNION ALL to view Update Stored Procedures

Removing Table From View • Update stored procedures, if necessary • Remove appropriate UNION ALL from view • Data can now be truncated / Archived / etc

Demo

“Table” Elimination • Partitioned tables only search partitions that match WHERE clause • Note: in practice, this typically isn’t a performance “game changer” • SQL Server will leverage check constraints by using startup conditions in execution plans • … so check constraints do give partitioned views similar “table elimination”

Use Cases For Partitioned Views

Limitations of Partitioned Tables • • • Prior to MSSQL 2016 SP 1, Enterprise feature Horizontal partitioning scheme only Statistics tracked at table (not partition) level Indexing defined at the table level Partitioning can only occur on one column Prior to MSSQL 2014, online rebuild only supported at table level

Vertical Partitioning • Horizontal partitioning separates groups of rows into partitions (table) or tables (view) • Vertical partitioning on the other hand separates columns from the rest of the table • Can be driven by normalization • In practice, this term is more often associated with row splitting

Horizontal Partition

Vertical Partition

Before Row Splitting ID_Int ID_GUID Name … Big_xml 1 1 a 23… Anna … <doc <a name=a />… /> 2 2 ff 0… Bubba … <doc <a name=b />… /> … … … 9001 10 ab… Goku … <doc <a name=b />… /> Rating …

After Row Splitting ID_Int ID_GUID Name … 1 1 a 23… Anna … 2 2 ff 0… Bubba … … … 9001 10 ab… Goku … ID_Int Big_xml Rating 1 <… /> … 2 <… /> … … 9001 <… /> …

Before Row Splitting ID_Int ID_GUID Name … SSN Salary 1 1 a 23… Anna … 111 -22 -3333 $150, 000 2 2 ff 0… Bubba … 111 -22 -4444 $80, 000 … … … 9001 10 ab… Goku … 111 -22 -9999 $125, 000

After Row Splitting ID_Int ID_GUID Name … 1 1 a 23… Anna … 2 2 ff 0… Bubba … … … 9001 10 ab… Goku … ID_Int SSN Salary 1 111 -22 -3333 $150, 000 2 111 -22 -4444 $80, 000 … … … 9001 111 -22 -9999 $125, 000

Statistics Sampling Frequency 2. 5 2 1. 5 1 0. 5 0 0 0. 5 1 1. 5 2 2. 5 3 3. 5

Connect the Dots Simply Frequency 2. 5 2 1. 5 1 0. 5 0 0 0. 5 1 1. 5 2 2. 5 3 3. 5

Possible Answer Frequency 2. 5 2 1. 5 1 0. 5 0 0 0. 5 1 1. 5 2 2. 5 3 3. 5

Remember This? 2005? 2006? 2007? 2008? Data Boundaries Coded Into View / Procs My. Tbl_05 My. Tbl_06 My. Tbl_07 My. Tbl_08 … … …

Multi-Dimensional Partitioned View 2005? 2006? 2007? 2008? Data Boundaries Coded Into View / Procs My. Tbl_05 My. Tbl_06 My. Tbl_07 … … … … … Premium? no yes My. Tbl_08 Fast_SSD … … …

SQL Saturday Madison After Party § Join us @6: 30 pm for some networking and fun. Appetizers provided. Madison’s 119 King Street Madison, WI 53703

Thank You Rick Lowe – rick@data-flowe. com Data. FLowe

Partition Function vs Scheme Partition Functions Partition Scheme

Defining a Partitioned Table (review) CREATE PARTITION FUNCTION My. Function(DATE) AS RANGE RIGHT FOR VALUES (…); CREATE PARTITON SCHEME My. Partition. Scheme AS PARTITION My. Function TO( … ); CREATE TABLE Sales. Order. Header( Order. Date DATETIME 2(3), Sales. Order. ID INT IDENTITY NOT NULL, … CONSTRAINT PK_Sales. Order. Header PRIMARY KEY CLUSTERED(Order. Date, Sales. Order. ID) ON My. Partition. Scheme( Order. Date ) );

Moving Data Around – Partitioned Table (review) • To extract an entire partition from a table – Use ALTER TABLE SWITCH to instantly switch the partition out to a new table – Archive / delete / manipulate the new table • To add existing data to partitioned table – Get data into table with same structure/indexing – Add check constraints – Switch the new table into the partitioned table