Maria's journey in the data fields

In the Books Online, in the Table and Index Organization chapter there is such schema: I think, this is not correct , because, there there is only one set of indexes and only one heap per table, and each b-tree or heap separated into partitions. Even in table sys.partitions, partitions are stored as object_id ->index_id->partition_id It should look like this

Yes. Even if constraint checks partitioning column and "bad" data can exist in one partition only. See for yourself: I create table natively partitioned on integer field with another char field to fill more pages (full script at the end of message), I fill the table with 1000000 rows. I also have a nonclustered index in partitioned column. For a design of our application i need to add check constraint (i >2) to the table. create partition function f (int) as range for values (5,50,100,500); create partition scheme s as partition f all to ([primary]) create table t ( i int not null,c char(2000)) on s(i) declare @i int set @i = 1 while @i begin insert into t select @i,replicate('a',2000) set @i = @i + 1 end create index ind1 on t(i) Checking the view dm_db_index_operational_stats I see that sql server scanned index on ALL partitions to check if data ok for the constraint : field range_scan_count changed from 0 to 1 in all partitions, page_lock_count also changed for...