DZone
Java Zone
Thanks for visiting DZone today,
Edit Profile
  • Manage Email Subscriptions
  • How to Post to DZone
  • Article Submission Guidelines
Sign Out View Profile
  • Post an Article
  • Manage My Drafts
Over 2 million developers have joined DZone.
Log In / Join
  • Refcardz
  • Trend Reports
  • Webinars
  • Zones
  • |
    • Agile
    • AI
    • Big Data
    • Cloud
    • Database
    • DevOps
    • Integration
    • IoT
    • Java
    • Microservices
    • Open Source
    • Performance
    • Security
    • Web Dev
DZone > Java Zone > Some Common Queries Related to Filtered Index

Some Common Queries Related to Filtered Index

Joydeep Das user avatar by
Joydeep Das
·
May. 20, 14 · Java Zone · Interview
Like (0)
Save
Tweet
2.26K Views

Join the DZone community and get the full member experience.

Join For Free

Introduction

The Filtered Index was introduced in MS SQL Server 2008. In this article, we are not directly discussing the Filtered Index, but solving some common queries related to the 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.

Database

Published at DZone with permission of Joydeep Das, DZone MVB. See the original article here.

Opinions expressed by DZone contributors are their own.

Popular on DZone

  • Don't Underestimate Documentation
  • OPC-UA, MQTT, and Apache Kafka: The Trinity of Data Streaming in IoT
  • Refactoring Java Application: Object-Oriented And Functional Approaches
  • Portfolio Architecture Examples: Retail Collection

Comments

Java Partner Resources

X

ABOUT US

  • About DZone
  • Send feedback
  • Careers
  • Sitemap

ADVERTISE

  • Advertise with DZone

CONTRIBUTE ON DZONE

  • Article Submission Guidelines
  • MVB Program
  • Become a Contributor
  • Visit the Writers' Zone

LEGAL

  • Terms of Service
  • Privacy Policy

CONTACT US

  • 600 Park Offices Drive
  • Suite 300
  • Durham, NC 27709
  • support@dzone.com
  • +1 (919) 678-0300

Let's be friends:

DZone.com is powered by 

AnswerHub logo