filtered indexes in sql server 2008 tech republic

Filtered Indexes in SQL Server 2008 | TechRepublic

http://www.techrepublic.com/blog/datacenter/filtered-indexes-in-sql-server-2008/490[08/29/2012 3:48:09 PM]

Blogs Downloads Newsletters Galleries Q&A Discussions News

Research Library

Home / Blogs / The Enterprise Cloud

The Enterprise Cloud

Filtered Indexes in SQL Server2008

MapR HadoopDownloadMost Open, Enterprise-Grade Distribution forHadoop. Try Now.www.mapr.com/Free-download

Google Docs For BusinessCreate & Upload Images, Tables, Equations,Drawings, Links & More!www.google.com/apps

Dynamics in RomaniaMicrosoft Dynamics NAV Microsoft DynamicsAXwww.llpdynamics.ro

Keep Up with TechRepublic

By Tim ChapmanDecember 22, 2008, 8:44 AM PST

Takeaway: Filtered indexes are a neat new feature in SQL Server 2008 that allows you todefine indexes on subsets of data. In todays article, database architect Tim Chapman shows howyou can take advantage of this useful new feature.

A filtered index is a non-clustered index created on a well-defined subset of data in a SQL Servertable object. By well-defined, I am talking about those sets of data that are used exclusively tosatisfy query criteria. For example, if you have a field in a table that contains predominately NULLvalues, you may benefit from creating a filtered index that only contains those values that are NOTNULL. Note that you cannot define a clustered index with a filter.

Why a filtered index?Filtered indexes can provider performance gains in those scenarios where a majority of queries ona table filter on a specific subset of data. These indexes are likely going to be much smaller thanan index on the entire field, so there is less index storage involved. Also, filtered indexes arelikely going to take less work to maintain. Because the filtered index will be smaller, datamanipulation operations will affect smaller portions of the index, making these operations lesscostly in terms of database I/O.

Creating a filtered indexLets take a look at how to create a filtered index, and how we can see some performance benefitsfrom its use. First, run the following script to create the SalesHistory table and populate it.

IF OBJECT_ID(SalesHistory, U) IS NOT NULL

DROP TABLE SalesHistory

GO

CREATE TABLE [dbo].[SalesHistory]

