Questions tagged [indexed-view]
142 questions
2
votes
1 answer
When indexed view updated
I create a indexed view View1 on Table1 and Table2 and have instead of trigger and after trigger on Table1 that use of View1. It seem that data of View1 not change when I use this view on instead of trigger and after trigger. But I want to use View1…
mehdi lotfi
- 11,194
- 18
- 82
- 128
1
vote
1 answer
Inserts to indexed views
Greetings Overflowers,
Is there an SQL DBMS that allows me to create an indexed view in which I can insert new rows without modifying the original tables of the view? I will need to query this view after performing the in-view-only inserts. If the…
geeko
- 2,649
- 4
- 32
- 59
1
vote
1 answer
Can we use openCV to load and resize 2d image that had indexed colors?
So, I have an image cv::Mat created as an indexed 2D matrix with colors 1,2,3,... up to 255. I want to resize my image all at once but do it like I currently do - individually for each index, so as not to get mixed colors:
//...
std::map
myWallJSON
- 9,110
- 22
- 78
- 149
1
vote
2 answers
SQL Server Indexed View Error
I realize this is a very contrived example, but I've simplified the full version down to the following which demonstrates the problem:
CREATE VIEW model.Appointments_Partition1
WITH SCHEMABINDING AS
SELECT CONVERT(varchar(15), AppointmentId) as…
Jeff
- 35,755
- 15
- 108
- 220
1
vote
2 answers
Index of view isn't used on SQL Server
I want to speed up the access to a view and have put an index over it. Unfortunately this is not used, although it seems logical and obvious to me that this should be used. Can someone tell me what I am doing wrong?
CREATE VIEW dbo.PickingInfoTEST…
André Reichelt
- 1,484
- 18
- 52
1
vote
2 answers
What can cause index corruption in SQL Server indexed view
I have a view with a clustered index on it.
sometimes, when I update Order table (using EF Core that uses transaction)
UPDATE dbo.Order SET OrderStatus = @orderStatus
INSERT INTO dbo.History VALUES (....) //no relationship to orders tabe
I get…
Liero
- 25,216
- 29
- 151
- 297
1
vote
0 answers
How to drop and recreate indexed view without hardcoding the CREATE VIEW definitiotion
I have couple of indexed views (with schema binding and index) in my database.
I want to alter some some columns used by the view, but I get:
The object 'MyIndexedView' is dependent on the column 'MyColumn'.
ALTER TABLE ALTER COLUMN MyColumn…
Liero
- 25,216
- 29
- 151
- 297
1
vote
2 answers
SQL Server - indexed view with string_agg
I try to define an indexed view to create full text search index on it.
The view itself is created correctly:
CREATE OR ALTER VIEW dbo.my_view WITH SCHEMABINDING AS
SELECT p.id as protector_id,
p.name as protector_name,
…
r34
- 320
- 2
- 12
1
vote
2 answers
Need query to determine if any Indexed Views exist in a database
We are in the process of migrating from SQL-Server 2005 Enterprise to SQL-Server 2008 Standard.
I am trying to find a query that can tell me if any INDEXED VIEWS exist in a database (as they won't be supported in SQL-Server 2008 Standard). Nothing…
Chris Baxter
- 16,083
- 9
- 51
- 72
1
vote
2 answers
indexed view update is table scanning 110 million rows for 6 actual rows found out of histogram key bounds
On SQL Server 2017(RTM-CU17) with query optimizer hot fixes on I have an indexed view that is taking a considerable amount of time to update. I am at a loss and cannot figure out why a full table scan is occurring for the update.
The indexed view…
Ben H
- 26
- 4
1
vote
1 answer
How can I create a Spatial Index on an Indexed View?
I have a view which already has SchemaBinding applied AND it has a UNIQUE CLUSTERED index.
Now, I'm trying to add a second index, which is a SPATIAL index ... and I get the following error message:-
And here's the a picture of the schema:
Pure.Krome
- 84,693
- 113
- 396
- 647
1
vote
0 answers
Indexed View w Business Logic
I have been tasked to create several 'views' on data that exists on our analytics server. These views are 'business logic' heavy, but will be referenced by potentially multiple tools, so we are trying to centralize the logic into the sql server.
In…
Andy
- 31
- 4
1
vote
1 answer
Can I use an indexed view in SQL Server to fetch only non duplicate records
We have a huge table with millions of records. We want to fetch only first record based on some key in that table. When I tried using self-join it's resulting in temp db space issue. From the performance point of view, I was thinking of using an…
Icarus
- 415
- 4
- 12
1
vote
1 answer
SQL Server If Else Indexed View
In SQL Server 2012, I would like to run a query that checks, if an indexed view exists. If it does, I want to run a SELECT statement WITH(NOEXPAND). If it doesn't, I would like to run a select statement without expand. Below is the code I am…
Dixie
- 23
- 1
- 4
1
vote
0 answers
SQLServer indexed views cause very slow deletes from table at "top" of dependency tree
Scenario:
SSIS packages loading data. Deletes of sections of data prior to reload taking a very long time. Currently done manually with constraints disabled, data loads done, then constraints re-applied.
This is deleting maybe 100k records from a…
mrmoosehead
- 145
- 1
- 8