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 <= 1000000
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 all columns to be the number of actual pages
in each partition according to the dm_db_partition_stats view. i think it
should have only checked first partition to be sure that there is no data
less than 2.

select index_id,partition_number,range_scan_count,singleton_lookup_count,
row_lock_count,page_lock_count,index_lock_promotion_count
from sys.dm_db_index_operational_stats (db_id(),object_id('t'),null,null)

alter table t add constraint checkCons check(i>2)

select index_id,partition_number,range_scan_count,singleton_lookup_count,
row_lock_count,page_lock_count,index_lock_promotion_count
from sys.dm_db_index_operational_stats (db_id(),object_id('t'),null,null)

In the example below, optimizer should have only checked first partition to be sure that there is no data less than 2.

In order to explain how bad such behaivior is, adding such constraint on the
table with 900 partitions each one ~ million rows takes about 15 min. What will happen with table with 20 million rows in each partition?

Comments

Popular posts from this blog

SQL Awesomeness: Finding a QUALIFY query clause in the depths of the database ocean

The Greatest Reasons to use or not to use a Centralized Data Access Architecture

Look back and realize how far you came