Go for box two mate. I really would use an index view for this.
Another alternative is using a trigger on the table. A trigger will give
you more control over what you can a can't do and in my opinion a trigger is
elegant and meaningful than both.
I hope this helps
Greg O MCSD
SQL Scribe Doentation Builder
Doent any SQL server database in minutes
Programmers love it, DBA dream of it
AGS SQL Scribe download a 30 day trial today
"Zig Mandel" <nonehotmail.com> wrote in message
news:eGT5onCQDHA.3016TK2MSFTNGP10.phx.gbl...the> which is better to use?
> simplified example:
> I have a table T with fields:
> id (int key)
> fSelected (bit) not null
> I want to validate that fSelected=1 for at most one row.
> There are 2 ways to do this that I can think of. I want to know which isfSelected=1> best one.
> Method 1:
> create a view:
> select * from T where fSelected=1
> and create an index on the view with field fSelected, UNIQUE
> Method 2:
> create a check constraint on table T
> CHECK(fSelected = 0 OR TotalSelected() = 0 OR IdSelected() = [id])
> for brevity, I will only describe what TotalSelected and IdSelected does.
> They are user-defined functions.
> TotalSelected: returns the count of fSelected=1 on table T
> IdSelected: returns the id on the first item in T where fSelected=1
> While Method 1 seems more elegant, I can see a problem with it:
> Since the view does not include the row's id, it cannot be used to quickly
> search for the fSelected=1 row (which is a requirement for this example).
> And besides, that only works in Enterprise edition (that is, using indexed
> views to opimize queries). So I need to create another index (lets call it
> Index_fSelected) and end up with 2 indexes on T.
> Method 2 is more complicated, but it has the following advantages:
> 1) I only need to create one index (Index_fSelected) to speed upTotalSelected> queries, and that same index will be used by SQL when callingits> and IdSelected.
> In short, Ive seen posts saying its better to use indexed views because> in better compliance with SQL, but it seems to me that I can accomplish it
> better using Method 2 (check constraint), which avoids the creation of the
> extra index by reusing an index that my application needs to have anyway
> (the Index_fSelected mentioned above.)
> Thanks for any suggestions.