Stack Overflow
Stack Overflow

Questions tagged [indexed-view]

142 questions
2
votes
0 answers

Why does STATISTICS_NORECOMPUTE turn on by itself?

I am running SQL Server 13.0.5622.0. I have a number of large indexed views (>5MM rows) defined similar to: CREATE VIEW MyView WITH SCHEMABINDING AS SELECT T1.Column1, T1.Column2 FROM dbo.T1 INNER JOIN dbo.T2 ON T2.Id = T1.Id WHERE…
Mike
  • 7,500
  • 8
  • 44
  • 62
2
votes
1 answer

Querying a view after creating a clustered index on it still yields the same query plan

I have the following view SET QUOTED_IDENTIFIER ON SET ANSI_NULLS ON GO ALTER VIEW web.vGridHotelBooking WITH SCHEMABINDING AS SELECT HBK_ID, COF_ID, COF_CST_ID, HTL_Name, COF_Data FROM …
Mihail Shishkov
  • 14,129
  • 7
  • 48
  • 59
2
votes
1 answer

Is there benefit to index base tables of an indexed view?

After I created the indexed view, I tried disabling all the indexes in base tables including the indexes for foreign key column (constraint is still there) and the query plan for the view stays the same. It is just like magic to me that the indexed…
Steve
  • 11,696
  • 7
  • 43
  • 81
2
votes
1 answer

SQL Server columnstore clustered index on an indexed view

I want to have an automatically updated structure in SQL Server that can handle aggregate queries well. Can I create an indexed view in SQL server 2014 that has a columnstore index as its clustered index?
uh_big_mike_boi
  • 3,350
  • 4
  • 33
  • 64
2
votes
1 answer

On-demand refresh mode for indexed view (=Materialized views) on SQL Server?

I know Oracle offers several refreshmode options for their materialized views (on demand, on commit, periodically). Does Microsoft SQLServer offer the same functions for their indexed views? If not, how can I else use indexed views on SQLServer if…
MOLAP
  • 784
  • 4
  • 13
  • 28
2
votes
1 answer

Equivalent of Materialised Views in SQL Server

We have something called Materialized Views in Oracle, do we have anything like it in SQL Server. After doing some findings on Google, I feel that Indexed Views can be somewhat similar to it. Can anyone highlight me with the difference between…
user3793074
  • 23
  • 1
  • 6
2
votes
1 answer

WHERE Clause Performance While Querying an Indexed View

I created an indexed view (to save time on a very time-consuming aggregation) using a code like the following (simplified): CREATE VIEW vCosts WITH SCHEMABINDING AS SELECT ProjectID ,YEAR(Date) AS Year ,SUM(Cost) AS…
Serital
  • 343
  • 3
  • 13
2
votes
2 answers

get the latest record using an indexed view

I am working on a monitoring project, and every time I collect the data I generate a new ID for that collection. I would like to have the latest code in an indexed view. SELECT THE_ID = MAX(THE_ID) FROM…
Marcello Miorelli
  • 3,368
  • 4
  • 44
  • 67
2
votes
3 answers

Index view: How to choose the Clustered Index?

I'm going to do an indexed view, based on three tables with inner and outer joins between them (SQL Server 2005). I will run all kind of queries against this view. So, I wonder what is the best way to choose which index to be clustered. What are…
anthares
  • 11,070
  • 4
  • 41
  • 61
2
votes
1 answer

How did a not null date become nullable in a view

I have a datetime field in a table called dbo.Traffic I am trying to aggregate traffic data by day. I am planing on creating a schemabound view and adding an index. CREATE VIEW [dbo].[vwTraffic] WITH SCHEMABINDING AS SELECT CONVERT(date,…
James A Mohler
  • 11,060
  • 15
  • 46
  • 72
2
votes
3 answers

View Clustered Index Seek over 0.5 million rows takes 7 minutes

Take a look at this execution plan: http://sdrv.ms/1agLg7K It’s not estimated, it’s actual. From an actual execution that took roughly 30 minutes. Select the second statement (takes 47.8% of the total execution time – roughly 15 minutes). Look at…
Fyodor Soikin
  • 78,590
  • 9
  • 125
  • 172
2
votes
2 answers

SQL Server 2005 Index Filter Feature

I was told that there is new a feature in SQL Server 2005 called index filters. What I want to do is add an Index to a column and have the index ignore null values. I can't find good information on this feature (maybe my source is wrong). Can…
codingguy3000
  • 2,695
  • 15
  • 46
  • 74
2
votes
1 answer

Deadlock on indexed view

I have been pulling my hair on this deadlock: [Enlarge] IX_OrderAmounts is an indexed view doing aggregations on the Amount table. The transaction on the left (Repeatable read - although same happens with Read commited) only inserts a single row in…
Flavien
  • 7,497
  • 10
  • 45
  • 52
2
votes
1 answer

Not sure if I understand what an Indexed View does behind the scenes, in SqlServer

I'm using sql server 2008 and have started to find using Indexed Views are helping me speed up some of my queries ... as my schema isn't basic. So, if i have a table that is the following... **ParentTable ParentId INT PK IDENTITY Name…
Pure.Krome
  • 84,693
  • 113
  • 396
  • 647
2
votes
2 answers

SQL Server Transaction Replication For Indexed Views

I am doing transaction replication for indexed views. I have other replicating schemabound views that reference the indexed views using the NOEXPAND hint. Even though I call sp_addarticle for the NOEXPANDing views after calling sp_addarticle for the…
Jeff
  • 35,755
  • 15
  • 108
  • 220
1 2 3
…
9 10