Ò׽ؽØÍ¼Èí¼þ¡¢µ¥Îļþ¡¢Ãâ°²×°¡¢´¿ÂÌÉ«¡¢½ö160KB

SQL Server2008 Filtered Indexes

SQL Server Filtered Indexes - What They Are, How to Use and Performance Advantages
Written By: Arshad Ali -- 7/2/2009 -
Problem
SQL Server 2008 introduces Filtered Indexes which is an index with a WHERE clause. Doesn’t it sound awesome especially for a table that has huge amount of data and you often select only a subset of that data? For example, you have a lot of NULL values in a column and you want to retrieve records with only non-NULL values (in SQL Server 2008, this is called Sparse Column). Or in another scenario you have several categories of data in a particular column, but you often retrieve data only for a particular category value.
In this tip, I am going to walk through what a Filtered Index is, how it differs from other indexes, its usage scenario, its benefits and limitations.
Solution
A Filtered Index, which is an optimized non-clustered index, allows us to define a filter predicate, a WHERE clause, while creating the index. The B-Tree containing rows from the filtered index will contain only those rows which satisfy the filter criteria used while creating the index. This optimized index offers several benefits over a full table non-clustered index as follows:
As discussed above, the filtered index contains only those rows which satisfy the defined filter criteria. As a result it reduces the storage space requirement for the index. In the example below I will explain it more.
The filtered statistics or statistics for a filtered index are more compact and accurate, because they consider only the rows in the filtered index and the smaller size of the filtered index reduces the cost/overhead of updating the statistics.
The impact of data modification is less with a filtered index as it is updated only when the data of the index is impacted or a new record is inserted matching the filter criteria.
Maintenance costs will be reduced as well since only a subset of rows will be in consideration while re-organizing or rebuilding the index


Ïà¹ØÎĵµ£º

½«accessµ¼ÈëSQL server

Ò»¡¢SQL SERVER ºÍACCESSµÄÊý¾Ýµ¼Èëµ¼³ö
³£¹æµÄÊý¾Ýµ¼Èëµ¼³ö£º
ʹÓÃDTSÏòµ¼Ç¨ÒÆÄãµÄAccessÊý¾Ýµ½SQL Server£¬Äã¿ÉÒÔʹÓÃÕâЩ²½Öè:
¡¡¡¡¡ð1ÔÚSQL SERVERÆóÒµ¹ÜÀíÆ÷ÖеÄTools£¨¹¤¾ß£©²Ëµ¥ÉÏ£¬Ñ¡ÔñData Transformation
¡¡¡¡¡ð2Services£¨Êý¾Ýת»»·þÎñ£©£¬È»ºóÑ¡Ôñ  czdImport Dat ......

Oracle SQLµÄÓÅ»¯


SQLµÄÓÅ»¯Ó¦¸Ã´Ó5¸ö·½Ãæ½øÐе÷Õû£º
1.È¥µô²»±ØÒªµÄ´óÐͱíµÄÈ«±íɨÃè
2.»º´æÐ¡ÐͱíµÄÈ«±íɨÃè
3.¼ìÑéÓÅ»¯Ë÷ÒýµÄʹÓÃ
4.¼ìÑéÓÅ»¯µÄÁ¬½Ó¼¼Êõ
5.¾¡¿ÉÄܼõÉÙÖ´Ðмƻ®µÄCost
SQLÓï¾ä£º
ÊǶÔÊý¾Ý¿â(Êý¾Ý)½øÐвÙ×÷µÄΩһ;¾¶£»
ÏûºÄÁË70%~90%µÄÊý¾Ý¿â×ÊÔ´£»¶ÀÁ¢ÓÚ³ÌÐòÉè¼ÆÂß¼­£¬Ïà¶ÔÓÚ¶Ô³ÌÐòÔ´´úÂëµÄÓÅ»¯£¬¶ÔSQLÓï¾äµÄÓÅ» ......

SQL ServerÊý¾Ý¿â¿ª·¢µÄ¶þʮһÌõ

Èç¹ûÄãÕýÔÚ¸ºÔðÒ»¸ö»ùÓÚSQL ServerµÄÏîÄ¿£¬»òÕßÄã¸Õ¸Õ½Ó´¥SQL Server£¬Äã¶¼ÓпÉÄÜÒªÃæÁÙһЩÊý¾Ý¿âÐÔÄܵÄÎÊÌ⣬ÕâÆªÎÄÕ»áΪÄãÌṩһЩÓÐÓõÄÖ¸µ¼£¨ÆäÖдó¶àÊýÒ²¿ÉÒÔÓÃÓÚÆäËüµÄDBMS£©¡£
ÔÚÕâÀÎÒ²»´òËã½éÉÜʹÓÃSQL ServerµÄÇÏÃÅ£¬Ò²²»ÄÜÌṩһ¸ö°üÖΰٲ¡µÄ·½°¸£¬ÎÒËù×öµÄÊÇ×ܽáһЩ¾­Ñé----¹ØÓÚÈçºÎÐγÉÒ»¸öºÃµÄÉè¼Æ ......

Ϊʲôsql ÀïÒªÇóstring×Ö¶ÎÓõ¥ÒýºÅÒýÆðÀ´

select * from student where name=?;
Èç¹û²»Óõ¥ÒýºÅÒýÆðÀ´£¬ pstmt.setString(1,"xx or 1=1");¼´sqlÓ¦¸Ã¾ÍÊÇselect * from student where name=xx or 1=1¾Í¿ÉÒÔÈ«²¿²é³ö¡£
Ç¿ÖÆµ¥ÒýºÅÒýÆðÀ´£¬select  * from student where name='xx or 1=1'¡£¾ÍÎÞЧÁË¡£
ÊýÖµÐ͵ÄûÓÐÒªÇóÓõ¥ÒýºÅÒýÆðÀ´£¬Ó¦¸ÃÊÇÓÉÓÚÓÐÒ»¸öת»»¹ý ......
© 2009 ej38.com All Rights Reserved. ¹ØÓÚE½¡ÍøÁªÏµÎÒÃÇ | Õ¾µãµØÍ¼ | ¸ÓICP±¸09004571ºÅ