Results 1 to 6 of 6

Thread: Index On a View

  1. #1
    Join Date
    Sep 2003
    Posts
    364

    Unanswered: Index On a View

    Does anyone know of a trick that allows you to apply an index on the following view? I would like to index the license_id column. When I try to apply a unique clustered index I get the following error - Index on view 'dbo.v_lic_status' cannot be created because function 'getdate' yields nondeterministic results.

    VIEW DDL:

    SELECT license_id,
    CASE
    WHEN (term_date < GETDATE()) THEN 'Terminated'
    WHEN (suspend_date < GETDATE()) THEN 'Suspended'
    WHEN (expiration_date < GETDATE()) THEN 'Expired'
    WHEN (effective_date < GETDATE()) THEN 'Active'
    ELSE 'Pending'
    END AS lic_status
    FROM dbo.license

    INDEX DDL:

    create unique clustered index ux_v_lic_status_01 on dbo.v_lic_status (
    license_id
    )
    with
    fillfactor= 90
    go

  2. #2
    Join Date
    Sep 2003
    Location
    Dallas, Texas
    Posts
    311

    Re: Index On a View

    You have to create the view with schemabinding and both ANSI_NULLS and QUOTED_IDENTIFIER option must be on. For detailed information check BOL under the subject 'Creating an Indexed View'.

  3. #3
    Join Date
    Sep 2003
    Posts
    364
    Hmm...I tried that and still doesn't work. Here's the create statement for the view.

    /*================================================= =============*/
    /* View: v_lic_status */
    /*================================================= =============*/
    create view dbo.v_lic_status with schemabinding as
    SELECT license_id,
    CASE
    WHEN (term_date < GETDATE()) THEN 'Terminated'
    WHEN (suspend_date < GETDATE()) THEN 'Suspended'
    WHEN (expiration_date < GETDATE()) THEN 'Expired'
    WHEN (effective_date < GETDATE()) THEN 'Active'
    ELSE 'Pending'
    END AS lic_status
    FROM dbo.license
    go

  4. #4
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    Books OnLine (article name:Creating an Indexed View) has this to say under Requirements for an Indexed View:

    All functions referenced by expressions in the view must be deterministic. The IsDeterministic property of the OBJECTPROPERTY function reports if a user-defined function is deterministic. For more information, see Deterministic and Nondeterministic Functions.


    As for how to work around this, I am not too sure. Looks like you would have to put a flag on a base table for when something goes expired, terminated, etc..

  5. #5
    Join Date
    Sep 2003
    Posts
    364
    Thanks for the reply, that's what I was afraid of. Didn't know if someone knew a trick or shortcut around this. It sux since I have an 'else' as a catch all in the case statement sql server doesn't consider this deterministic.

  6. #6
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    An indexed view actually creates a persistent copy of the data in a virtual table. The data in the virtual table is automatically updated as data in the underlying tables is modified. So you see, creating an indexed view using a statement that references GETDATE would need to be updated continuously, hence it is not allowed.

    I hope this help you understand some of the restrictions places upon indexed views.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

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