Filtre uygulanmış dizin tasarım yönergeleri

Bir sütun yalnızca az sayıda sorguları için ilgili değerleri varsa, filtre uygulanmış dizin değerleri alt küme küme ağını üzerinde oluşturabilirsiniz.Örneğin, ne zaman değerleri bir sütun çoğunlukla boştur ve null olmayan değerler yalnızca sorgu seçer, boş olmayan veri satırları için filtre uygulanmış bir dizin oluşturabilirsiniz.Sonuçta elde edilen dizin, daha küçük ve daha aynı anahtar sütunları üzerinde tanımlanan tam tablo kümelenmemiş dizin korumak için daha az maliyet.

Örneğin, AdventureWorks2008R2 veritabanı olan bir Production.BillOfMaterials tablo satırları 2679.The EndDate column has only 199 rows that contain a non-NULL value and the other 2480 rows contain NULL.Aşağıdaki filtre uygulanmış dizin sorguları yalnızca için null olmayan bir değer içeren satırları seçin ve dizinde tanımlanan sütunları dönmek kapak EndDate.

USE AdventureWorks2008R2;
GO
IF EXISTS (SELECT name FROM sys.indexes
    WHERE name = N'FIBillOfMaterialsWithEndDate'
    AND object_id = OBJECT_ID (N'Production.BillOfMaterials'))
DROP INDEX FIBillOfMaterialsWithEndDate
    ON Production.BillOfMaterials
GO
CREATE NONCLUSTERED INDEX FIBillOfMaterialsWithEndDate
    ON Production.BillOfMaterials (ComponentID, StartDate)
    WHERE EndDate IS NOT NULL ;
GO

Filtre uygulanmış dizin FIBillOfMaterialsWithEndDate Geçerli için aşağıdaki sorgu.Belirlemek için sorgu yürütme planı görüntülemek sorgu iyileştiricisi filtre uygulanmış dizin kullanılır.Sorgu yürütme planı görüntüleme hakkında daha fazla bilgi için bkz: Bir sorgu çözümleme.

SELECT ProductAssemblyID, ComponentID, StartDate 
FROM Production.BillOfMaterials
WHERE EndDate IS NOT NULL 
    AND ComponentID = 5 
    AND StartDate > '01/01/2008' ;
GO

Filtre uygulanmış dizinler oluşturmak ve tanımlamak filtre uygulanmış Dizin doğrulama hakkında daha fazla bilgi için ifade, bkz: CREATE INDEX (Transact-SQL).

Ne zaman bir tablo olan heterojen veri satır, bir veya daha fazla veri kategorileri için filtre uygulanmış bir dizin oluşturabilirsiniz.

Örneğin, listelenen ürünlerden Production.Product tablo her atandığı bir ProductSubcategoryID, hangi ilişkilendirilen ürün kategorileri Bisikletler, bileşenleri, elbise ya daDonatılar. Bu kategoriler de kendi sütun değerleri nedeniyle heterojen Production.Product Tablo değil yakından ilişkili.Örneğin, renk, ReorderPoint, ListPrice, ağırlık, sınıf ve stil her ürün kategorisi için benzersiz özelliklere sahiptir.Sık kullanılan sorgular için Donatılar alt kategoriler 27-36 olan olduğunu varsayalım.Donatılar alt kategorilere filtre uygulanmış bir dizin oluşturarak Donatılar için sorguları performansını artırabilir.

Aşağıdaki örnek tüm ürünler Donatılar alt kategorileri, filtre uygulanmış dizin oluşturur Production.Product tablo.

USE AdventureWorks2008R2;
GO
IF EXISTS (SELECT name FROM sys.indexes
    WHERE name = N'FIProductAccessories'
    AND object_id = OBJECT_ID ('Production.Product'))
DROP INDEX FIProductAccessories
    ON Production.Product;
GO
CREATE NONCLUSTERED INDEX FIProductAccessories
    ON Production.Product (ProductSubcategoryID, ListPrice) 
        Include (Name)
