When an index is created with STATISTICS_NORECOMPUTE = OFF, how frequently is the index updated? Is it updated every time the table has new rows created on it?
Within the same operation as the base table. SQL's indexes are never out of sync with the table.
STATISTICS_NORECOMPUTE has nothing to do with the index itself. If it's set on it just means that that index's statistics object is not automatically updated.
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