Results 1 to 8 of 8

Thread: Indexed Views

  1. #1
    Join Date
    May 2003
    Posts
    8

    Question Unanswered: Indexed Views

    I am looking for a little insight. I am using an SQL Server database created by a third party vendor. There are certain columns in a given table that I query for quite often. To speed things up, I created an indexed view.

    Now I can no longer insert into the base table. Attempting an insert causes a SQL error stating that the system properties ARITHABORT and NUMERIC_ROUNDABORT are incorrect. If I remove the index from my view, the inserts work just fine.

    Can somebody provide some insight as to why this happens and how I might be able to correct it (keep in mind that the DB was setup by a third party, so I cannot change too much of the underlying setup without possibly compromising their functionality).

  2. #2
    Join Date
    Mar 2003
    Location
    Indiana, USA
    Posts
    100
    SEE BOL
    indexed views --> SET Options That Affect Results

    These six SET options must be set to ON:
    ANSI_NULLS
    ANSI_PADDING
    ANSI_WARNINGS
    ARITHABORT
    CONCAT_NULL_YIELDS_NULL
    QUOTED_IDENTIFIER
    The NUMERIC_ROUNDABORT option must be set to OFF.

    all INSERT, UPDATE, and DELETE operations must have the same setting of CONCAT_NULL_YIELDS_NULL ON as the connection that created the index.
    ( I think this also applies for the other settings)

    The above applies to indexed views or computed columns; do you have a computed column in the table or the view? If so, I would try removing it from the view and see if the error goes away.

    Tim S
    Last edited by TimS; 03-09-05 at 17:19.

  3. #3
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    always be careful screwing with the backend of someone elses closed software. I would copy the database to somewhere you can query against it without fear of messing things up.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  4. #4
    Join Date
    May 2003
    Posts
    8
    I have no computed columns. If I am understanding this right, any session issuing INSERT, UPDATE, or DELETE queries must have same session properties as the session that created the index. Does this mean that if the base table (and it's indexes) were created by one session, but the indexed view was created with a different session (with different session properties), there will always be an error when trying to query the base table?

    For example, in my case it seems that the base table was created with the session property "ARITHABORT" turned off. Indexed views, however, require ARITHABORT to be on. Does this mean that I cannot create an indexed view on that base table since the view's index will always require ARITHABORT to be on and the base table index will always require it to be off?

    Quote Originally Posted by TimS
    SEE BOL
    all INSERT, UPDATE, and DELETE operations must have the same setting of CONCAT_NULL_YIELDS_NULL ON as the connection that created the index.
    ( I think this also applies for the other settings)

    The above applies to indexed views or computed columns; do you have a computed column in the table or the view? If so, I would try removing it from the view and see if the error goes away.

    Tim S

  5. #5
    Join Date
    May 2003
    Posts
    8
    This isn't a case of backend software per se. It is a mutually shared database, but it was configured by them. They put their data in, we put ours in, and then we generate reports.

    Either way, yes, we always backup stuff before messing with it.

    Quote Originally Posted by Thrasymachus
    always be careful screwing with the backend of someone elses closed software. I would copy the database to somewhere you can query against it without fear of messing things up.
    Last edited by Daventrian; 03-09-05 at 17:50.

  6. #6
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Yes, but the point being, you'd be way better served to copy a nightly dump and create another database for reporting....

    preferably on another box, or at least a separate instance...
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  7. #7
    Join Date
    May 2003
    Posts
    8
    That would be a great solution if the data was not needed in real time.

    Quote Originally Posted by Brett Kaiser
    Yes, but the point being, you'd be way better served to copy a nightly dump and create another database for reporting....

    preferably on another box, or at least a separate instance...

  8. #8
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    replication?
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

Posting Permissions

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