Stack Overflow
Stack Overflow

Questions tagged [indexed-views]

20 questions
4
votes
1 answer

Error when creating view - [dbo].[someFunction] is not schema bound

I get this error when creating a view: "Cannot schema bind view 'dbo.viewStock'. 'dbo.GetStockCurrentQuantityByProduct' is not schema bound." BACKGROUND: What I actually want to achieve is improve the speed of a query that retrieves Product Id /…
dopoto
  • 1,124
  • 1
  • 10
  • 20
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
3
votes
1 answer

Session setting to support indexed views

I am using indexed views in my database. So client should have some session connection setting to call them. I am using ado.net connection, command to call stored procedures. Every time I have to call a stored procedure I create connection (I hope…
garik
  • 5,669
  • 5
  • 30
  • 42
2
votes
2 answers

Joining to CTE transformation of table

frequently I encounter a situation like this, where I need to join a big table to a certain transformation of a table. I have made an example with a big table and a smaller prices table. Enter the table CarPrices, which has prices per car…
littlegreen
  • 7,290
  • 9
  • 45
  • 51
2
votes
1 answer

Can I create an Sql Server index that includes a summary of some child rows?

Is it possible in Sql Server 2016 to create some indexed View (or equiv) that summarizes a simple Parent / Child relationship. e.g.: Parent: Vehicles Children: Parts (the parts that make up the car) Children: Workers (the people who helped build…
Pure.Krome
  • 84,693
  • 113
  • 396
  • 647
2
votes
4 answers

Indexed views: How to insert value into other table with index views?

My table: CREATE TABLE [dbo].[Balance] ( [Id] INT IDENTITY (1, 1) NOT NULL, [Balance] DECIMAL (18, 2) NOT NULL, [Today_Date] AS (CONVERT([char](10),getdate(),(126))), [Date_end] …
Bungicasse
  • 145
  • 8
2
votes
1 answer

Basic Event Sourcing in SQL Server using Indexed Views

I'm trying to implement very basic Event Sourcing using a SQL Server. No libraries, no other servers, nothing fancy, just from basics using SQL Server. Assume I have this table: Id | Name | Data | CreatedOn 1 | Fruit | Apple |…
tejas
  • 607
  • 6
  • 11
2
votes
2 answers

SQL Server Indexed Views: Unable to use an expression in the SELECT statement of the view

I am trying to use an expression in the SELECT statement to create an indexed view. According to the MDSN guidelines for Indexed views, expressions can be used if they are deterministic. Unfortunately, when I try to create a unique clustered index…
user2673722
  • 295
  • 2
  • 6
  • 15
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
0 answers

Low maintainence alternatives to indexing a view that can't be indexed in SQL server?

I'm trying to index my views since the data is relatively static and it could increase performance. I cannot index the view because it contains a "ranking or aggregate window function". Is there a workaround for that? SELECT r.Id, r.Value,…
Triynko
  • 18,766
  • 21
  • 107
  • 173
1
vote
1 answer

SQL Server Indexed View Division

Is it possible to perform a division in the SELECT statement of an indexed view? The moment I add a column result which would be one column's value divided by the other, I start getting the below error: Cannot create the clustered index 'CX_t' on…
DenizEng
  • 400
  • 4
  • 14
1
vote
0 answers

Real time table alternative vs swapping table

I use SSMS 2016. I have a view that has a few millions of records. The view is not indexed and should not be as it's being updated (insert, delete, update) every 5 minutes by a job on the server to then display update data sets in to the client…
Data Engineer
  • 795
  • 16
  • 41
1
vote
1 answer

Clustered index key length warning on varchar(120)?

So today i have been doing some optimization creating some indexed views etc. and I came across this warning Warning! The maximum key length for a clustered index is 900 bytes. The index 'IX_...' has maximum length of 8004 bytes. For some…
Mark
  • 1,544
  • 1
  • 14
  • 26
1
vote
0 answers

indexed view vs temp table to improve performance of a seldom executed query

i have a slow query whose structure is select fields from table join manytables join (select fields from tables) as V1 on V1 on V1.field = .... join (select fields1 from othertables) as V2 join (select fields2 from moretables) as V3 The…
UnDiUdin
  • 14,924
  • 39
  • 151
  • 249
1
vote
2 answers

Partitioning Views in sql

How do I partition an Indexed View in MS-SQL ? I have a index view created which stores range of values. The view definition is as follows CREATE VIEW dbo.target_individual_product WITH SCHEMABINDIN AS SELECT day_dim.date_time AS Date, …
Deepak
  • 6,684
  • 18
  • 69
  • 121
1
2