Nested Loop on Partitioned Table/Index
Nested Loop on Partitioned Table/Index
By : Kasim Wirama, MCDBA
This article I would like to show you the behavior of nested loop in partitioned table/index in SQL Server 2005. Partitioning has been introduced since SQL Server 2005. This feature will be available on next release of SQL Server.
Let’s say you have a table whose values of one of the column is range between 1 and 1000 and you want to put these values into 4 partitions, first partition from 1 to 199, second one from 200 to 399, third one from 400 to 599, and the rest value (after 599) is assigned to fourth partition. Here is the script to create partitioned table:
CREATE partition FUNCTION pf_1(INT) AS range RIGHT FOR VALUES (200,400,600);
CREATE partition scheme ps_1 AS partition pf_1 ALL TO ([PRIMARY]);
CREATE TABLE test_partition(id INT PRIMARY KEY, DATA VARCHAR(10))
ON ps_1(id);
Now issue select statement below and get its query plan :
SELECT * FROM test_partition
SELECT * FROM test_partition
|--Nested Loops(Inner Join, OUTER REFERENCES:([PtnIds1003]) PARTITION ID:([PtnIds1003]))
|--Constant Scan(VALUES:(((1)),((2)),((3)),((4))))
|--Clustered Index Scan(OBJECT:([tempdb].[dbo].[test_partition].[PK__test_partition__0BC6C43E]))
Notice that there is phrase : PARTITION ID instead of correlated parameter. PtnIds1003 shows partition id column (in this example, it is id column). SQL Server will do constant scan iterative from first until last partition, and does scanning for each iterations of partition member.