Stack Overflow
Stack Overflow

Questions tagged [indexed-view]

142 questions
1
vote
1 answer

nonclustered index on view just created, but get warning columns miss statistics

obviously there is something I do not understand about index statistics. I have a table with millions of rows for logging purposes. To improve performance I created a view and a unique clustered index on that (had to because first index must be…
DrCopyPaste
  • 4,023
  • 1
  • 22
  • 57
1
vote
1 answer

Error creating indexed view

I have problem with a counting column in my view. SELECT ColumnC, ColumnA % ColumnB AS ModuloColAColB, COUNT_BIG(*) AS cBig FROM dbo.T1 GROUP BY ColumnC, ModuloColAColB Query is similar to this MSDN…
itdeveloper
  • 339
  • 2
  • 3
  • 14
1
vote
3 answers

Doing large updates against indexed view

We have an indexed view that runs across three large tables. Two of these tables (A & B) are constantly getting updated with user transactions and the other table (C) contains data product info that is needs to be updated once a week. This product…
user217136
  • 11
  • 2
1
vote
1 answer

Are these the correct Indexes for an Indexed View for Leaderboards Grouped By Hour/Day/Month?

Here's and example of a script which creates an Indexed View Grouped By the Hour. I also have 2 others for Day and Month. The table UserPoints stores a record whenever points are awarded to the User with an exact DateTime timestamp called…
Vyrotek
  • 5,356
  • 5
  • 45
  • 70
1
vote
1 answer

Reliability of Indexed views

I just found out that a report I quickly threw together years ago has been the sole means of collecting millions of dollars, and there isn't anything being done to check if it is correct. For performance reasons, the report makes heavy use of…
Brad
  • 1,360
  • 4
  • 18
  • 27
1
vote
1 answer

Indexed views, with (NOEXPAND), and SSAS cubes

I've set up an indexed view which I intend to use as a data source view for a SSAS cube. The indexed view looks something like this: create view vw_indexed1 with schemabinding as select key1, key2, count_big(*) as bigcount from table1 group…
Karl
  • 5,573
  • 8
  • 50
  • 73
1
vote
2 answers

Will using an indexed view improve performance of SELECT COUNT queries?

I have a table with that will grow to several million rows over some years. As part of my web application, I have to query the count on a subset of this table whenever a user accesses a particular page. Someone with an architecty hat has said that…
Steve Campbell
  • 3,385
  • 1
  • 31
  • 43
1
vote
1 answer

Will updating any field cause updating indexed view?

Here is a description in MSDN of the indexed view："As modifications are made to the data in the base tables, the data modifications are reflected in the data stored in the indexed view". But i'm confused now, will updating any field in base table…
yman
  • 3,385
  • 3
  • 15
  • 9
1
vote
1 answer

MS-SQL 2005 full-text index on a view with outer joins

I have a view that I'd like to apply a full text index on. It of course needs a unique clustered index to allow for the full text index. Unforunately, I learned that you cannot create an indexed view that contains outer joins. Problem is that my…
ScottE
  • 21,530
  • 18
  • 94
  • 131
1
vote
2 answers

Is it possible to create an indexed view with SQL Server 2008 which selects from another indexed view?

Is it possible to create an indexed view with SQL Server 2008 which selects from another indexed view? create view V1 as (select 1 as abc) create view V2 as (select abc from V1 group by abc)
usr
  • 168,620
  • 35
  • 240
  • 369
0
votes
4 answers

alternate to indexed views

Whats the alternate approach to indexed views in sql server? Thanks, Salman Shehbaz.
salman
0
votes
1 answer

Index on view with 3 joined table is not working

I have Microsoft SQL Server 2019. I want to create a view that includes 3 tables, then create an index on it to use it in query. When I do this on 2 tables, all works fine, but with 3 tables in the view, it does not work (all created correctly, but…
Денис Дудко
  • 3
  • 1
0
votes
2 answers

How do I Index this View Properly?

I have a table similar to this: CREATE TABLE dbo.SomeTable (Work_ID VARCHAR(9)); I need to be able to run the following query and unfortunately can not change the data type of the Work_ID column: SELECT Work_ID FROM dbo.SomeTable WHERE WorkID >=…
TheSQLGuy
  • 1
  • 1
0
votes
0 answers

Indexed view on hierarchical data

I have some tree-like data that is implemented using hierarchyid. Some nodes in that tree can be "locked" and when that happens, all the subnodes of the locked nodes become locked too. Whether the node is locked or not is calculated with a…
torvin
  • 6,515
  • 1
  • 37
  • 52
0
votes
1 answer

Greatest value in group solution that also meets requirements for indexed view

In an Azure SQL database, I have an EAV-style table AttributeValues that stores multiple attribute values, like this: Id Organization_Id Attribute_Id Value 1 1 1 Old Org 1 Description 2 1 1 New Org 1 Description 3 2 1 Old Org 2…
coryseaman
  • 377
  • 4
  • 16
1 2 3
…
9 10