Questions tagged [filtered-index]

The tag has no usage guidance.

Filter by
Sorted by
Tagged with
0 votes
1 answer
74 views

Why new nonclustered index cause more IO reads on table?

We are in process of creating new non-clustered index for specific views. One of the view looks like this: SELECT * FROM [dbo].[table_backup] UNION ALL ( SELECT * FROM [dbo].[tableA] a LEFT JOIN [dbo]....
user avatar
  • 307
1 vote
2 answers
437 views

Index on VARCHAR(MAX) column

Table structure: ID textvalue longtextvalue textvalue has an index and a length of 900 longtextvalue has no index and a length of MAX Goal is to put together both columns with a length of MAX and an ...
user avatar
  • 13
0 votes
2 answers
60 views

Is it worth to use NONCLUSTERD FILTERED INDEX with clause `<>''`?

I am just thinking to create NONCLUSTERED FILTERED INDEX with condition: WHERE varcharColumn <> '' This columns contains around 3127 different values including value ''. All the queries has ...
user avatar
  • 45
-2 votes
2 answers
112 views

Search with more than 5 Million recods With Posgres

Search to be implemented for currently developing a project which is being developed using Spring Boot and Postgres. The search table has at least 8 columns, all the columns are used in the search ...
user avatar
0 votes
1 answer
84 views

SQL Only display data with Max latest date date

tracking_id | date_action | action_id 1 | 2021/01/24 | 4 1 | 2021/01/26 | 3 2 | 2021/01/25 | 67 2 | 2021/01/24 | 8 4 | 2021/01/27 | 10 5 ...
user avatar
7 votes
4 answers
288 views

Using a filtered index when setting a variable

I know from other questions and posts that when SQL compiles a query plan it can only use a filtered index if the filtered index is guaranteed to be able to be used every time the query runs. This ...
user avatar
  • 3,152
0 votes
1 answer
526 views

Receiving "Query processor could not produce a query plan because of the hints defined in this query" when using FORCESEEK but can use FORCESCAN?

I have a fairly large table (500 million rows, 30 columns wide, about 130 GB of data total). One of the columns in that table is a DateTime data type, and I need to repeatedly select records from that ...
user avatar
  • 24.2k
0 votes
1 answer
83 views

quoted identifiers on filtered indices

I´ve 2 questions. I am using a T-SQL Server 2014. When creating a table with a filtered index on a T-SQL server, it is a must-have to set QUOTED_IDENTIFIER to ON. Why is this so? I´ve some SPs which ...
user avatar
  • 25
0 votes
1 answer
95 views

How to get the settings associated with a stored procedure in relation to filtered indexes?

I quite like using filtered indexes in the proper context. I have added the following filtered index: USE [COLA]; CREATE UNIQUE NONCLUSTERED INDEX F_InterviewReport ON [dbo].[...
user avatar
0 votes
1 answer
58 views

Should a filtered index theoretically perform faster in this example?

I have a SuperheroMovies table which has all superhero movies, and has a column called "UniverseName" which has either the values "Marvel", "DC", "Capcom", &...
user avatar
  • 24.2k
1 vote
3 answers
1k views

Filtered index is not used when variable in WHERE condition

Why MS SQL Server refuse using supporting filtered index in this scenario? -- demo data CREATE TABLE #Test ( ID INT IDENTITY(1,1) NOT NULL CONSTRAINT PK_Test_ID PRIMARY KEY ,Col1 NVARCHAR(36) ...
user avatar
  • 935
2 votes
1 answer
75 views

Any benefit to having multiple filtered indexes?

I have a SQL Server database and stumbled across a table with a uniqueidentifier primary key named ActivityId and columns StateCode (int) and ActivityTypeCode (int) along with many others. The tables ...
user avatar
  • 51
6 votes
2 answers
707 views

Datetime conversion fails when filtered index is placed on partition column

I've got a mysterious error of char data conversion at the moment of insertion a new row into a table. Troubleshooting reveals that a filtered index is the source of the problem. The index's filter ...
user avatar
10 votes
1 answer
1k 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 (...
user avatar
  • 27.1k
5 votes
2 answers
18k 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 ...
user avatar
4 votes
3 answers
560 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) ...
user avatar
  • 1,169
25 votes
2 answers
5k 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 ...
user avatar
  • 935
2 votes
1 answer
479 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(...
user avatar
0 votes
2 answers
204 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) ...
user avatar
12 votes
3 answers
3k 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 ...
user avatar
  • 223
1 vote
1 answer
52 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 ...
user avatar
4 votes
1 answer
90 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), ...
user avatar
4 votes
1 answer
403 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 ...
user avatar
3 votes
1 answer
104 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] order ...
user avatar
3 votes
1 answer
571 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....
user avatar
3 votes
1 answer
1k 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 ...
user avatar
  • 279
3 votes
2 answers
2k 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 ...
user avatar
8 votes
2 answers
246 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, ...
user avatar
19 votes
5 answers
5k 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 ...
user avatar
10 votes
3 answers
3k 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 ...
user avatar
  • 423
2 votes
1 answer
310 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 ...
user avatar
0 votes
1 answer
141 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: ...
user avatar
  • 1,721
3 votes
1 answer
404 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, ...
user avatar
6 votes
2 answers
10k 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 ...
user avatar
1 vote
1 answer
2k 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 ...
user avatar
2 votes
0 answers
52 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 ...
user avatar
  • 2,394
1 vote
0 answers
454 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 ...
user avatar
  • 1,344
3 votes
1 answer
643 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 ...
user avatar
  • 1,002
5 votes
2 answers
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 ...
user avatar
14 votes
1 answer
361 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 ...
user avatar
  • 243
0 votes
1 answer
357 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 ...
user avatar
  • 1,735
4 votes
1 answer
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 ...
user avatar
  • 1,735
10 votes
2 answers
1k 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 ...
user avatar
  • 1,735
3 votes
1 answer
495 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 ...
user avatar
  • 1,735
14 votes
3 answers
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 ...
user avatar
  • 283
11 votes
3 answers
422 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)....
user avatar
27 votes
6 answers
19k 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 ...
user avatar
  • 2,538