Wednesday, January 19, 2011

Deletion strategy for Sql Server 2008

create table product
(
product_id int not null identity(1,1),
the_product varchar(50) not null,
is_deleted int not null default 0
);


create unique index ux_product on product(the_product)
where is_deleted = 0;

insert into product(the_product) values('keyboard');
insert into product(the_product) values('mouse');
update product set is_deleted = 1 where the_product = 'mouse';

-- insert into product(the_product) values('keyboard'); -- won't be allowed

/* For all intents and purposes, the old mouse is 
deleted from the system, should still allow inserting new mouse. 
Prior to filtered index capability(available only on Sql Server 2008), 
having a unique column on table with marked deleted deleting strategy 
is an impossible implementation */
insert into product(the_product) values('mouse'); -- will be allowed

No comments:

Post a Comment