Indexing in views

  • Sean already said what I wanted to say.

    Thanks, Kapil!

  • Great question, thanks.

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • I supposed (incorrectly) that the ALTER VIEW would fail due to the addition of the non-determinitic column, CurrentDate, to the indexed view.

    Learned lots this morning! Nice question!

  • This was removed by the editor as SPAM

  • sneumersky (7/24/2013)


    That said, there are probably other potential uses for indexed views (BI or non-BI related) than I can think of at this moment in time.

    I use indexed view to filter rows and have small index, same as CREATE INDEX with WHERE clause.

    But, indexed views can reference other tables in JOIN.

    e.g.:

    create table a(k int, to_process bit default 1)

    go

    create view v_a

    with schemabinding

    as

    select a.k

    from dbo.a

    where to_process = 1

    go

    create unique clustered index idx_v_a on v_a(k)

    go

    -- after process a record unset flag

    declare @k int

    begin tran

    update top(1) a set to_process = 0,@k = k

    -- processing

    print 'processing: ' + cast(k as varchar(10))

    if @@error = 0

    commit

    else

    rollback

  • Andrew Diniz (7/25/2013)


    I supposed (incorrectly) that the ALTER VIEW would fail due to the addition of the non-determinitic column, CurrentDate, to the indexed view.

    Learned lots this morning! Nice question!

    Even if you attempted to create a unique clustered index on the view as defined in the ALTER VIEW statement, it would indeed fail because GETDATE() is non-deterministic and indexes cannot be created on views with non-deterministic definitions.

    I picked the right answer because I knew that ANY attempt to create an index on view that defines a column as the result of a non-deterministic function would fail. There was only one choice that included "Error" as the result of Scenario 3.

    Jason Wolfkill

  • wolfkillj (7/25/2013)


    Andrew Diniz (7/25/2013)


    I supposed (incorrectly) that the ALTER VIEW would fail due to the addition of the non-determinitic column, CurrentDate, to the indexed view.

    Learned lots this morning! Nice question!

    Even if you attempted to create a unique clustered index on the view as defined in the ALTER VIEW statement, it would indeed fail because GETDATE() is non-deterministic and indexes cannot be created on views with non-deterministic definitions.

    I picked the right answer because I knew that ANY attempt to create an index on view that defines a column as the result of a non-deterministic function would fail. There was only one choice that included "Error" as the result of Scenario 3.

    *cough*

    Actually, the view is altered twice. And the second ALTER effectively undoes the first, removing the GETDATE() column again. So that would not be a reason for the CREATE INDEX to fail.


    Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
    Visit my SQL Server blog: https://sqlserverfast.com/blog/
    SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

  • Good question but your script needs the ";" replaced with GO in spots. 😎

  • Hugo Kornelis (7/25/2013)


    wolfkillj (7/25/2013)


    Andrew Diniz (7/25/2013)


    I supposed (incorrectly) that the ALTER VIEW would fail due to the addition of the non-determinitic column, CurrentDate, to the indexed view.

    Learned lots this morning! Nice question!

    Even if you attempted to create a unique clustered index on the view as defined in the ALTER VIEW statement, it would indeed fail because GETDATE() is non-deterministic and indexes cannot be created on views with non-deterministic definitions.

    I picked the right answer because I knew that ANY attempt to create an index on view that defines a column as the result of a non-deterministic function would fail. There was only one choice that included "Error" as the result of Scenario 3.

    *cough*

    Actually, the view is altered twice. And the second ALTER effectively undoes the first, removing the GETDATE() column again. So that would not be a reason for the CREATE INDEX to fail.

    There are indeed two ALTER statements and I did indeed overlook the second. I tend to approach QotDs with lots of code by first skimming through the code looking for any red flags that might indicate what the question is really about. Here, I saw the first ALTER VIEW with a nondeterministic function and in my peripheral vision saw the CREATE INDEX statement in Scenario 3. My brain immediately said "Aha!" because I know off the top of my head that you can't create an index on a view like that (for some reason, the flag didn't go up immediately at Scenario 1's attempt to create a nonclustered index without a unique clustered index). When I saw that only one answer had "Error" as the outcome of Scenario 3, I decided that I had found the crux of the problem and didn't need to read any further. So, I arrived at the correct answer by flawed reasoning. Just lucky today, I guess! 😀

    Jason Wolfkill

  • Good Question kapil

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • Good question on indexes on views. 🙂

Viewing 11 posts - 16 through 25 (of 25 total)

You must be logged in to reply to this topic. Login to reply