Home     |     .Net Programming    |     cSharp Home    |     Sql Server Home    |     Javascript / Client Side Development     |     Ajax Programming

Ruby on Rails Development     |     Perl Programming     |     C Programming Language     |     C++ Programming     |     IT Jobs

Python Programming Language     |     Laptop Suggestions?    |     TCL Scripting     |     Fortran Programming     |     Scheme Programming Language


 
 
Cervo Technologies
The Right Source to Outsource

MS Dynamics CRM 3.0

Sql Server Programming

Indexed Views


Hello,

We have implemented a couple of index views on one of our application
databases. Our applications analysts have found some problems when using
them. They have found that when they look for the some record the results
that they get are not in sync with the base table. For example, if they
retrieve the most recently updated record from the indexed view, there is
often a record that has been updated more recently in the base table.

How are indexed views updated? I have read that updates to the base tables
are automatically reflected in the indexed view. When the base table is
updated (ie. records added, updated, or deleted) is the indexed view also
updated as part of the same transaction or as a separate transaction?

An indexed view is updated as part of the same transaction automatically by
the database engine when the data in the underlying table is modified just
the way indexes on the table are being updated when the data in the indexed
columns is modified.

How have you verified that the data is wrong? Can you reproduce the problem?

ML

---
http://milambda.blogspot.com/

-----------------------------------------------Reply-----------------------------------------------

Hi ML,

Here is the code for the indexed view. Can you spot any mistakes?

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

CREATE VIEW [dbo].[DataV1] WITH SCHEMABINDING
AS
SELECT
Column1,
Column2,
Column3,
Column4,
Column5,
Column6,
Column7,
Column8,
Column9,
Column10,
Column11,
Column12,
Column13
FROM         dbo.DataT33
WHERE     (Column3 < 400)

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

set
ANSI_PADDING,ANSI_WARNINGS,CONCAT_NULL_YIELDS_NULL,ARITHABORT,QUOTED_IDENTI FIER,ANSI_NULLS
on
GO

set NUMERIC_ROUNDABORT off
GO

 CREATE  UNIQUE  CLUSTERED  INDEX [DataV9_CLUSTERED] ON
[dbo].[DataV1]([Column1], [Column3], [Column4], [Column5], [Column12],
[Column13], [Column10], [Column6], [Column7], [Column8], [Column9]) ON
[PRIMARY]
GO

set NUMERIC_ROUNDABORT off set arithabort  OFF
GO

SET QUOTED_IDENTIFIER OFF

SET ANSI_NULLS ON
GO

"ML" <M@discussions.microsoft.com> wrote in message

news:0934B6F4-25AA-428A-A3EF-1DA24D3CC354@microsoft.com...

On May 30, 8:52 am, "Loren Z" <anonym@discussions.microsoft.com>
wrote:

Another alternative would be to use an index on
([Column3], [Column1], [Column4], [Column5], [Column12],
[Column13], [Column10], [Column6], [Column7], [Column8], [Column9])

Because Column3 is first, your queries will run just as fast.

-----------------------------------------------Reply-----------------------------------------------

The view references a subset of data in the base table. Could this be the
reason why rows seem to be missing from the view?

ML

---
http://milambda.blogspot.com/

Add to del.icio.us | Digg this | Stumble it | Powered by Megasolutions Inc