Stack Exchange Network

Stack Exchange network consists of 174 Q&A communities including Stack Overflow, the largest, most trusted online community for developers to learn, share their knowledge, and build their careers.

Visit Stack Exchange

Questions tagged [filtered-index]

The tag has no usage guidance.

4
votes
1answer
121 views

Should the filtering column(s) always be in the keys / includes?

I'm considering creating a filtered index in my copy of the Stack Overflow database. Something like this, for example: CREATE UNIQUE NONCLUSTERED INDEX IX_DisplayName_Filtered ON dbo.Users (...
0
votes
0answers
26 views

when to use Simple or Forced Parameterization in my databases?

I have recently created a filtered index in one of my databases but it would neither be used nor accepted in an index hint in any query unless I added the option(recompile) hint in it. it turned out ...
2
votes
1answer
46 views

Query processor could not produce a query plan because of the hints defined in this query

There is a similar question, but it is not the same: The query processor could not produce a query plan I have the following query, and the following filtered index, and I cannot see any reason why ...
4
votes
2answers
92 views

Temp Table Filtered Index Creation is Blocked When Stored Procedure is Run Multiple Times Concurrently

Are there any known blocking issues in SQL Server 2014 that are specific to creating one or more filtered indexes on a temp table after the initial table creation? (i.e. not an inline index creation) ...
9
votes
1answer
319 views

Why filtered index on IS NULL value is not used?

