The most reliable sequence I can find to cause an automatic statistics update is:
Update statistics sampling zero rows
This results in an empty statistic object.
-- Example
UPDATE STATISTICS
Person.[Address]
IX_Address_StateProvinceID
WITH SAMPLE 0 ROWS;
Update the target column(s) in a single row of the table
This increments the column modification counter. The combination of an empty statistics object and an incremented modification counter enables a special case statistics update (it simulates creating statistics on an empty table, then adding a row).
-- Example
BEGIN TRANSACTION;
UPDATE TOP (1)
Person.[Address]
SET StateProvinceID = StateProvinceID;
ROLLBACK TRANSACTION;
Run the query with OPTION (RECOMPILE)
This causes an automatic update of detected stale statistics, even if a matching plan for the query is already present in cache. The resulting statistics update will subsequently cause an optimality-based recompilation for the original cached plan, if it is matched again.
-- Example
SELECT
A.City,
A.AddressLine1,
A.AddressLine2
FROM Person.[Address] AS A
WHERE
1 = 1
AND A.StateProvinceID = 54
OPTION (RECOMPILE);
###Demo
Using a similar AdventureWorks query as used in jyao's answer, the following script puts everything above together:
DBCC FREEPROCCACHE;
GO
-- Cache a plan for the query
GO
SELECT
A.City,
A.AddressLine1,
A.AddressLine2
FROM Person.[Address] AS A
WHERE
1 = 1
AND A.StateProvinceID = 54;
GO
DBCC SHOW_STATISTICS
(
'Person.Address',
'IX_Address_StateProvinceID'
) WITH STAT_HEADER;
GO
-- Empty stats object
UPDATE STATISTICS
Person.[Address]
IX_Address_StateProvinceID
WITH SAMPLE 0 ROWS;
GO
-- Perform and rollback a single row update
BEGIN TRANSACTION;
UPDATE TOP (1)
Person.[Address]
SET StateProvinceID = StateProvinceID;
ROLLBACK TRANSACTION;
GO
DBCC SHOW_STATISTICS
(
'Person.Address',
'IX_Address_StateProvinceID'
) WITH STAT_HEADER;
GO
-- Run the query again to trigger stats update
GO
SELECT
A.City,
A.AddressLine1,
A.AddressLine2
FROM Person.[Address] AS A
WHERE
1 = 1
AND A.StateProvinceID = 54
OPTION (RECOMPILE);
GO
DBCC SHOW_STATISTICS
(
'Person.Address',
'IX_Address_StateProvinceID'
) WITH STAT_HEADER;
GO
###Output
The DBCC SHOW_STATISTICS
results show the original statistics header, the empty header, and the desired updated header at the end of the process: