Professional Web Applications Themes

Indexed view vs. Check constraint? - Microsoft SQL / MS SQL Server

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 is the 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 ...

  1. #1

    Default Indexed view vs. Check constraint?

    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 is the
    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 up fSelected=1
    queries, and that same index will be used by SQL when calling TotalSelected
    and IdSelected.

    In short, Ive seen posts saying its better to use indexed views because its
    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.


    Zig Mandel Guest

  2. #2

    Default Re: Indexed view vs. Check constraint?

    Zig,
    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
    regards
    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
    [url]http://www.ag-software.com/ags_scribe_index.asp[/url]

    "Zig Mandel" <nonehotmail.com> wrote in message
    news:eGT5onCQDHA.3016TK2MSFTNGP10.phx.gbl...
    > 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 is
    the
    > 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 up
    fSelected=1
    > queries, and that same index will be used by SQL when calling
    TotalSelected
    > and IdSelected.
    >
    > In short, Ive seen posts saying its better to use indexed views because
    its
    > 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.
    >
    >

    Greg Obleshchuk Guest

  3. #3

    Default Re: Indexed view vs. Check constraint?

    If you has posted DDL instead of a personal narrative, would it look
    more like this?

    CREATE TABLE Foobar
    (foo_id INTEGER NOT NULL PRIMARY KEY,
    f_selected INTEGER NOT NULL);

    Do not use the non-relational, proprietary BIT datatypes It looks like
    you are trying to store flags in a table, instead of attribute values.
    And it does not port. This is suposed to be SQL (set oriented, high
    level, portable) and not assembly language (record oriented, low level,
    proprietary).
    >> I want to validate that fSelected=1 for at most one row.
    <<

    SELECT CASE SUM(f_selected)
    WHEN 0 THEN 'okay'
    WHEN 1 THEN 'okay'
    ELSE 'not okay'
    FROM Foobar;
    >> Ive seen posts saying its better to use indexed views because its in
    better compliance with SQL, <<

    Unh? There are no indexes in Standard SQL; those are implementation
    particulars. The user-defined functions will be expensive and
    proprietary.

    Ideally, this should be in a CHECK() constraint, but SQL Server does not
    yet support table references in CHECK() constraints.

    --CELKO--
    ===========================
    Please post DDL, so that people do not have to guess what the keys,
    constraints, Declarative Referential Integrity, datatypes, etc. in your
    schema are.

    *** Sent via Developersdex [url]http://www.developersdex.com[/url] ***
    Don't just participate in USENET...get rewarded for it!
    Joe Celko Guest

Similar Threads

  1. html and indexed pages
    By harrial in forum Macromedia Dynamic HTML
    Replies: 0
    Last Post: September 7th, 08:19 PM
  2. Indexed color space
    By Andrej_Dino@adobeforums.com in forum Adobe Acrobat SDK
    Replies: 2
    Last Post: August 22nd, 02:04 PM
  3. Converting to Indexed Color
    By Stacy_Koenig@adobeforums.com in forum Adobe Photoshop Mac CS, CS2 & CS3
    Replies: 2
    Last Post: April 7th, 12:26 AM
  4. goto on indexed clips
    By PierreAlain in forum Macromedia Flash Actionscript
    Replies: 4
    Last Post: February 27th, 05:47 AM
  5. Error Evaluating Check Constraint
    By Joyce in forum Microsoft Access
    Replies: 0
    Last Post: July 14th, 11:53 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139