WHERE ProductSubcategoryID >= 27 AND ProductSubcategoryID <= 36;
GO

Filtre uygulanmış dizin FIProductAccessories Kapaklar aşağıdaki sorgu için sorgu

sonuçlar dizinde bulunan ve sorgu planı için bir temel tablo araması içermez.Örneğin, sorgu doðrulama deyimi ProductSubcategoryID = 33 alt küme küme küme kümesidir, filtre uygulanmış dizin yüklemi ProductSubcategoryID >= 27 ve ProductSubcategoryID <= 36, ProductSubcategoryID ve ListPrice dizin içindeki her iki anahtar sütunları sütunlar sorgu yüklemi olan ve adı depolanır yaprak düzey dizin olarak gelen bir sütun.

SELECT Name, ProductSubcategoryID, ListPrice
FROM Production.Product
WHERE ProductSubcategoryID = 33 AND ListPrice > 25.00 ;
GO

Sorgu tanımını depolar sanal bir tablo görünümdür; onu daha geniş bir amacı ve işlevi filtre uygulanmış dizin daha var.Görünümler hakkında daha fazla bilgi için bkz: Görünümler anlama ve Görünümleri kullanma senaryoları.Aşağıdaki tablo birlikte, süzülmüş dizinler görünümlerinde izin işlevsellik karşılaştırır.

* where yan tümce tümce tümce sözdizimi, bir koşul olarak basit karşılaştırma mantığı için bkz: create INDEX.

** İçinde bir yüklemi karmaşık karşılaştırma mantığı için where yan tümce tümce tümce sözdizimi için bkz: seçin.

Filtre uygulanmış dizin üzerinde bir görünüm oluşturamazsınız.Ancak, sorgu iyileştiricisi bir görünümde başvurulan bir tablo üzerinde tanımlı filtre uygulanmış bir dizinden yararlanabilirsiniz.Görünümden seçer bir sorgu için filtre uygulanmış bir dizin sorgu en iyi duruma getiricisi düşünür sorgu sonuçlar doğru olacaktır.Aşağıdaki örnek bir görünüm ile 1 Nisan 2000 sonrası başlangıç tarihleri ve başlangıç tarihleri 1 Ağustos 2000 sonra filtre uygulanmış bir dizin oluşturur.

USE AdventureWorks2008R2;
GO
IF OBJECT_ID ('ViewOnBillOfMaterials') IS NOT NULL
DROP VIEW ViewOnBillOfMaterials;
GO
CREATE VIEW ViewOnBillOfMaterials AS 
SELECT ComponentID, StartDate, EndDate, StartDate + 2 AS ShipDate
FROM Production.BillOfMaterials
WHERE StartDate > '20000401';
GO
IF EXISTS (SELECT name FROM sys.indexes
    WHERE name = N'FIBillOfMaterialsByStartDate'
    AND object_ID = OBJECT_ID (N'Production.BillOfMaterials'))
DROP INDEX FIBillOfMaterialsByStartDate 
    ON Production.BillOfMaterials;
GO
CREATE NONCLUSTERED INDEX FIBillOfMaterialsByStartDate
    ON Production.BillOfMaterials (ComponentID, StartDate, EndDate)
WHERE StartDate > '20000801';
GO

Aşağıdaki örnekte, sorgu tüm filtre uygulanmış dizin ve filtre uygulanmış görünüm içinde bulunan başlangıç tarihleri 1 Eylül 2004'den büyük seçer.sorgu iyileştiricisi Filtre uygulanmış dizin düşünür FIBillOfMaterialsByStartDate çünkü doğru sonuçlar için sorgu içerir

SELECT StartDate, ComponentID FROM ViewOnBillOfMaterials
WHERE StartDate > '20040901';
GO

