SQL SERVER Indexes – ALL (Covers almost everything) [Continued – Page 3]

https://msbiskills.com/

Download – SQL SERVER Indexes Interview Notes – Pawan Kumar Khowal

Filtered Index

1	If you add a where clause to a NonClustered index it is called Filtered Index.
2	When you create a NonClustered index, you can add a WHERE clause to reduce the number of rows that are stored at the leaf level.
3	If we have fewer rows in an index then less I/O will be used and it will improve query performance.
4	Also the size of the index will be smaller since we have fewer rows in the index. Hence less space will be used by the Index.
5	Filtered indexes are excellent for performance if you have a value that is used in a where clause very frequently and that value is only a small amount of the total values for that table.
6	The example of Filtered index is given below.

--

CREATE TABLE testFilterIndexes
(
	 ID INT
	,Name VARCHAR(50)
	,OrderStatus BIT --can be 0,1 OR NULL
)
GO

CREATE NONCLUSTERED INDEX Ix_Filx ON testFilterIndexes(OrderStatus)
WHERE OrderStatus IN ( 0 , 1 )

SELECT OrderStatus FROM testFilterIndexes
WHERE OrderStatus = 0 OR OrderStatus = 1

Output
---------

--

7	You cannot use OR in filtered indexes. Please check out the example below.

--

CREATE NONCLUSTERED INDEX Ix_Filx1 ON testFilterIndexes(OrderStatus)
WHERE OrderStatus =  0 OR  OrderStatus =  1

Output
--------

Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'OR'.

--

8	Please check out some sample Filtered Indexes below. We can use AND, IN, NULL, NOT NULL.

--

CREATE NONCLUSTERED INDEX Ix_Filx1 ON testFilterIndexes(OrderStatus)
WHERE OrderStatus =  0 AND OrderStatus =  1

CREATE NONCLUSTERED INDEX Ix_Filx2 ON testFilterIndexes(OrderStatus)
WHERE OrderStatus IN ( 0 , 1 )

CREATE NONCLUSTERED INDEX Ix_Filx3 ON testFilterIndexes(OrderStatus)
WHERE OrderStatus IS NOT NULL

CREATE NONCLUSTERED INDEX Ix_Filx4 ON testFilterIndexes(OrderStatus)
WHERE OrderStatus IS NULL

---

9	You cannot use BETWEEN, NOT IN, or a CASE statement with Filtered Indexes.
10	The query optimizer won’t use filtered indexes if you’re using local variables or parameterized SQL. Use the way we have used our dynamic parameterized queries given below.

--

SELECT OrderStatus FROM testFilterIndexes
WHERE OrderStatus = 0

DECLARE @SQL NVARCHAR(MAX), @OrderStatus INT 

SET @OrderStatus = 0 

SET @SQL = N' SELECT OrderStatus FROM testFilterIndexes WHERE OrderStatus =' + CAST(@OrderStatus AS VARCHAR(10)) 

EXECUTE sp_executesql @SQL

--

Final Say-

Indexes are very easy to add to your database to improve performance. However, too much of an indexes can be bad as we have to pay their maintenance cost. When designing a database, or troubleshooting poor performing query, consider all your indexes carefully and test them thoroughly.