Home Dashboard Directory Help
Search

Increase efficiency when adding a CHECK constraint by Hugo Kornelis


Status: 

Closed
 as Won't Fix Help for as Won't Fix


7
0
Sign in
to vote
Type: Suggestion
ID: 789885
Opened: 6/11/2013 6:50:24 AM
Access Restriction: Public
0
Workaround(s)
view

Description

When adding a CHECK constraint (without using the NOCHECK option) the engine has to check if all existing data satisfies the constraint. This often can only be done by checking all rows. But sometimes, a more efficient approach is possible and checking all rows is a waste of resources.

For instance in this case:
USE Sandbox;
go
CREATE TABLE dbo.Demo
(DateCol datetime NOT NULL,
    IntCol int        NOT NULL,
    Filler char(8000) NOT NULL);
CREATE CLUSTERED    INDEX cix_DemoDate ON dbo.Demo(DateCol);
CREATE NONCLUSTERED INDEX nix_DemoInt ON dbo.Demo(IntCol);
go
INSERT INTO dbo.Demo (DateCol, IntCol, Filler)
VALUES (CURRENT_TIMESTAMP, 1, 'x');
go 5000
SET STATISTICS IO ON;
go
ALTER TABLE dbo.Demo
ADD CONSTRAINT ck_Demo_Date CHECK (DateCol > '20000101');
go
SET STATISTICS IO OFF;
go
DROP TABLE dbo.Demo;
go

The output shows 17 logical reads, which can only mean that the nonclustered index has been completely scanned.
Details
Sign in to post a comment.
Posted by Microsoft on 6/27/2013 at 3:46 PM
Thanks for the feedback - regret that we can't address this request in the immediate future.

Regards,
Richard
Posted by Hugo Kornelis on 6/12/2013 at 5:33 AM
Another use case (to add to the one Dmitri describes) is if you want to convert an existing table to a partitioned table to facilitate future maintenance. Creating a partitioned table with two partitions and one boundary, such that all rows go in the first partition, is easy. Switching the existing table into that first partition is easy as well, and can be done as a meta data operation - but it does required a CHECK constraint to be added to the table first.
See https://www.simple-talk.com/sql/t-sql-programming/painless-management-of-a-logging-table-in-sql-server/
Posted by Dmitri Korotkevitch on 6/12/2013 at 5:20 AM
I think that the biggest disadvantage of the current behavior is not really the performance but rather the duration of the schema modification lock. I see more and more cases when customers want to use some kind of the tiered storage in their systems and place most recent data to SSDs keeping old data on conventional hard drives. Generally speaking, we can utilize partitioned views for that. For example, I can create the main partitioned table on the SlowStorage FG and separate table on FastStorage FG with partitioned view created. Assuming that operational period is June, 2013:

create partition function pfData(date)
as range right for values
('20130101','20130201','20130301','20130401'
,'20130501','20130601','20130701','20130801'
,'20130901','20131001','20131101','20131201');

create partition scheme psData
as partition pfData
all to ([SlowStorage]);

create table dbo.Data2013
(
     DateCol date not null,
    
     constraint CHK_Data2013_6
     check (DateCol >= '20130101' and DateCol < '20130601')
);

create clustered index ciData2013 on dbo.Data2013(DateCol);

create table dbo.Data2013_06
(
     DateCol date not null,
    
     constraint CHK_Data2013_Data
     check (DateCol >= '20130601' and DateCol < '20130701')
) on [FastStorage];

create clustered index ciData2013_06 on dbo.Data2013_06(DateCol)
on [FastStorage]
go

create view dbo.vData
with schemabinding
as
     select DateCol from dbo.Data2013_06
     union all
     select DateCol from dbo.Data2013

I cannot simply put the partition to the FastStorage because I cannot move the single partition to another FG without SCH-M lock in place for the duration of the operation. I can, of course, accomplish it by rebuilding the indexes to another partition scheme but it’s a lot of the overhead.

When I need to change the operational period and move Data2013_06 to the SlowStorage, I’d expect to do something like that:

create index ciData2013_06 on dbo.Data2013_06(DateCol)
with (online=on, drop_existing=on)
on [SlowStorage];

alter table dbo.Data2013 add constraint CHK_Data2013_7
check (DateCol >= '20130101' and DateCol < '20130701');

alter table dbo.Data2013 drop constraint CHK_Data2013_6;

alter table dbo.Data2013_6 switch to dbo.Data2013 partition 6;

I would expect adding constraint CHK_Data2013_7 to be “metadata only” operation – existing trusted constraint CHK_Data2013_6 guarantees that there are no rows in the table that violate the new constraint. But it’s not the case and we would have SCH-M lock held for the duration of the index scan.
Sign in to post a workaround.