Assume we have a table definition like this: CREATE TABLE MyTab ( ID INT IDENTITY(1,1) CONSTRAINT PK_MyTab_ID PRIMARY KEY ,GroupByColumn NVARCHAR(10) NOT NULL ,WhereColumn DATETIME NULL ...
2
votes
1answer
112 views

Query using a filtered index but wrong estimated number of rows

I have a table with a nonclustered index on a datetime2 field. On that same table i have a field (char[1]) which is used to logically delete records and can have 2 distinct values : A(active) or D(...
0
votes
2answers
68 views

a filtered index to support a query - is it possible to consider nulls?

I have been dealing with this query: SELECT @amount = sum(accountAmount) FROM dbo.accountTransaction WHERE invoiceID = @invoiceID AND (notCountedInTotal = 0 OR notCountedInTotal IS NULL) ...
12
votes
3answers
2k views

Use “LEN” function in “WHERE” clause in “CREATE UNIQUE INDEX”

I have this table: CREATE TABLE Table01 (column01 nvarchar(100)); And I want to create a unique index on column01 with this condition LEN(column01) >= 5 I tried: CREATE UNIQUE INDEX UIX_01 ON ...
1
vote
1answer
42 views

using statistics in order to decide whether or not to add a filtered index

I have many queries on table dbo.tblSAPDespatchItem, some of them have the following condition in the where clause: WHERE b.lngDespatchItemStatusID = 2 AND b.decvalue > 0 for example ...
4
votes
1answer
65 views

Where are filtered-index dependencies listed?

Given a table of the form... create table dbo.FilterIDXTest ( id int not null identity primary key clustered, _bigint bigint, _varchar10 varchar(10), _varchar20 varchar(20), ...
3
votes
1answer
243 views

Why is this filtered index not only not used but errors when I try to force it [duplicate]

I'm trying to tune a rather annoying query where a number of the tables have something that looks like: date = '9999-12-31 23:59:59.9999999' At least a few of the tables are in the 300 mil row range ...
2
votes
0answers
80 views

what if all the non clustered indexes on my table were filtered indexes?

I have a table that is used heavily. This table has 370,370 records. When I run the following select: select [ImageSize] , the_num = count(*) from [dbo].[ProductImages] group by [ImageSize] ...
3
votes
1answer
317 views

Optimizer gets the wrong number of estimated rows even with updated stats [closed]

I have a procedure running in SQL Server 2008 that contains a query similar to the one below; SELECT /* various columns from table 1 and table 2 */ FROM dbo.tbl_1 AS a INNER JOIN dbo.tbl_2 AS b ON a....
3
votes
1answer
307 views

Filtered Index hint rejected by SQL Server

I have a filtered non-clustered index that SQL Server is not using. I'm pretty sure the optimizer is doing the right choice but I would like to force it to run with that index so I can compare the ...
3
votes
2answers
865 views

Unable to create UNIQUE Filtered index with IGNORE_DUP_KEY = ON

I have a table , in SQL Server 2012 enterprise edition, for which I want to have a unique index with IGNORE_DUP_KEY = ON, however I only want this index (and ignoring the duplicates) to apply only to ...
8
votes
2answers
227 views

is there a work around for when you want to put an OR inside a filtered index?

is there a work around for when you want to put an OR inside a filtered index? create index FIDX_tblbOrders_sdtmOrdCreated_INCL on dbo.tblBOrder(sdtmOrdCreated) INCLUDE (sintMarketID, ...
17
votes
5answers
2k views

Unable to create a Filtered Index on a Computed Column

In a previous question of mine, Is it a good idea to disable lock escalation while adding new calculated columns to a table?, I am creating a computed column: ALTER TABLE dbo.tblBGiftVoucherItem ADD ...
10
votes
3answers
2k views

Filtered index only used when the filtered part is in the JOIN not the WHERE

I've created the filtered index below however when I run the 2 queries further down this index only gets used for a seek in the first example that has the END_DTTM in the JOIN rather than the where ...
2
votes
1answer
225 views

how to get the missing indexes that are “filtered indexes”

I have a procedure that shows me the missing indexes. It has been very good to me, but it does not show me the missing FILTERED INDEXES. I would like my procedure to be able to catch and suggest ...
0
votes
1answer
121 views

NC Index not being used [closed]

I have a table (9mm records) with a field called filename nvarchar(max)(I didn't decide on this datatype and can't change it). I'm wanting to count how many records meet the following criteria: ...
2
votes
1answer
140 views

Problem trying to force the use of a filtered index

I was trying to do some testing on filtered indexes yesterday and created a fairly simple situation: CREATE TABLE IndexTest ( ID INT NOT NULL IDENTITY(1,1) CONSTRAINT pk_IndexTest PRIMARY KEY, ...
5
votes
1answer
7k views

Filtered index throwing error - UPDATE failed because the following SET options have incorrect settings: ANSI_WARNINGS

While executing proc1, I am getting error as below: UPDATE failed because the following SET options have incorrect settings: 'ANSI_WARNINGS'. Verify that SET options are correct for use with ...
1
vote
1answer
617 views

SQL Server unique index, per subset of data, based on values of another column

Say I have a Customers table, and an Emails table. Emails table has these fields: Id CustomerId EmailValue Now, I want to create a constraint so that no duplicate emails should be ...
2
votes
0answers
48 views

Filtered Index is not selected once an identical, non-filtered, index is made [duplicate]

I have a lot of historical data, but 98% of the time, my data entry team only looks up data from the last 5 days. So I added a filtered index to help those lookups be faster. My test query looks ...
1
vote
0answers
419 views

Why is Update Query Slow with correct Indexes?

I have a query that updates a temp table. There are 2 Filtered indexes (by date) used in this query. However, the update take 8-10 mins to run. I am struggling to find out why it is so slow. There is ...
3
votes
1answer
440 views

Why is my filtered index being ignored?

--Setup, actual queries after the space --create table fltrind (a integer,b integer) truncate table fltrind DROP INDEX fltrind.nf DROP INDEX fltrind.f DECLARE @i integer SET @i=0 while @i<100 BEGIN ...
5
votes
2answers
6k views

Filtered index not chosen, and rejected with hint

I'm trying to understand why a non-clustered index is not available for a given query when filtered. The relevant part of my (large) query is this: ) results JOIN BE_Insurance ins ON results.PayorId ...
15
votes
1answer
302 views

Why aggregate operator used after unique index scan

I have a table with a unique index filtered for non-nullable values. In the query plan there is use of distinct. Is there a reason for this? USE tempdb CREATE TABLE T1( Id INT NOT NULL IDENTITY ...
0
votes
1answer
342 views

SQL Server 2008 - Question about index behaviour

I have a general question about advanced issues regarding index behaviour. In short, roughly one year ago, we dynamically dropped and recreated a number of indexes with different filters, but using ...
4
votes
1answer
1k views

SQL Server 2008 query planner failing after index drop & recreate

Recently we ran a script to our production DB that would dynamically drop and recreate hundreds of indexes as filtered indexes. While this script had run perfectly in all other previous tests, now ...
9
votes
2answers
508 views

What is the effect of replacing indexes with filtered (non-null value) indexes?

Our project runs a very large, very complicated database. So about a month ago, we noticed that the space used by indexed columns containing null values was getting too large. As a response to that, I ...
3
votes
1answer
485 views

SQL Server 2008 - Adding a column and a filtered index within a single transaction?

This is more of a general question. Why can you not add a filtered index within the same transaction scope as adding the new column in the first place? The solution is of course simple, just make two ...
14
votes
3answers
3k views

Improve performance of query using IN()

I have the following SQL query: SELECT Event.ID, Event.IATA, Device.Name, EventType.Description, Event.Data1, Event.Data2 Event.PLCTimeStamp, Event.EventTypeID FROM Event INNER JOIN ...
11
votes
3answers
371 views

Included Column versus Filtered Index

We currently work with table named tb_tranfers. This table has 40 million rows and is ~26 GB in size (11 GB data, 15 GB indexes). 10 to 15% of the rows are soft-deleted rows (DeletedDate is not null)....
23
votes
5answers
9k views

How to create a conditional index in MySQL?

How to create an index to filter a specific range or subset of the table in MySQL? AFAIK it's impossible to create directly but I think it's possible to simulate this feature. Example: I want to ...