filtered index selectivity calcs

FISC.sql

dbcc traceon(2363,3604)
go

set nocount on;

create table #test (
	id int
)

declare @id int = 1;

while @id <= 1000
	begin
		print @id
		
		insert #test select @id;
		set @id = @id + 1;
	end

create index ix_test on #test(id)
	where id = 5;
go

select * 
from #test
where id = 5

/*
	calculated selectivity of .001 with notes:
	Begin selectivity computation

		Input tree:

		  LogOp_Select

			  CStCollBaseTable(ID=1, CARD=1000 TBL: #test)

			  ScaOp_Comp x_cmpEq

				  ScaOp_Identifier QCOL: [tempdb].[dbo].[#test].id

				  ScaOp_Const TI(int,ML=4) XVAR(int,Not Owned,Value=5)

		Plan for computation:

		  CSelCalcColumnInInterval

			  Column: QCOL: [tempdb].[dbo].[#test].id

		Loaded histogram for column QCOL: [tempdb].[dbo].[#test].id from stats with id 2

		Selectivity: 0.001

		Stats collection generated: 

		  CStCollFilter(ID=2, CARD=1)

			  CStCollBaseTable(ID=1, CARD=1000 TBL: #test)

		End selectivity computation

*/

create index ix_test_5k on #test(id)
where id >= 500;

select id
from #test
where id >= 525

/*
	Begin selectivity computation

	Input tree:

	  LogOp_Select

		  CStCollBaseTable(ID=1, CARD=1000 TBL: #test)

		  ScaOp_Comp x_cmpGe

			  ScaOp_Identifier QCOL: [tempdb].[dbo].[#test].id

			  ScaOp_Const TI(int,ML=4) XVAR(int,Not Owned,Value=525)

	Plan for computation:

	  CSelCalcColumnInInterval

		  Column: QCOL: [tempdb].[dbo].[#test].id

	Loaded histogram for column QCOL: [tempdb].[dbo].[#test].id from stats with id 3

	Selectivity: 0.476

	Stats collection generated: 

	  CStCollFilter(ID=2, CARD=476)

		  CStCollBaseTable(ID=1, CARD=1000 TBL: #test)

	End selectivity computation

*/
dbcc traceoff(2363,3604)
go
set nocount off;
go