Materialized Views, how often they updated in MS SQL?

• mario17

  SSCertifiable

  Points: 6079

  More actions

  August 28, 2014 at 9:59 am

  #290173

  Hi,

  Unlike in Oracle we don't option to set update frequency for MViews in SQL SErver, so just curious how it works in there.

  I have MView (aka Indexed view with schemabinding) with 1M rows, and this morning at 9am got .5 million load of new rows in underlying tables.

  Will I have 1.5 million if I do select at 9.05 am? Who controls that refresh, or upon first Select it still do sync?

  Thanks

  Mario

• Gail Shaw

  SSC Guru

  Points: 1004504

  More actions

  August 28, 2014 at 11:42 am

  #1741705

  It's updated as part of the transaction which modified the base tables.

  You'll have 1.5 million if you query one microsecond after the commit for the insert into the base table.

  Gail Shaw
  Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
  SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

  We walk in the dark places no others will enter
  We stand on the bridge and no one may pass