Filtered index versus normal non-clustered index

Question

I have 270 million entries in the table. I currently have a non-clustered index in a date column. 99% of the time I use strings with a date> 1/1/2008 .., i.e. 140 million of them. I am using SQL Server 2008. In this situation, it will be useful to use a filtered index that is different from a regular non-clustered index?

Also, if I use the "date" data type instead of the "datetime", how useful is it?

Thanks in advance!

Answer 1

Yes, filtered non-clustered index will be used for:

• , . WHERE date ='20101016' ( , 270M).
• , : SELECT COUNT(date) FROM ... WHERE date BETWEEN '20080101' AND '20090101'

. , , .

, , , WHERE date > '200080101', ... . , date ( , , , ), "", 140M 270M .

- . MSDN, : . , .

Answer 2

, , . , "WHERE MyColumn IS NOT NULL", 140 , . , , .

(3- ) , datetime (8- ), . , (), datetime.