Sonraki örnekte, sorgu tüm görünümünde, ancak filtre uygulanmış dizin bulunan başlangıç tarihleri 1 Haziran 2004'den büyük seçer.sorgu iyileştiricisi, filtre uygulanmış dizin dikkate almaz FIBillOfMaterialsByStartDate çünkü sorgu kullanarak filtre uygulanmış dizin, sorgunun seçtiği görünümü doğru sonuçları ile karşılaştırıldığında farklı sonuçlar geri dönmek

SELECT StartDate, ComponentID FROM ViewOnBillOfMaterials
WHERE StartDate > '20040601';
GO

Filtre uygulanmış dizinleri, dizin oluşturulmuş görünümler aşağıdaki avantajları vardır:

• Dizin bakım maliyetlerini düşürür.Örneğin, bir dizinli görünüm'den süzülmüş dizini güncelleştirmek için daha az cpu kaynağı sorgu işlemci kullanır.

• İyileştirilmiş plan kalitesi.Örneğin, sorgu derleme sırasında sorgu iyileştiricisi eşdeğer dizinli görünüm çok daha fazla durumlarda filtre uygulanmış dizin kullanarak dikkate alır.

• Çevrimiçi dizin yeniden oluşturur.Sorguları için kullanılabilir oldukları sırada filtre uygulanmış dizinler yeniden inşa edebilirsiniz.Dizin oluşturulmuş görünümler için çevrimiçi dizin yeniden oluşturmayı desteklemiyor.Yeniden oluşturma seçeneği için daha fazla bilgi için bkz: alter INDEX (Transact-sql).

• Benzersiz olmayan dizinler.Dizin oluşturulmuş görünümler benzersiz süzülmüş dizinler benzersiz olmayan, olabilir.

Yukarıdaki nedenlerden dolayı dizinli görünüm yerine, mümkün olduğunda filtre uygulanmış bir dizin kullanmanızı öneririz.Aşağıdaki koşullar yerine getirildiğinde, filtre uygulanmış dizin dizinli görünüm yerine kullanmak mümkündür: yalnızca bir tablo görünüme başvuruyor, sorguları hesaplanan sütunlar döndürür ve Görünüm yüklemi basit karşılaştırma mantığı kullanır.BENZERİ içerdiğinden, doðrulama deyimi aşağıdaki görünüm tanımı ancak filtre uygulanmış dizinler verilir işleç.

WHERE StartDate > '20040701' AND ModifiedDate LIKE 'E%'

Az sayıda anahtar eklemek iyi olabilir veya filtre uygulanmış dizin tanımı ve sorgu yürütme planı için filtre uygulanmış dizin seçmek sorgu iyileştiricisi için gerekli olan sütunlar eklemek için sütun dahil.sorgu iyileştiricisi Mı yapar veya sorgu kapak olsun sorgu için filtre uygulanmış bir dizin seçebilirsiniz.Ancak, sorgu iyileştiricisi sorgu kapsıyorsa, filtre uygulanmış bir dizin seçmek daha yüksektir.Kapsayan sorgular hakkında daha fazla bilgi için bkz: Eklenen sütunlar ile dizin oluşturmak.

Bazı durumlarda, filtre uygulanmış Dizin ifade süzülmüş dizini tanımındaki anahtar veya içerilen sütunlar sütunlar dahil olmadan filtre uygulanmış bir dizin sorgu kapsar.Bir sütuna filtre uygulanmış Dizin ifade anahtar olmalıdır veya sütun filtre uygulanmış dizin tanımına dahil aşağıdaki yönergeleri açıklamaktadır.Filtre uygulanmış dizin için örneklere bakın FIBillOfMaterialsWithEndDate daha önce oluşturulmuş.

