The tag has no usage guidance.

learn more… | top users | synonyms

3
votes
1answer
98 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 ...
2
votes
1answer
87 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
214 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 ...
0
votes
0answers
65 views

Filtering with cursor pagination

We're using cursor pagination as described in this article over a large dataset using PostgreSQL and Django Rest Framework. I'm trying to get a better understanding of the internals of this to solve ...
8
votes
2answers
214 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, ...
8
votes
3answers
1k 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
76 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
0answers
26 views

Which database is suitable for simple querying/filtering/paging and ability to expire the data?

From what I read, i have to maintain index if I were to use redis. I can set EXPIRE of an object but then I have to update index set myself which is something I rather not do. mongodb has this ...
0
votes
1answer
66 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
65 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, ...
1
vote
1answer
3k 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
181 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
43 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
256 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
232 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 ...
4
votes
2answers
4k 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 ...
13
votes
1answer
287 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 ...
4
votes
0answers
246 views

Rebuild Index Task removed WHERE clause from filtered index [closed]

I have a filter index setup on a table as follows: CREATE UNIQUE NONCLUSTERED INDEX [IX_TableName_Column_Filtered] ON [Schema].[TableName] ( [Column] ASC ) WHERE ([Column] IS NOT NULL) WITH ...
0
votes
1answer
319 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 ...
8
votes
2answers
199 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
397 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 ...
12
votes
3answers
2k 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
331 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 ...