[ Impact of Re-compute Statistics flag in SQL Server 2014 when creating an index ]

When creating a new non-clustered index on a column in a table in the SQL Server, what does the Re-compute statistics flag do? is it a onetime operation that happens during the creation of the index? or does it impact data modifications? (Re-compute stats every time a data operation occurs?)

Answer 1

Flag STATISTICS_NORECOMPUTE just disable any future automatic statistic updates. In case you have special automated procedure to update statistics that option is needed.

However, most of the time statistics auto-update works fine and better to skip STATISTICS_NORECOMPUTE option to prevent query optimizer from picking wrong query plan based on old statistics.