Some Common Query related to FILTERED INDEX

Introduction

The Filtered Index is introduced at MS SQL Server 2008. Here in this article we are not directly discuss the Filtered Index, but solve some common query in mind Related to Filter Index.

An Example of Filtered Index

-- Base table

IF OBJECT_ID(N'dbo.tbl_EMPLOYEE', N'U') IS NOT NULL

   BEGIN

     DROP TABLE [dbo].[tbl_EMPLOYEE];

   END

GO

CREATE TABLE [dbo].[tbl_EMPLOYEE]

  (EMPID   INT         NOT NULL IDENTITY PRIMARY KEY,

   EMPNAME VARCHAR(50) NOT NULL,

   DOB     DATE        NOT NULL,

   GRADE   CHAR(1)     NOT NULL);

GO

-- Inserting Records

INSERT INTO [dbo].[tbl_EMPLOYEE]

    (EMPNAME, DOB, GRADE)

VALUES('Joydeep Das', '1974-12-17', 'A'),

      ('Jukamal Jana','1974-10-11', 'A'),

      ('Chandan Bannerjee','1973-06-09', 'B'),

      ('Soumen Bhomik', '2008-11-28', 'C');

GO

     

-- Create Filtered Index

CREATE NONCLUSTERED INDEX IX_NON_GRADE_tbl_EMPLOYEE

ON [dbo].[tbl_EMPLOYEE](GRADE)

WHERE GRADE = 'A';     

Now we are trying to solve some Query

Query – 1

What Type of Comparison operator used in Filter Criteria in Filtered Index

Comparison Operator

=

!=

<

<=

>

>=

IS NULL

IS NOT NULL

Query – 2

Is BETWEEN Clause is used in Filter Criteria of Filtered Index

NO. The BETWEEN Clause is not used in Filter Criteria of FILTERED Index. We can use >= and <= to get the output like BETWEEN Clause.

CREATE NONCLUSTERED INDEX IX_NON_GRADE_tbl_EMPLOYEE

ON [dbo].[tbl_EMPLOYEE](GRADE)

WHERE DOB >= '1974-01-01' AND DOB <= '1974-12-31';

Query – 3

Is it possible to add more data to Filtered Index without dropping and re-creating it

Yes it is possible.

Query – 4

Can we use the SUB QUERY in the Filtered Index WHERE Clause like

WHERE Grate=(SELECT grade FROM tbal_2)

NO it is not Possible. WHERE Clause of Filtered Index always needs a Scalar value.

As per MSDN  

http://msdn.microsoft.com/en-us/library/ms188783.aspx

Under the "WHERE" section of the Arguments heading:

The filter predicate uses simple comparison logic and cannot reference a computed column, a UDT column, a spatial data type column, or a hierarchyID data type column. Comparisons using NULL literals are not allowed with the comparison operators. Use the IS NULL and IS NOT NULL operators instead.

If you have any question in mind related to FILTERED INDEX please make some comments to solve it.

Hope you like it.

Posted by: MR. JOYDEEP DAS