Stack Overflow
Stack Overflow

Questions tagged [indexed-view]

142 questions
5
votes
3 answers

how clustered index implemented on view

I can create clustered or non-clustered index on view and SQL SERVER says, we can have multiple non-clustered index (max 249), but only one clustered index exist on table or view. Because, records are sorted, where they physically stored and we…
Ravi
  • 30,829
  • 42
  • 119
  • 173
5
votes
3 answers

Indexed view to improve performance of multiple joins on SQL Server

I've a query that performs join on many tables which is resulting in poor performance. To improve the performance, I've created an indexed view and I see a significant improvement in the performance of the query on view with date filter. However, my…
Manish Mulani
  • 7,125
  • 9
  • 43
  • 45
5
votes
3 answers

Do Indexed Views Update During a Transaction?

Let's say I've got a SQL Server Indexed View, vwIndexedView, that reads from two tables, tbTableOne, and tbTableTwo. If I perform an action on one of the two tables inside a transaction, what happens to my indexed view? Is it refreshed immediately,…
Peder Rice
  • 1,764
  • 3
  • 28
  • 51
4
votes
2 answers

SQL Server ISDATE In Indexed View

I have a indexed view where I basically need to do this SELECT ... CASE WHEN ISDATE(ColumnName) = 1 THEN CONVERT(datetime, ColumnName, 103) ELSE NULL END AS ViewColumn .... Trying to create the index yields: Cannot…
Jeff
  • 35,755
  • 15
  • 108
  • 220
4
votes
1 answer

Index is not getting applied on Indexed View

I have an indexed view but when I run queries on that view the index which is built on View is not applied and the query runs without index. Below is my dummy script: Tables + View+ Index on View CREATE TABLE P_Test ( [PID] INT…
Rocky Singh
  • 15,128
  • 29
  • 99
  • 146
4
votes
3 answers

How to create indexed view of children count

I am trying to take a table with a parent child relationship and get the number of children. I would like to create an indexed view of the number of children by utilizing COUNT_BIG(*). The problem is that in my index view I don't want to eliminate…
ParoX
  • 5,685
  • 23
  • 81
  • 152
4
votes
1 answer

How is BLOB stored in an indexed view?

The Question Assuming I make an indexed view on a table containing a varbinary(max) column, will the binary content be physically copied into the indexed view's B-Tree, or will the original fields just be "referenced" somehow, without physically…
Branko Dimitrijevic
  • 50,809
  • 10
  • 93
  • 167
4
votes
5 answers

In SQL Server, when should I use an indexed view instead of a real table?

I know in SQL Server you can create indexes on a view, then the view saves the data from the underlying table. Then you can query the view. But, why I need to use view instead of table?
Just a learner
  • 26,690
  • 50
  • 155
  • 234
4
votes
4 answers

Does Indexing a View in Sql Server 2008 actually duplicate the original data?

If i create an Indexed View (in Sql Server 2008), does this mean i copy all required the data from the source tables into a separate new table? Or are only some tiny pointers/indexes saved, to represent this view?
Pure.Krome
  • 84,693
  • 113
  • 396
  • 647
4
votes
2 answers

How to create Indexed Views with entity framework code-first approach

I have a 3 tables which need to be joined for doing some queries. The tables are relatively read-only for specific duration of time. Only if there is need, we need to write them. I want to avoid join on demand for these tables. So I was planning to…
arangari
  • 61
  • 1
  • 4
4
votes
4 answers

SQL Server Indexed Views: Cannot create clustered index because the select list contains an expression on result of aggregate function

I am trying to create a simple indexed view on the query below. But when I try to create a unique clustered index on it, I get the following error: Cannot create the clustered index '..' on view '..' because the select list of the view contains…
user2673722
  • 295
  • 2
  • 6
  • 15
4
votes
2 answers

Sql server indexed view

OK, I'm confused about sql server indexed views(using 2008) I've got an indexed view called AssignmentDetail when I look at the execution plan for select * from AssignmentDetail it shows the execution plan of all the underlying indexes of all the…
Jose
  • 10,891
  • 19
  • 67
  • 89
4
votes
1 answer

How to avoid this subquery and create an indexed view?

I work with SQL Server 2008. I have this following table "Forecast". Forecast_ID | Budget_Code | IAM_ID | Forecast | Timestamp 1 | 00-0001 | 24 | 123.41 | '01-01'2010' 2 | 00-0001 | 10 | 111.41 |…
Alex
  • 2,927
  • 8
  • 37
  • 56
4
votes
1 answer

Consequences of Indexing the rowversion/timestamp column on SQL Server

Related to my prior question about having a sequence without interim holes (a guarantee that the numbers that are visible to readers are always incrementing) enter link description here I'd like to ask if a solution I devised makes sense. I created…
Szymon Pobiega
  • 3,358
  • 17
  • 18
4
votes
1 answer

SQL Server Performance and Indexed View

Using SQL Server 2008. (Sorry if this turns out to be an article but I'm trying to give as much info as possible.) I have multiple locations which each contain multiple departments which each contain multiple Items which can have zero to many scans.…
Frank
  • 105
  • 3
  • 9
1
2
3
…
9 10