Data Partitioning SQL Server Satya Agenda Data Partitioning
- Slides: 13
Data Partitioning – SQL Server Satya
Agenda • Data Partitioning in SQL Server • Architecture • Demo
Data Partitioning • Also known as Horizontal Partitioning ▫ Large table/index can be split into multiple manageable portions • Advantages ▫ ▫ ▫ Improves Query Performance Better Data Manageability and Cost Effective Deleting and Moving data is faster from partitions Narrow downs the index maintenance window OLTP/DSS – Operational/Non-Operational data
Data Partitioning Architecture Table/Indexed View Logical Representation P 1 Pn Index /Heap Index/ Heap Data pages
Partition Concepts • PARTITION FUNCTION ▫ Used to specify partition boundary values ▫ Two types (represents boundary data directions) �LEFT �RIGHT -myself and my left range values -myself and my right range values • PARTITION SCHEME ▫ Logical & Partitions physically aligned ▫ Span over single or multiple file groups ▫ Can specify each partition can go to a individual file group or all partitions can go into a single file group
Creating Partition Function CREATE PARTITION FUNCTION My. Partition. Funcation. Name_pfn(int) AS RANGE LEFT FOR VALUES(10, 50) <=10 >10 & <=50 >50 CREATE PARTITION FUNCTION My. Partition. Funcation. Name_pfn(int) AS RANGE RIGHT FOR VALUES(10, 50) <10 >=10 & <50 >=50
Partition Physical Architecture CREATE PARTITION SCHEME My. Partition. Scheme_ps AS PARTITION My. Partition. Funcation. Name_pfn ALL TO ([FG]) --- specifying single file group --TO ([FGA], [FGB]) --- specifying multiple file groups Database Partition scheme File Group A Database Partition scheme File Group B File Group C File Group D
Partition Concepts (2) • SPLIT/MERGE ▫ SPLIT �Introduces new boundary �Partition will be added to respective side (L/R) ▫ MERGE �Deletes boundary �Partition will be merged to the respective side (L/R) • SWITCH IN/OUT ▫ Moving partition from partitioned table to other partitioned table called “in” ▫ Moving partition from partitioned table to non partitioned table called “out”
SPLIT – LEFT Boundary <=10 >10 & <=50 >50 ALTER PARTITION SCHEME My. Partition. Scheme_ps NEXT USED [FG]; -- specifying file group for new boundary ALTER PARTITION FUNCTION My. Partition. Function. Name_pfn() SPLIT RANGE(25) <=10 >10 & <=25 >25 & <=50 >50
MERGE – LEFT Boundary <=10 >10 & <=25 >25 & <=50 >50 ALTER PARTITION FUNCTION My. Partition. Function. Name_pfn() MERGE RANGE(10) <=10 <=25
SWITCH OUT Partitioned Table Partition 1 Partition 2 Non Partitioned table Partition 1 ALTER TABLE Partitioned. Table. Name SWITCH PARTITION 2 TO Non. Partitioned. Table. Name -- <Partition number> TO Destination. Partitioned. Table. Name<destination Partition number> SWITCH IN/OUT says “Hey, its your partition by updating metadata. ” Partitioned Table Partition 1 Non Partitioned table
Demo Presentation and Scripts will be mailed you at the end Or Alternatively available on http: //satyasql. wordpress. com To become master…
Additional Information • Would not support for composite key • SQL Server 2008 SP 2 or Later versions boosted creation of partitions up to 15000
- Sql server vertical partitioning
- Fixed partitioning and dynamic partitioning
- Azure sql server advanced data security
- Microsoft sql server 2012 parallel data warehouse
- Sql master data management
- Sql server master data management
- Polybase query service for external data
- Sql server unstructured data
- Sql server change data capture vs temporal tables
- Sys.sp_cdc_change_job
- Mpdwsvc.exe
- Urutan satya lencana tni al
- 5 prinsip agama hindu
- Amritdhara pharmacy v satya deo