Stack Overflow
Stack Overflow

Questions tagged [indexed-view]

142 questions
0
votes
0 answers

SQL Server: Materialized view based on stored proc with dynamic sql - how to

My client wants a pivot table, showing the performance of each month (column headers) per department (row headers). It has to be be possible to insert a 'as-of date' as a parameter, so the user (PHP) can pass that date and the pivot only shows…
Paul de Roos
  • 47
  • 5
0
votes
2 answers

Is it possible to create an indexed view from Xml Data in Sql Server 2008?

I see from the 2005 documentation that you cannot create an indexed view from an Xml column. Is this possible in 2008 or 2008R2? I can't find any documentation saying that it is something that was added but am looking for confirmation and I don't…
dkackman
  • 15,179
  • 13
  • 69
  • 123
0
votes
1 answer

Multiple composite index vs single non-clustered column store index

We have a OLTP system and we have got a grid, which is containing close to 20 columns, coming from multiple tables. The grid loaded based on search parameters involving around 6 columns. The data is huge with 100M rows coming from background…
Venkataraman R
  • 12,181
  • 2
  • 31
  • 58
0
votes
1 answer

Performance issues with inserting records into a table used by indexed views?

I have a fairly big table, let's call that 2B records split evenly between 200 partitions (SQL Server 2012). Every day, I have a process that takes 6 hours to create a new partition and insert 10 million rows. I think indexed views could improve the…
user3339793
  • 3
  • 3
0
votes
1 answer

Updating underlying tables of indexed view but the column is not present in the view

Let's say I have two tables, Country and City. Country(id, fname, president_name) City(id, cname, country_id_fk, mayor_name) City table has a foreign key dependency on the Country table. Let's say I create a indexed view over tables like…
JavaLearner
  • 527
  • 1
  • 5
  • 16
0
votes
2 answers

Use Sum on extra aggregation and Make Total

I want to create an indexed View. I have this query: SELECT srvmn.BBC_ID AS Inventory, srvInv.ObjectID, srv.State, SUM( IIF(srvInv.Direction = srvInv.ReverseBroken, 1, 0)) AS Broken, SUM(IIF(srvInv.Direction = 0, 1,…
Massoud Beygi
  • 5
  • 3
0
votes
1 answer

Using indexed views to encapsulate the complexity of larger queries is ok for performance?

i am creating a complex querying system based on a "framework of views". In this way writing high level queries is very easy. Currently the performance is bad (comparing to what I could achieve by not using views), but using indexed views is a…
UnDiUdin
  • 14,924
  • 39
  • 151
  • 249
0
votes
0 answers

Add an index in view if it is simply a simple select on one table. No joins

I know this is DBA 101, but the question was asked and I have no answer ... well I guess what the answer is but need to confirm. I have a view (View1) that is a simple select of one table: Select col1, col2 .... from table1 where col2=0. Table1 is…
DJElie
  • 1
0
votes
1 answer

SQL: What is the best way to display (near) realtime site activity over a time interval? i.e. "Messages sent in the last hour"

The goal is to update the UI as frequently as possible but performance is a concern and hits to the database should be minimised. I used Indexed Views to keep up to date stats on items that were not for a specific time interval however this in not…
IntoTheBlue
  • 199
  • 1
  • 3
  • 10
0
votes
1 answer

Makes it sense to create an indexed view when the data is frequently changed?

I have round about 5 tables which each have more than 1,000,000 datasets inside of them. I`ve read about indexed-views and that they can increase the performance at continuous querys. But the tables/the data are continuous updated respectively the…
Robert Wolf
  • 191
  • 1
  • 11
0
votes
1 answer

Not able to create index on schema binding view

Not able to create index on below schema binding view.It is created from another view (v_prod_manu_sub).It is showing below error message: Cannot create index on view "dbo.V_PROD_MANU" because it references derived table "X" (defined by SELECT…
Ram
  • 727
  • 2
  • 16
  • 33
0
votes
0 answers

Cannot create Index Views

I have read in forums that index views can speed up the performance since it stores the table. How can I modify my below query to use NewDate as a unique index? I also know there are some limitations with respect to index queries. Can someone guide…
raghav
  • 45
  • 1
  • 1
  • 9
0
votes
1 answer

Indexed View vs. Table

I had a long running query (relative to the data) that was hitting an indexed view. I thought an indexed view was physically stored data? I dumped everything into a table and ran the same query against the table and found that it executed almost 3x…
JMG
  • 55
  • 7
0
votes
0 answers

Multiple values in single column as an indexed view

I have such problem. My data is like: Numers(Id) Letters(Id, NumberId) If I do Join query, I get output like that: SELECT N.Id, L.Id FROM Numbers N JOIN Letters L ON N.Id = L.NumberId And the output is 1 | a 1 | b 2 | a 2 | b 2 | c But I would…
Miłosz Wieczorek
  • 581
  • 3
  • 11
0
votes
1 answer

Replace view with a trigger

I have a view which I was hoping to create an index over which is failing because I would need to index a computed column (this fails with error code 2729 "...cannot be used in an index or statistics or as a partition key because it is…
Star
  • 283
  • 1
  • 4
  • 5
1 2 3
…
9 10