(

[SaleID] [int] IDENTITY(1,1),

[Product] [varchar](10) NULL,

[SaleDate] [datetime] NULL,

Follow this blog:

Five Apps

Google in the Enterprise

Subscribe Today

Follow us however you choose!

IT Management Development IT Support Data Center Networks Security

Log In Join TechRepublic FAQ Go Pro!ZDNet Asia SmartPlanet TechRepublic

http://ad.doubleclick.net/click;h=v8/3ce0/0/0/%2a/d;44306;0-0;0;74341537;31-1/1;0/0/0;;~sscs=%3fhttp://www.techrepublic.com/http://www.techrepublic.com/http://www.techrepublic.com/blogshttp://www.techrepublic.com/downloadshttp://www.techrepublic.com/newslettershttp://www.techrepublic.com/photoshttp://www.techrepublic.com/forum/questionshttp://www.techrepublic.com/forum/discussionshttp://www.techrepublic.com/newshttp://www.techrepublic.com/research-libraryhttp://www.techrepublic.com/http://www.techrepublic.com/blogshttp://www.techrepublic.com/blog/datacenterhttp://ad.doubleclick.net/click;h=v8/3ce0/0/0/%2a/j;44306;0-0;0;74341537;3823-300/100;0/0/0;;~sscs=%3fhttp://googleads.g.doubleclick.net/aclk?sa=l&ai=B_gEeRNc9UN34IamMlAK8z4DgB4bxrc4CjvnCoDrAjbcBgPJXEAEYASDaxfUDKAM4AFCVm87EB2B1oAHi9pHrA7IBFHd3dy50ZWNocmVwdWJsaWMuY29tyAEB2gFmaHR0cDovL3d3dy50ZWNocmVwdWJsaWMuY29tL2Jsb2cvZGF0YWNlbnRlci9pbnRyb2R1Y3Rpb24tdG8tY2hhbmdlLWRhdGEtY2FwdHVyZS1pbi1zcWwtc2VydmVyLTIwMDgvNTIwgAIBqAMB6APXAugDBegDH_UDAgAAROAGjtL4Ew&num=1&sig=AOD64_1Nj2kt-BQVazp3UdeO2I_zTi6RGw&client=ca-cnet-techrepublic-content&adurl=http://www.mapr.com/download%3Fsource_id%3D5http://googleads.g.doubleclick.net/aclk?sa=l&ai=B_gEeRNc9UN34IamMlAK8z4DgB4bxrc4CjvnCoDrAjbcBgPJXEAEYASDaxfUDKAM4AFCVm87EB2B1oAHi9pHrA7IBFHd3dy50ZWNocmVwdWJsaWMuY29tyAEB2gFmaHR0cDovL3d3dy50ZWNocmVwdWJsaWMuY29tL2Jsb2cvZGF0YWNlbnRlci9pbnRyb2R1Y3Rpb24tdG8tY2hhbmdlLWRhdGEtY2FwdHVyZS1pbi1zcWwtc2VydmVyLTIwMDgvNTIwgAIBqAMB6APXAugDBegDH_UDAgAAROAGjtL4Ew&num=1&sig=AOD64_1Nj2kt-BQVazp3UdeO2I_zTi6RGw&client=ca-cnet-techrepublic-content&adurl=http://www.mapr.com/download%3Fsource_id%3D5http://googleads.g.doubleclick.net/aclk?sa=l&ai=B_gEeRNc9UN34IamMlAK8z4DgB4bxrc4CjvnCoDrAjbcBgPJXEAEYASDaxfUDKAM4AFCVm87EB2B1oAHi9pHrA7IBFHd3dy50ZWNocmVwdWJsaWMuY29tyAEB2gFmaHR0cDovL3d3dy50ZWNocmVwdWJsaWMuY29tL2Jsb2cvZGF0YWNlbnRlci9pbnRyb2R1Y3Rpb24tdG8tY2hhbmdlLWRhdGEtY2FwdHVyZS1pbi1zcWwtc2VydmVyLTIwMDgvNTIwgAIBqAMB6APXAugDBegDH_UDAgAAROAGjtL4Ew&num=1&sig=AOD64_1Nj2kt-BQVazp3UdeO2I_zTi6RGw&client=ca-cnet-techrepublic-content&adurl=http://www.mapr.com/download%3Fsource_id%3D5http://googleads.g.doubleclick.net/aclk?sa=l&ai=B_gEeRNc9UN34IamMlAK8z4DgB4bxrc4CjvnCoDrAjbcBgPJXEAEYASDaxfUDKAM4AFCVm87EB2B1oAHi9pHrA7IBFHd3dy50ZWNocmVwdWJsaWMuY29tyAEB2gFmaHR0cDovL3d3dy50ZWNocmVwdWJsaWMuY29tL2Jsb2cvZGF0YWNlbnRlci9pbnRyb2R1Y3Rpb24tdG8tY2hhbmdlLWRhdGEtY2FwdHVyZS1pbi1zcWwtc2VydmVyLTIwMDgvNTIwgAIBqAMB6APXAugDBegDH_UDAgAAROAGjtL4Ew&num=1&sig=AOD64_1Nj2kt-BQVazp3UdeO2I_zTi6RGw&client=ca-cnet-techrepublic-content&adurl=http://www.mapr.com/download%3Fsource_id%3D5http://googleads.g.doubleclick.net/aclk?sa=l&ai=B_gEeRNc9UN34IamMlAK8z4DgB4bxrc4CjvnCoDrAjbcBgPJXEAEYASDaxfUDKAM4AFCVm87EB2B1oAHi9pHrA7IBFHd3dy50ZWNocmVwdWJsaWMuY29tyAEB2gFmaHR0cDovL3d3dy50ZWNocmVwdWJsaWMuY29tL2Jsb2cvZGF0YWNlbnRlci9pbnRyb2R1Y3Rpb24tdG8tY2hhbmdlLWRhdGEtY2FwdHVyZS1pbi1zcWwtc2VydmVyLTIwMDgvNTIwgAIBqAMB6APXAugDBegDH_UDAgAAROAGjtL4Ew&num=1&sig=AOD64_1Nj2kt-BQVazp3UdeO2I_zTi6RGw&client=ca-cnet-techrepublic-content&adurl=http://www.mapr.com/download%3Fsource_id%3D5http://googleads.g.doubleclick.net/aclk?sa=l&ai=BKdAiRNc9UN34IamMlAK8z4DgB7Tn-bECtKejzzDAjbcBoJaAARACGAIg2sX1AygDOABQ3orVzfj_____AWB1oAGe8_HuA7IBFHd3dy50ZWNocmVwdWJsaWMuY29tyAEB2gFmaHR0cDovL3d3dy50ZWNocmVwdWJsaWMuY29tL2Jsb2cvZGF0YWNlbnRlci9pbnRyb2R1Y3Rpb24tdG8tY2hhbmdlLWRhdGEtY2FwdHVyZS1pbi1zcWwtc2VydmVyLTIwMDgvNTIwqAMB6APXAugDBegDH_UDAgAAROAGuvz8EQ&num=2&sig=AOD64_2Sm_PC7wGbLwJ21GRI7zfDlSQIOw&client=ca-cnet-techrepublic-content&adurl=http://clickserve.dartsearch.net/link/click%3Flid%3D43700003450913598%26ds_s_kwgid%3D58700000030950130%26ds_e_adid%3D12987663020%26ds_e_matchtype%3Dcontent%26ds_url_v%3D2http://googleads.g.doubleclick.net/aclk?sa=l&ai=BKdAiRNc9UN34IamMlAK8z4DgB7Tn-bECtKejzzDAjbcBoJaAARACGAIg2sX1AygDOABQ3orVzfj_____AWB1oAGe8_HuA7IBFHd3dy50ZWNocmVwdWJsaWMuY29tyAEB2gFmaHR0cDovL3d3dy50ZWNocmVwdWJsaWMuY29tL2Jsb2cvZGF0YWNlbnRlci9pbnRyb2R1Y3Rpb24tdG8tY2hhbmdlLWRhdGEtY2FwdHVyZS1pbi1zcWwtc2VydmVyLTIwMDgvNTIwqAMB6APXAugDBegDH_UDAgAAROAGuvz8EQ&num=2&sig=AOD64_2Sm_PC7wGbLwJ21GRI7zfDlSQIOw&client=ca-cnet-techrepublic-content&adurl=http://clickserve.dartsearch.net/link/click%3Flid%3D43700003450913598%26ds_s_kwgid%3D58700000030950130%26ds_e_adid%3D12987663020%26ds_e_matchtype%3Dcontent%26ds_url_v%3D2http://googleads.g.doubleclick.net/aclk?sa=l&ai=BKdAiRNc9UN34IamMlAK8z4DgB7Tn-bECtKejzzDAjbcBoJaAARACGAIg2sX1AygDOABQ3orVzfj_____AWB1oAGe8_HuA7IBFHd3dy50ZWNocmVwdWJsaWMuY29tyAEB2gFmaHR0cDovL3d3dy50ZWNocmVwdWJsaWMuY29tL2Jsb2cvZGF0YWNlbnRlci9pbnRyb2R1Y3Rpb24tdG8tY2hhbmdlLWRhdGEtY2FwdHVyZS1pbi1zcWwtc2VydmVyLTIwMDgvNTIwqAMB6APXAugDBegDH_UDAgAAROAGuvz8EQ&num=2&sig=AOD64_2Sm_PC7wGbLwJ21GRI7zfDlSQIOw&client=ca-cnet-techrepublic-content&adurl=http://clickserve.dartsearch.net/link/click%3Flid%3D43700003450913598%26ds_s_kwgid%3D58700000030950130%26ds_e_adid%3D12987663020%26ds_e_matchtype%3Dcontent%26ds_url_v%3D2http://googleads.g.doubleclick.net/aclk?sa=l&ai=BKdAiRNc9UN34IamMlAK8z4DgB7Tn-bECtKejzzDAjbcBoJaAARACGAIg2sX1AygDOABQ3orVzfj_____AWB1oAGe8_HuA7IBFHd3dy50ZWNocmVwdWJsaWMuY29tyAEB2gFmaHR0cDovL3d3dy50ZWNocmVwdWJsaWMuY29tL2Jsb2cvZGF0YWNlbnRlci9pbnRyb2R1Y3Rpb24tdG8tY2hhbmdlLWRhdGEtY2FwdHVyZS1pbi1zcWwtc2VydmVyLTIwMDgvNTIwqAMB6APXAugDBegDH_UDAgAAROAGuvz8EQ&num=2&sig=AOD64_2Sm_PC7wGbLwJ21GRI7zfDlSQIOw&client=ca-cnet-techrepublic-content&adurl=http://clickserve.dartsearch.net/link/click%3Flid%3D43700003450913598%26ds_s_kwgid%3D58700000030950130%26ds_e_adid%3D12987663020%26ds_e_matchtype%3Dcontent%26ds_url_v%3D2http://googleads.g.doubleclick.net/aclk?sa=L&ai=BqFgwRNc9UN34IamMlAK8z4DgB5Pn1tABw-uZ0jjAjbcBsOMtEAMYAyDaxfUDKAM4AFD1uqqO-f____8BYHWyARR3d3cudGVjaHJlcHVibGljLmNvbcgBAdoBZmh0dHA6Ly93d3cudGVjaHJlcHVibGljLmNvbS9ibG9nL2RhdGFjZW50ZXIvaW50cm9kdWN0aW9uLXRvLWNoYW5nZS1kYXRhLWNhcHR1cmUtaW4tc3FsLXNlcnZlci0yMDA4LzUyMIACAakCFW1BwDARtT6oAwHoA9cC6AMF6AMf9QMCAABE4Abz-ZUI&num=3&sig=AOD64_0fo0-IBpi0gucVbgEvcVGajRwg2w&client=ca-cnet-techrepublic-content&adurl=http://www.llpdynamics.rohttp://googleads.g.doubleclick.net/aclk?sa=L&ai=BqFgwRNc9UN34IamMlAK8z4DgB5Pn1tABw-uZ0jjAjbcBsOMtEAMYAyDaxfUDKAM4AFD1uqqO-f____8BYHWyARR3d3cudGVjaHJlcHVibGljLmNvbcgBAdoBZmh0dHA6Ly93d3cudGVjaHJlcHVibGljLmNvbS9ibG9nL2RhdGFjZW50ZXIvaW50cm9kdWN0aW9uLXRvLWNoYW5nZS1kYXRhLWNhcHR1cmUtaW4tc3FsLXNlcnZlci0yMDA4LzUyMIACAakCFW1BwDARtT6oAwHoA9cC6AMF6AMf9QMCAABE4Abz-ZUI&num=3&sig=AOD64_0fo0-IBpi0gucVbgEvcVGajRwg2w&client=ca-cnet-techrepublic-content&adurl=http://www.llpdynamics.rohttp://googleads.g.doubleclick.net/aclk?sa=L&ai=BqFgwRNc9UN34IamMlAK8z4DgB5Pn1tABw-uZ0jjAjbcBsOMtEAMYAyDaxfUDKAM4AFD1uqqO-f____8BYHWyARR3d3cudGVjaHJlcHVibGljLmNvbcgBAdoBZmh0dHA6Ly93d3cudGVjaHJlcHVibGljLmNvbS9ibG9nL2RhdGFjZW50ZXIvaW50cm9kdWN0aW9uLXRvLWNoYW5nZS1kYXRhLWNhcHR1cmUtaW4tc3FsLXNlcnZlci0yMDA4LzUyMIACAakCFW1BwDARtT6oAwHoA9cC6AMF6AMf9QMCAABE4Abz-ZUI&num=3&sig=AOD64_0fo0-IBpi0gucVbgEvcVGajRwg2w&client=ca-cnet-techrepublic-content&adurl=http://www.llpdynamics.rohttp://googleads.g.doubleclick.net/aclk?sa=L&ai=BqFgwRNc9UN34IamMlAK8z4DgB5Pn1tABw-uZ0jjAjbcBsOMtEAMYAyDaxfUDKAM4AFD1uqqO-f____8BYHWyARR3d3cudGVjaHJlcHVibGljLmNvbcgBAdoBZmh0dHA6Ly93d3cudGVjaHJlcHVibGljLmNvbS9ibG9nL2RhdGFjZW50ZXIvaW50cm9kdWN0aW9uLXRvLWNoYW5nZS1kYXRhLWNhcHR1cmUtaW4tc3FsLXNlcnZlci0yMDA4LzUyMIACAakCFW1BwDARtT6oAwHoA9cC6AMF6AMf9QMCAABE4Abz-ZUI&num=3&sig=AOD64_0fo0-IBpi0gucVbgEvcVGajRwg2w&client=ca-cnet-techrepublic-content&adurl=http://www.llpdynamics.rohttp://cbsiprivacy.custhelp.com/app/answers/detail/a_id/1272/http://www.techrepublic.com/search?q=tim+chapmanhttp://www.techrepublic.com/blog/datacenter?mode=rsshttp://www.techrepublic.com/alerts/add?url=http%3A%2F%2Fwww.techrepublic.com%2Fblog%2Fdatacenter%3Fmode%3Drss&title=The+Enterprise+Cloud+on+TechRepublic&source=http%3A%2F%2Fwww.techrepublic.com%2Fblog%2Fdatacenter%2Ffiltered-indexes-in-sql-server-2008%2F490&frequency=weekly&rss_type=38http://www.facebook.com/TechRepublichttp://twitter.com/techrepublichttps://plus.google.com/115467927011694256253/http://www.linkedin.com/groups?gid=38635http://digg.com/techrepublichttp://www.techrepublic.com/rssfeedshttps://market.android.com/details?id=com.lionbridge.android.techrepublichttp://www.techrepublic.com/members/login?regSrc=global-reghttp://www.techrepublic.com/members/join?regSrc=global-reghttp://www.techrepublic.com/faq/generalhttp://www.techrepublic.com/prohttp://www.zdnetasia.com/http://www.smartplanet.com/http://www.techrepublic.com/



View All

Ask a Question

View All

Media Gallery

[SalePrice] [money] NULL,

CONSTRAINT PK_SalesHistory_SaleID PRIMARY KEY CLUSTERED (SaleID ASC)

)

