In the context of partitioning, it’s essential to understand the concepts of aligned and non-aligned indexes. Let us learn about it today.
CTE or Temp Table: Choosing Where an Intermediate Result Lives
A CTE is a name, not a saved result. Build both versions on 20,000 rows, compare the plans, and see why the answer is never always one or the other.
Unit of Measure Conversions: One Table for Kilograms, Pounds and Boxes
Adding kilograms, pounds and boxes gives a number that means nothing. Convert each product to one base unit and find the rows that cannot convert.
SQL SERVER – Table Partitioning for Slow Performance
Something just conversation which I keep on doing again for ages. One of the conversations which never gets old is Table Partitioning for Slow Performance.
TRUNCATE TABLE WITH PARTITIONS: Empty One Partition Fast
TRUNCATE TABLE WITH PARTITIONS empties one partition and logs far less than DELETE. See the syntax, the index rule and the limits in SQL Server.
Partition Switch in SQL Server: Move a Million Rows in a Moment
A partition switch moves a whole partition from one table to another without copying a single row. This post builds a partitioned table with a million rows, switches the January partition out and back in, and shows that only metadata changes. It lists the five errors that stop a switch, with the real message numbers, and ends with the sliding window pattern.
Partition Function Fanout: Count Partitions in SQL Server
Count partitions in SQL Server by reading the fanout of a partition function. This post reads fanout and modify_date from sys.partition_functions, watches SPLIT RANGE and MERGE RANGE change them, and finds functions that no table uses.