Implementing Horizontal Fragmentation in SQL Server

Lecture 19 provides hands-on engineering walkthroughs implementing horizontal fragmentation within Microsoft SQL Server. Students examine native database mechanisms for partitioning data across filegroups and distinct server instances. The lecture covers CREATE PARTITION FUNCTION—specifying boundary values and range partition boundaries (LEFT or RIGHT)—and CREATE PARTITION SCHEME, mapping logical partitions to distinct physical filegroups. The curriculum investigates partitioned views using CHECK constraints, illustrating how the SQL Server query optimizer uses partition elimination to prune non-relevant fragments during query execution. Distributed partitioned views linked across linked servers via OLE DB providers are demonstrated, highlighting practical real-world deployment workflows. Cost-based query optimization models analyze CPU overhead, disk page access costs, and communication network transmission delays. Distributed join optimization strategies contrast broadcast joins, partitioned hash joins, and semi-join reducers like the SDD-1 algorithm. Distributed reliability architectures examine failure classifications, including transaction aborts, site crashes, and network partitioning anomalies. Atomic commitment protocols, notably Two-Phase Commit (2PC) and Three-Phase Commit (3PC), are demonstrated with formal state transition diagrams. Parallel database system architectures, spanning Shared-Memory, Shared-Disk, and Shared-Nothing topologies, are contrasted for speedup and scaleup performance. Parallel data placement techniques, including round-robin, hash partitioning, and range partitioning, illustrate workload balancing across processing nodes. Parallel join algorithms evaluate parallel nested-loop, associative, and parallel hash join executions in high-throughput query environments. Distributed Object-Oriented Database Management Systems (OODBMS) explore object identity, encapsulation, complex object hierarchies, and pointer swizzling. Client-server and page-server architectures in distributed OODBMS are analyzed for cache coherence, client caching, and transaction synchronization. Multidatabase and federated database systems illustrate how schema integration and mediation resolve semantic heterogeneity across legacy data stores. Exam preparation summaries and solved handout excerpts reinforce retention of critical mathematical proofs, fragmentation formulas, and execution algorithms. Comprehensive review of Virtual University exam patterns, past solved papers, and practice MCQs consolidates theoretical mastery and practical design competence. Systematic preparation on VU SIGMA empowers students to achieve outstanding grades in university examinations through structured practice and verified feedback.