Bir sütuna filtre uygulanmış Dizin ifade bilmesi gereken bir anahtar veya sütun filtre uygulanmış dizin tanımı filtre uygulanmış Dizin ifade için sorgu yüklemi eşdeğerdir ve sorgu, sorgu sonuçlar ile filtre uygulanmış Dizin ifade içinde sütun döndürmüyor.For example, FIBillOfMaterialsWithEndDate covers the following query because the query predicate is equivalent to the filter expression, and EndDate is not returned with the query results.FIBillOfMaterialsWithEndDate does not need EndDate as a key or included column in the filtered index definition.

SELECT ComponentID, StartDate FROM Production.BillOfMaterials
WHERE EndDate IS NOT NULL;
GO

Bir sütuna filtre uygulanmış Dizin ifade anahtar olmalıdır veya sorgu yüklemi sütun için filtre uygulanmış Dizin ifade eşdeğer bir karşılaştırma kullanıyorsa sütun filtre uygulanmış dizin tanımında yer.Örneğin, FIBillOfMaterialsWithEndDate olduğu için geçerli aşağıdaki sorgu için filtre uygulanmış dizin. satırların alt küme küme küme kümesini seçerBununla birlikte, aşağıdaki sorgu çünkü kapsamaz EndDate karşılaştırmaya kullanılan EndDate > '20040101', filtre uygulanmış Dizin ifade eşdeğer olduğuSorgu işlemci olamaz yürütmek değerlerini bakan olmadan bu sorgu EndDate.Bu nedenle, EndDate bir anahtar olmalı ya da dahil sütun süzülen tanım. dizin

SELECT ComponentID, StartDate FROM Production.BillOfMaterials
WHERE EndDate > '20040101';
GO

Sorguda sütun ise, filtre uygulanmış dizin tanımı dahil veya anahtar sütununda bir sütuna filtre uygulanmış Dizin ifade olmalıdır sonuç küme.Örneğin, FIBillOfMaterialsWithEndDate , verdiği için aşağıdaki sorgu kapak mu EndDate sütun sorgu sonuçlar.Bu nedenle, EndDate bir anahtar olmalı ya da dahil sütun süzülen tanım. dizin

SELECT ComponentID, StartDate, EndDate FROM Production.BillOfMaterials
WHERE EndDate IS NOT NULL;
GO

Birincil anahtar tablo bir anahtar ya da dahil sütun filtre uygulanmış dizin tanımı içinde olmasına gerek yoktur.Birincil anahtar otomatik olarak filtre uygulanmış dizinler dahil olmak üzere tüm kümelenmemiş dizinler dahil edilir.

Karşılaştırma işleç filtre uygulanmış dizin sonuçlar bir hata bir açık veya kapalı veri dönüştürme, filtre uygulanmış Dizin ifade içinde belirtilen dönüşümü karşılaştırma işleç sol tarafında oluşursa ortaya çıkar.Bir karşılaştırma işlecini sağ tarafındaki veri dönüştürme işleç (cast veya convert) ile filtre uygulanmış Dizin ifade yazmak için çözümdür.

Aşağıdaki örnekte çeşitli veri türlerini bir tablo oluşturur.

USE AdventureWorks2008R2;
GO
IF OBJECT_ID ('dbo.TestTable') IS NOT NULL
DROP TABLE dbo.TestTable;
GO
CREATE TABLE TestTable (a int, b varbinary(4));
GO

Dizin tanımı aşağıdaki filtre sütun b amacı için karşılaştırma için bir tamsayı veri türü örtülü olarak dönüştürülür sabit 1.Çünkü dönüştürme işleç filtre uygulanmış yüklemi de sol tarafında oluşur bu 10611 hata iletisi oluşturur.

USE AdventureWorks2008R2;
GO
IF EXISTS ( SELECT name from sys.indexes 
    WHERE name = N'TestTabIndex'
    AND object_id = OBJECT_ID (N'dbo.TestTable'))
DROP INDEX TestTabIndex on dbo.TestTable
GO
CREATE NONCLUSTERED INDEX TestTabIndex ON dbo.TestTable(a,b)
WHERE b = 1;
GO

