--step1: Creating sample table
create table emp(empid int,ename varchar(40),sal money)
insert emp values(1,'Rakesh',4000)
--step2
select * from emp where empid=1
--Check that automatically temporary statistics are created on empid colum
--step3
insert emp values(100,'Rakesh',4000)
go 99
--step4: Check the empid column statistics not updated
--step5: Check execution plan of the following query
              --Estimated no of rows not 100
select * from emp where empid=100 --ctrl+L
--Step6:
create index indx1 on emp(ename)
select * from emp where ename='Rakesh' --ctrl+L
select * from emp where empid=100 --check execution plan and rows
--step7: To verify statistics
SELECT
OBJECT_NAME([sp].[object_id]) AS "Table",
[sp].[stats_id] AS "Statistic ID",
[s].[name] AS "Statistic",
[sp].[last_updated] AS "Last Updated",
[sp].[rows],
[sp].[rows_sampled],
[sp].[unfiltered_rows],
[sp].[modification_counter] AS "Modifications"
FROM [sys].[stats] AS [s]
OUTER APPLY sys.dm_db_stats_properties ([s].[object_id],[s].[stats_id]) AS [sp]
WHERE [s].[object_id] = OBJECT_ID(N'emp');
go
--step8: To update total db stats
sp_updatestats
go
--step9: Verify again (Run step7 and check that modifications are 0)
--step10: Check the execution plan and now Estimated Number of Rows=99
select * from emp where empid=100