GO

SET NOCOUNT ON

BEGIN TRANSACTION

DECLARE @i INT

SET @i = 1

WHILE (@i 0

GO

CREATE INDEX idx_SalesHistory_SaleDate

ON SalesHistory(SaleDate)

Run the following command to run IO statistics on. This allows you to view IO values for eachTSQL command ran.

SET STATISTICS IO ON

The following query returns all rows from the SalesHistory table where the SaleDate contains a

Hot Questions

Hot Discussions

More Galleries

More Videos

SSL redirection3

Switching from a Job to a career inthe IT field: Need an IT pro'sadvice

3

windows 7 won't shutdown andkeeps switching on

2

can anyone suggest if any suchsoftware exist with similarfunctionality?

2

Should developers be sued forsecurity holes?

221

The sitting duck that is opensource

80

Five fast Windows desktop searchutilities

27

Is the death knell sounding fortraditional antivirus?

30

PHOTO GALLERY (1 of 15)Curiosity's autonomous'seven minutes of...

VIDEO (1 of 13)Cracking Open: HTC Titan II

http://www.techrepublic.com/forum/questions/hothttp://www.techrepublic.com/forum/questions/posthttp://www.techrepublic.com/forum/questions/posthttp://www.techrepublic.com/forum/discussions/hothttp://www.facebook.com/TechRepublichttp://twitter.com/techrepublichttps://plus.google.com/115467927011694256253/http://www.linkedin.com/groups?gid=38635http://digg.com/techrepublichttp://www.techrepublic.com/rssfeedshttps://market.android.com/details?id=com.lionbridge.android.techrepublichttp://itunes.apple.com/us/app/techrepublic/id426493569http://www.techrepublic.com/photoshttp://www.techrepublic.com/photos/curiositys-autonomous-seven-minutes-of-terror-pictures/6377802http://www.techrepublic.com/videoshttp://www.techrepublic.com/videos/test/cracking-open-htc-titan-ii/6358899http://www.techrepublic.com/forum/questions/101-394302/ssl-redirectionhttp://www.techrepublic.com/forum/questions/101-394290/switching-from-a-job-to-a-career-in-the-it-field-need-an-it-pros-advicehttp://www.techrepublic.com/forum/questions/101-394290/switching-from-a-job-to-a-career-in-the-it-field-need-an-it-pros-advicehttp://www.techrepublic.com/forum/questions/101-394290/switching-from-a-job-to-a-career-in-the-it-field-need-an-it-pros-advicehttp://www.techrepublic.com/forum/questions/101-394299/windows-7-wont-shutdown-and-keeps-switching-onhttp://www.techrepublic.com/forum/questions/101-394299/windows-7-wont-shutdown-and-keeps-switching-onhttp://www.techrepublic.com/forum/questions/101-394316/can-anyone-suggest-if-any-such-software-exist-with-similar-functionalityhttp://www.techrepublic.com/forum/questions/101-394316/can-anyone-suggest-if-any-such-software-exist-with-similar-functionalityhttp://www.techrepublic.com/forum/questions/101-394316/can-anyone-suggest-if-any-such-software-exist-with-similar-functionalityhttp://www.techrepublic.com/forum/discussions/102-394196http://www.techrepublic.com/forum/discussions/102-394196http://www.techrepublic.com/forum/discussions/102-394277http://www.techrepublic.com/forum/discussions/102-394277http://www.techrepublic.com/forum/discussions/102-394303http://www.techrepublic.com/forum/discussions/102-394303http://www.techrepublic.com/forum/discussions/102-394285http://www.techrepublic.com/forum/discussions/102-394285http://www.techrepublic.com/photos/curiositys-autonomous-seven-minutes-of-terror-pictures/6377802http://www.techrepublic.com/videos/test/cracking-open-htc-titan-ii/6358899http://www.techrepublic.com/photos/curiositys-autonomous-seven-minutes-of-terror-pictures/6377802http://www.techrepublic.com/photos/curiositys-autonomous-seven-minutes-of-terror-pictures/6377802http://www.techrepublic.com/photos/curiositys-autonomous-seven-minutes-of-terror-pictures/6377802http://www.techrepublic.com/videos/test/cracking-open-htc-titan-ii/6358899http://www.techrepublic.com/videos/test/cracking-open-htc-titan-ii/6358899



Start a Discussion

Blog Archive

value. This query uses the idx_SalesHistory_SaleDate index we created earlier, and uses anIndex Seek operation to return 2142 rows. This query requires 8 logical reads from the databaseto return the necessary rows.

Note that the queries used in this article only return the SaleDate field in the resultset. The reasonfor this excluding or including more fields in the SELECT list will alter the execution plan. So, forthe purposes of this article, I will only return the field for which I am setting criteria.

SELECT SaleDate

FROM SalesHistory

WHERE SaleDate IS NOT NULL

I can view index related data for my idx_SalesHistory_SaleDate index through querying somesystem views.

SELECT i.name, p.rows, i.filter_definition

FROM

sys.partitions p

JOIN sys.indexes i ON p.object_id = i.object_id AND p.index_id = i.index_id

WHERE

OBJECT_NAME(i.object_id) = SalesHistory

AND i.name = idx_SalesHistory_SaleDate

The following query is similar to the query specified above, but filters those rows where theSaleDate does contain a NULL value. This query also does an index seek on the index I createdabove, but this time requires 31 logical reads from the database as the query returns 12858records.

SELECT SaleDate

FROM SalesHistory

WHERE SaleDate IS NULL

Now that Ive looked a little bit how nonclustered indexes work, Ill take a look at how you can userfliteres when defining the nonclustered index to index only subsets of data. First, Ill need to dropthe index I created above.

DROP INDEX SalesHistory.idx_SalesHistory_SaleDate

In the following script I create a filtered nonclustered index on the SaleDate field. This index willcontain data pointers for ONLY those records for which the SaleDate IS NOT NULL. This meansthat for any records where the SaleDate IS NULL, the index will not be considered at all.

CREATE INDEX idx_SalesHistory_SaleDate

ON SalesHistory(SaleDate)


In the following query, an index scan of the idx_SalesHistory_SalePrice is used with 7 logicaldatabase reads. So, even though an index scan was performed, the operation took less logicalreads due to the filtered index.

SELECT SaleDate

FROM SalesHistory


August 2012

July 2012

June 2012

May 2012

April 2012

March 2012

February 2012

January 2012

December 2011

November 2011

October 2011

September 2011

August 2011

July 2011

June 2011

http://www.techrepublic.com/forum/discussions/posthttp://www.techrepublic.com/forum/discussions/posthttp://www.techrepublic.com/blog/datacenter/201208http://www.techrepublic.com/blog/datacenter/201207http://www.techrepublic.com/blog/datacenter/201206http://www.techrepublic.com/blog/datacenter/201205http://www.techrepublic.com/blog/datacenter/201204http://www.techrepublic.com/blog/datacenter/201203http://www.techrepublic.com/blog/datacenter/201202http://www.techrepublic.com/blog/datacenter/201201http://www.techrepublic.com/blog/datacenter/201112http://www.techrepublic.com/blog/datacenter/201111http://www.techrepublic.com/blog/datacenter/201110http://www.techrepublic.com/blog/datacenter/201109http://www.techrepublic.com/blog/datacenter/201108http://www.techrepublic.com/blog/datacenter/201107http://www.techrepublic.com/blog/datacenter/201106



Join Login

Get IT Tips, news, and reviews delivered directly to your inbox by subscribing to TechRepublics freenewsletters.

About Tim Chapman

Join the TechRepublic Community and join the conversation! Signing-up isfree and quick, Do it now, we want to hear your opinion.

I can query the same system table query as before to view the number of records contained in theindex. The previous index contained all 15,000 records from the table, whereas the current indexcontains only records where the SaleDate IS NOT NULL (2142 records in this case).

SELECT i.name, p.rows, i.filter_definition

FROM

sys.partitions p

JOIN sys.indexes i ON p.object_id = i.object_id AND p.index_id = i.index_id

WHERE

OBJECT_NAME(i.object_id) = SalesHistory

AND i.name = idx_SalesHistory_SaleDate

In the following query, I look for those records where the SaleDate IS NULL. Remember that theindex I defined earlier only contains those records where the SaleDate IS NOT NULL, so it will notbe considered for this query. In fact, a clustered index scan is used to find the records where theSaleDate IS NULL, resulting in 79 logical database reads.

SELECT SaleDate

FROM SalesHistory

WHERE SaleDate IS NULL

ConclusionThe new filtered index feature in SQL Server 2008 is a very useful new feature. It allows you tocreate indexes on only subsets of frequently used data. However, these types of indexes shouldbe used with care. In almost all circumstances, a normal non-clustered index will be the moreuseful index to use rather than a filtered on. Only after you are comfortable with the data usagepatterns of your database should you consider creating filtered indexes, otherwise you may causeyourself

Full Bio Contact

Prerequisites overview forinstalling Windows EssentialBusiness Server 2008

Perils of Adding fields toDatabase Tables

http://www.techrepublic.com/members/join?regSrc=disc-starthttp://www.techrepublic.com/members/join?regSrc=disc-starthttp://www.techrepublic.com/members/login?regSrc=disc-starthttp://www.techrepublic.com/members/login?regSrc=disc-starthttp://www.techrepublic.com/newslettershttp://www.techrepublic.com/newslettershttp://www.techrepublic.com/blog/datacenter/prerequisites-overview-for-installing-windows-essential-business-server-2008/517http://www.techrepublic.com/blog/datacenter/prerequisites-overview-for-installing-windows-essential-business-server-2008/517http://www.techrepublic.com/blog/datacenter/prerequisites-overview-for-installing-windows-essential-business-server-2008/517http://www.techrepublic.com/blog/datacenter/prerequisites-overview-for-installing-windows-essential-business-server-2008/517http://www.techrepublic.com/blog/datacenter/perils-of-adding-fields-to-database-tables/491http://www.techrepublic.com/blog/datacenter/perils-of-adding-fields-to-database-tables/491http://www.techrepublic.com/blog/datacenter/perils-of-adding-fields-to-database-tables/491

techrepublic.comFiltered Indexes in SQL Server 2008 | TechRepublic

luLXNxbC1zZXJ2ZXItMjAwOC80OTAA: form1: email: newsletters: e059:INTERNAL_NEWSLETTERnewsletters_(1): e062:INTERNAL_NEWSLETTERbutton0: q: button3:

filtered indexes in sql server 2008 tech republic

Documents