Dönüştürmek için çözümü olan sabit sütun olarak aynı türde olması için sağ tarafı üzerine b, aşağıdaki örnekte görüldüğü gibi:

CREATE INDEX TestTabIndex ON dbo.TestTable(a,b)
WHERE b = CONVERT(Varbinary(4), 1);
GO

Veri dönüştürme sol tarafından sağ tarafı için bir karşılaştırma işleç, taşıma, dönüştürme anlamını değişebilir.Yukarıdaki örnekte, dönüştürme işleç için sağ tarafı eklendiğinde karşılaştırma değiştirilmiş bir tamsayı karşılaştırması için gelen bir varbinary karşılaştırma.

The sys.sql_expression_dependencies catalog view tracks each column in the filtered index expression as a referencing dependency.Size olamaz bırakın, yeniden adlandırmak veya tanımını değiştirmek bir tablo bir filtre uygulanmış Dizin ifade tanımlanan sütun.

Filtre uygulanmış dizinler sorguları select ifadelerine başvuran veri alt kümelerine iyi tanımlanmış sütunlar içerir yararlı olur.Örnekler şunlardır:

• Sadece birkaç boş olmayan değerler içeren seyrek sütunlar.

• Veri kategorileri içeren türdeş olmayan sütunlar.

• Tutarları, saat ve tarih gibi değerler aralığı içeren sütunlar.

• Sütun değerleri için basit karşılaştırma mantığı tarafından tanımlanan tablo bölümleri.

Filtre uygulanmış dizinler için azaltılmış bakım maliyetleri dizin içindeki satır sayısını bir tam Tablo dizini ile karşılaştırıldığında küçük olduğunda en dikkat çekici.Filtre uygulanmış dizin satır çoğu tabloda yer alıyorsa, bir tam Tablo dizini korumak için daha fazla maliyet.Bu durum, filtre uygulanmış bir dizin yerine bir tam Tablo dizini kullanmalısınız.

Filtre uygulanmış dizinler tanımlanan bir tablo ve yalnızca basit bir karşılaştırma işleçleri destekler.Başvuran birden çok tablo veya karmaşık mantığı olan bir süzgeç ifade gerekiyorsa, bir görünüm oluşturmanız gerekir.

Genel olarak, Veritabanı Altyapısı ve filtre uygulanmış dizinler kümelenmemiş tam tablo dizinleri, filtre uygulanmış dizinler kümelenmemiş dizinler, özel bir türü olarak düşünmeden için girdikleri için aynı destek araçları sağlarAşağıdaki listede, araçları ve tam destek, destek veya filtre uygulanmış dizinler için destek sınırlandırmış özellikleri hakkında notlar sağlar.

• Filtre uygulanmış dizinler alter INDEX destekler.Filtre uygulanmış bir dizini değiştirmek için ifade, DROP_EXISTING ile dizin oluşturmak için kullanın.

• Eksik dizinler özelliği süzülmüş dizinler önerin.

• The Veritabanı Altyapısı Tuning Advisor considers filtered indexes when recommending index tuning advice and can recommend an is not null filtered index.

• Filtre uygulanmış dizinler çevrimiçi dizin işlemleri desteği.

• Tablo ipuçları filtre uygulanmış dizinleri desteklemez, ancak olmayan filtre uygulanmış dizinler için geçerli olan bazı kısıtlamaları vardır.Bu durum aşağıdaki bölümde açıklanmaktadır.

Bazı durumlarda, bir Parametreli Sorgu derleme saat filtre uygulanmış bir dizin seçmek sorgu iyileştiricisi için yeterli bilgi içermiyor.Eksik bilgi sağlamak için sorguyu yeniden yazmak mümkün olabilir.Aşağıdaki örnekte, sorgu iyileştiricisi filtre uygulanmış dizin dikkate almaz FIBillOfMaterialsWithComponentID için deyim parametre için değer çünkü @p ve @q bilinmiyor derleme saat.Böylece parametreleştirilmiş sorguları için filtre uygulanmış dizinler eşleşmeyen showplan_xml çıktıda görebilirsiniz on küme showplan_xml aşağıdaki sorgu örnek çalışır.

