Stack Overflow
Stack Overflow

Questions tagged [indexed-view]

142 questions
132
votes
5 answers

How to create materialized views in SQL Server?

I am going to design a Data Warehouse and I heard about materialized views. Actually I want to create a view and it should update automatically when base tables are changed. Can anyone explain with a query example?
Deepak
  • 6,684
  • 18
  • 69
  • 121
32
votes
4 answers

Is it possible to have an indexed view in MySQL?

I found a posting on the MySQL forums from 2005, but nothing more recent than that. Based on that, it's not possible. But a lot can change in 3-4 years. What I'm looking for is a way to have an index over a view but have the table that is viewed…
Thomas Owens
  • 114,398
  • 98
  • 311
  • 431
27
votes
2 answers

How do you list all the indexed views in SQL Server?

How can you get a list of the views in a SQL server database that have indexes (i.e. indexed views)? I've found it's pretty easy to run an "ALTER VIEW" as I'm developing and overlook that I'm not only editing the view but also dropping an existing…
EBarr
  • 11,826
  • 7
  • 63
  • 85
18
votes
3 answers

How do indexes work on views?

Can someone please explain to me in simple English how an index on a view works? I have a fairly simple understanding of indexes on tables; how would indexing a view work differently from just letting the indexes on the underlying tables do their…
Shaul Behr
  • 36,951
  • 69
  • 249
  • 387
13
votes
7 answers

How can i speed up this Indexed View?

I have a simple Indexed View. When I query against it, it's pretty slow. First I show you the schema's and indexes. Then the simple queries. Finally a query plan screnie. Update: Proof of Solution at the bottom of this post. Schema This is what it…
Pure.Krome
  • 84,693
  • 113
  • 396
  • 647
11
votes
3 answers

Why Query Optimizer totally ignores indexed view indexes?

SQL Fiddle: http://sqlfiddle.com/#!6/d4496/1 (data is pre-generated for your experiments) There is obvious table: CREATE TABLE Entity ( ID int, Classificator1ID int, Classificator2ID int, Classificator3ID int, Classificator4ID int, …
Roman Pokrovskij
  • 9,449
  • 21
  • 87
  • 142
10
votes
2 answers

How do I check the size of an indexed view in SQL Server?

Its easy to check storage sizes for Tables and Indexes, you can right-click the table object on SSMS explorer and voila, the details appear in a nice popup. But since Indexed Views are displayed the same as Normal Views, there is no storage…
Steven de Salas
  • 20,944
  • 9
  • 74
  • 82
10
votes
1 answer

Why do my indexes get dropped when I change an Index'd view's schema?

Server: MS Sql Server 2008 When i create an indexed view .. and i then alter the view's schema, the index's all get dropped. It's sooo annoying! Can someone explain why this is? At first I thought that it could be because the fields the index…
Pure.Krome
  • 84,693
  • 113
  • 396
  • 647
7
votes
1 answer

SQL Server - Creating a Schema Bound Indexed View with a current GetDate filter

I want to create the following indexed view: CREATE VIEW [Cic].[vwMarker] WITH SCHEMABINDING AS Select SubId, marker.EquipmentID, marker.ReadTime, marker.CdsLotOpside, marker.CdsLotBackside, …
Randy Minder
  • 47,200
  • 49
  • 204
  • 358
7
votes
2 answers

Does MySQL view always do full table scan?

I'm trying to optimize a query which uses a view in MySQL 5.1. It seems that even if I select 1 column from the view it always does a full table scan. Is that the expected behaviour? The view is just a SELECT "All Columns From These Tables - NOT *"…
Alex
  • 34,776
  • 10
  • 53
  • 68
7
votes
2 answers

How to use the NOEXPAND hint with Linq to SQL?

I have an indexed view that I need to specify the noexpand hint for in order for it to perform reasonably. Unfortunately as seen with regard to modifying the Linq to SQL generated T-SQL query from the NOLOCK hint it appears that there is no easy way…
jpierson
  • 16,435
  • 14
  • 105
  • 149
6
votes
6 answers

Slowness at Indexed View for SQL 2005

Say I have a very long table (~35 million rows) called TimeCard with only 5 columns (tableID, CompanyID, UserID, ProjectID, DailyHoursWorked, entryDate). This is a pretty straight forward table that records employees' worked hours per day per…
TheYouth
  • 159
  • 1
  • 1
  • 5
6
votes
1 answer

When is an indexed view updated?

I'm using SQL Server 2000, I hesitate to start using indexed views (I've a table with daily performance values, and I need to score them with many mathematical function). If I create an indexed view (using my performance table), and then I add a new…
Fractaliste
  • 5,777
  • 11
  • 42
  • 86
5
votes
4 answers

SQL Server Create View Index which contains distinct or group by

I have a table of address data in my SQL server database. This table is not normalized so it contain many addresses the are repeated. Each unique address can be identified by an Id field (these ids repeat often in the table). So i created a view on…
BBurke
  • 331
  • 1
  • 6
  • 18
5
votes
2 answers

Performance impact of indexed view in MS SQL Server 2008

Does anyone have experience with using indexed view in MS SQL Server 2008? I am trying to find out how does indexed view affect performance of insert / update statements, that are adding / updating rows in tables, which are being used by the indexed…
Tomas
  • 553
  • 4
  • 9
1
2 3
…
9 10