Sql Server Programming :: Indexed Views


	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... - Hide quoted text - > 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/ On May 30, 8:52 am, "Loren Z" <anonym@discussions.microsoft.com> wrote: - Hide quoted text - > 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... > > 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/ 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/