USE AdventureWorks2008R2;
GO
IF EXISTS ( SELECT name FROM sys.indexes
    WHERE name = N'FIBillOfMaterialsWithComponentID'
    AND object_id = OBJECT_ID (N'Production.BillOfMaterials'))
DROP INDEX FIBillOfMaterialsWithComponentID
    ON Production.BillOfMaterials;
GO
CREATE NONCLUSTERED INDEX FIBillOfMaterialsWithComponentID
    ON Production.BillOfMaterials (ComponentID, StartDate, EndDate)
WHERE ComponentID IN (533, 324, 753);
GO
SET SHOWPLAN_XML ON;
GO
DECLARE @p AS INT, @q AS INT;
SET @p = 533;
SET @q = 324;
SELECT StartDate, ComponentID from Production.BillOfMaterials 
WHERE ComponentID = @p OR ComponentID = @q;
GO
SET SHOWPLAN_XML OFF;
GO

The UnmatchedIndexes element and Parameterization subelement in the SHOWPLAN_XML output indicate that the filtered index was not a match for the query.showplan_xml çıktısını görüntüleme hakkında daha fazla bilgi için bkz: xml Showplans.

Bir sorguyu değiştirmek için çözümdür böylece sorgu sonuçlar parametreli bir ifade alt küme küme küme kümesi filtre yüklemi değil boş olur.Aşağıdaki sorgu, bu değişiklik göstermektedir.Ekleyerek ComponentID in (533, 324, 753) ifade where yan tümce tümce tümce sorgunun sonuçlar alt küme küme küme kümesi filtre uygulanmış yüklemi olduğu garanti ifade.Bu değişiklik ile sorgu iyileştiricisi filtre uygulanmış dizin düşünebilirsiniz FIBillOfMaterialsWithComponentID için aşağıdaki SELECT deyim.

USE AdventureWorks2008R2;
GO
SET SHOWPLAN_XML ON;
GO
DECLARE @p AS INT, @q AS INT;
SET @p = 533;
SET @q = 324;
SELECT StartDate, ComponentID FROM Production.BillOfMaterials
WHERE ComponentID in (533, 324, 753)
    AND (ComponentID = @p OR ComponentID = @q);
GO
SET SHOWPLAN_XML OFF;
GO

Basit parameterizasyonunu

Rağmen bu sorguyu filtre uygulanmış dizin kapak kalan sütunları almak için bir anahtar araması yaparak kapsamaz sorgu iyileştiricisi bir filtre uygulanmış dizin kullanabilirsiniz.Anahtar aramaları hakkında daha fazla bilgi için bkz: Arama Showplan işleç anahtar.sorgu iyileştiricisi Anahtar aramaları tahmini sayısı küçük ise bu yaklaşımı tercih edebilirsiniz.Sorgu işlemci kullanmaya zorlamak için bir dizin ipucu aşağıdaki sorguyu kullanır FIBillOfMaterialsWithEndDate için yer işareti arama ile EndDate.Anahtar arama için ortaya EndDate > @date karşılaştırma sorgu yüklemi.

USE AdventureWorks2008R2;
GO
DECLARE @date AS DATE;
SET @date = '20040825'
SELECT ComponentID, StartDate, EndDate FROM Production.BillOfMaterials
WITH ( INDEX (FIBillOfMaterialsWithEndDate) )
WHERE EndDate > @date;
GO

Dikkat EndDate > @Date olan filtre uygulanmış Dizin ifade ile tam bir eşleşme değil EndDate IS NOT NULL.Filtre uygulanmış dizin bu parametreli sorgu için hala geçerli çünkü filtre uygulanmış Dizin ifade tanımlanmış satırların alt küme küme küme kümesini döndürür.