Tags
2.NonClustered Index, Advanced SQL tutorial pdf, change fill factor while creating NonClustered Index, clustered and non clustered index in sql, Clustered and Nonclustered Indexes Described, Clustered Index, clustered index in sql, Clustered Index vs. Non-Clustered Index in SQL Server, Clustered versus Non Clustered Index, Clustered vs. Nonclustered Index Structures in SQL Server, Covering Index, Covering Index in SQL, Difference Between Clustered and Non-Clustered Indexes, Difference between clustered and nonclustered index, difference between clustered and nonclustered index in sql, difference between clustered and nonclustered index in sql server with example, Download SQL Questions, Download SQL Server Interview Question in PDF, Download SQL SERVER Interview questions, Download SQL Server Interview questions and answers, download sql server interview questions and answers pdf, download sql server interview questions by Pawan Khowal, download sql server interview questions by Pawan Kumar, download sql server interview questions by Pawan Kumar Khowal, Download T-SQL Interview Questions, Fill factor in Indexes, Filtered Index, Free Download SQL SERVER Interview questions, Indexes in detail, Learn Indexes in detail, NonClustered Index with Included columns, SQL, SQL Common Interview Questions, SQL Common Interview Questions and answers, SQL FAQ, SQL FAQs, SQL Interview Q & A, SQL Interview Questions, SQL Queries asked in interviews, SQL Questions, SQL Server - General Interview Questions and Answers, SQL Server developer Interview questions and answers, SQL Server developer Interview questions with answers, SQL SERVER Indexes, SQL SERVER Interview questions, SQL SERVER Interview questions & Answers, SQL Server Interview questions and answers, SQL Server Interview Questions and Answers - Free PDF, sql server interview questions and answers for net developers, SQL SERVER Tips, SQL Tips & Tricks, SQL Tips and Tricks, SQL Tricks, T-SQL Server Interview Questions, Types of Indexes, Types of Indexes in SQL
SQL SERVER Indexes – ALL (Covers almost everything) [Continued – Page 3]
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.
You must be logged in to post a comment.