Adding check constraint to the partitioned table in SQL 2005 always scans ALL partitions
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...