Questions tagged [indexed-view]
142 questions
3
votes
3 answers
Indexed view in Sql Server 2005 Error
I tried to add an index on a view in Sql Server 2005 an I got this error: "Cannot create index on view 'AllAssignmentNotes' because the view is not schema bound."
I didn't want to put too much information here as it might be overload. Just wondering…
Jose
- 10,891
- 19
- 67
- 89
3
votes
1 answer
Create index on a view with "Latest record of group" select
I'm trying to improve performance of my views using indexed views, however I'm not sure if I can overcome the limitations.
In my view I'm trying to select latest records for a particular group. I'm able to write such query either using inner select…
Liero
- 25,216
- 29
- 151
- 297
3
votes
1 answer
Error adding an index to a view
I have created a view using the following code
CREATE VIEW dbo.two_weeks_performance WITH SCHEMABINDING
AS
SELECT dbo.day_dim.date_time AS Date,
dbo.order_dim.quantity AS Target_Acheived
FROM dbo.day_dim
JOIN dbo.order_fact ON…
Deepak
- 6,684
- 18
- 69
- 121
3
votes
1 answer
Indexed view with datepart, query on datepart is not using the index
I need to do a query by datepart(day, BornDate) and/or month, on a table that has several million rows, which is vey CPU intenstive.
I tried using indexed view with the datepart columns, even creating nonclustered index on the datepart(day,…
Michal Řehout
- 123
- 11
3
votes
1 answer
Indexed view with data for the last two weeks
I'm trying to create indexed view containing only the data for the last 2 weeks.
This part works fine:
CREATE VIEW [dbo].[MainLogView]
WITH SCHEMABINDING
AS
SELECT Id, Date, System, [Function], StartTime, EndTime, Duration, ResponseIsSuccess,…
BWA
- 5,672
- 7
- 34
- 45
3
votes
1 answer
Deterministic function for getting today's date
I am trying to create an indexed view using the following code (so that I can publish it to replication it as a table):
CREATE VIEW lc.vw_dates
WITH SCHEMABINDING
AS
SELECT DATEADD(day, DATEDIFF(day, 0, GETDATE()), number) AS SettingDate
FROM…
bbailes
- 371
- 1
- 9
3
votes
1 answer
SQL Server indexed view matching of views with joins not working
Does anyone have experience of when SQL Server 2008 R2 is able to automatically match indexed view (also known as materialized views) that contain joins to a query?
For example the view
select dbo.Orders.Date, dbo.OrderDetails.ProductID
from…
usr
- 168,620
- 35
- 240
- 369
3
votes
2 answers
How to formulate a SQL Server indexed view that aggregates distinct values?
I have a schema that includes tables like the following (pseudo schema):
TABLE ItemCollection {
ItemCollectionId
...etc...
}
TABLE Item {
ItemId,
ItemCollectionId,
ContributorId
}
I need to aggregate the number of distinct…
jlew
- 10,491
- 1
- 35
- 58
3
votes
1 answer
Alternative to replace Union and subqueries to create Indexed view
When I go to run this query on SQL Server to create an indexed view, an error occurs to remove subqueries and Union
CREATE VIEW [dbo].[view_ToolGroup]
WITH SCHEMABINDING
AS
SELECT
toolGroup.ToolGroupId,toolGroupToTool.ToolId,…
Prak
- 815
- 7
- 18
2
votes
1 answer
SQL Server Indexes INSERT/UPDATE Performance
I know that indexes hurt insert/update performance, but I'm trying to troubleshoot and determine the right balance between query performance and insert/update performance.
We've created a number of views (about 20) for some really really complicated…
Jeff
- 35,755
- 15
- 108
- 220
2
votes
1 answer
SQL Server: Count the number of times the ID from table A occurs in table B without using outer joins
I asked here about counting the number of times a value from table a occurs in table b, but after it was solved, I realized I couldn't use a left join (or right or outer), since I'm going to use the results in an indexed view, which doesn't allow…
Petter Brodin
- 2,069
- 4
- 21
- 27
2
votes
1 answer
SQL Server Indexed View Column Precision
I'm trying to create an indexed view and get the following error creating the index:
Cannot create index on view
....'
because column 'Amount' that is referenced by the view in the
WHERE or GROUP BY clause is imprecise. Consider eliminating…
Jeff
- 35,755
- 15
- 108
- 220
2
votes
1 answer
Using SQL Server Indexed View in combination with OPENJSON
I have a table with just one row and one column which stores a JSON array with about 30MB/16k objects in it:
CREATE TABLE [dbo].[CitiesTable]
(
[CitiesJson] [NVARCHAR](MAX) NOT NULL
) ON [PRIMARY]
GO
INSERT INTO [dbo].[CitiesTable]…
Jan Köhler
- 5,817
- 5
- 26
- 35
2
votes
1 answer
Do SQL Server View Indexes perform correctly if called from a stored procedure?
The ORM we are using (LLBL) does not easily support the NOEXPAND keyword on selecting from SQL Server Indexed Views, which, as I understand it, pretty much negates the purpose of the Indexed View.
I normally steer clear from Stored Procedures for a…
johnc
- 39,385
- 37
- 101
- 139
2
votes
0 answers
Updating an indexed view with NOEXPAND
Suppose I have a table T, and I have an indexed view V on it:
CREATE TABLE dbo.T (id int PRIMARY KEY, b bit NOT NULL, txt varchar(20));
GO
CREATE VIEW dbo.V
WITH SCHEMABINDING AS
SELECT T.Id, T.txt
FROM dbo.T AS T
WHERE T.b = 1;
GO
CREATE…
Charlieface
- 52,284
- 6
- 19
- 43