asked on
SET QUOTED_IDENTIFIER ON
go
SET ARITHABORT ON
go
SET CONCAT_NULL_YIELDS_NULL ON
go
SET ANSI_NULLS ON
go
SET ANSI_PADDING ON
go
SET ANSI_WARNINGS ON
go
SET NUMERIC_ROUNDABORT OFF
go
CREATE VIEW [dbo].[mv_1_6497] WITH SCHEMABINDING
AS
SELECT [dbo].[attachment].[case_id] as _col_1, [dbo].[attachment].[bPurged] as _col_2, count_big(*) as _col_3 FROM [dbo].[attachment] GROUP BY [dbo].[attachment].[case_id], [dbo].[attachment].[bPurged]
go
SET ARITHABORT ON
SET CONCAT_NULL_YIELDS_NULL ON
SET QUOTED_IDENTIFIER ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
SET NUMERIC_ROUNDABORT OFF
go
CREATE UNIQUE CLUSTERED INDEX [IXmv_1_6497_c_31_308208894__K2_K1] ON [dbo].[mv_1_6497]
(
[_col_2] ASC,
[_col_1] ASC
)WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF) ON [PRIMARY]
go
CREATE NONCLUSTERED COLUMNSTORE INDEX
ASKER
Of course, if the view creation represents just a suggestion then you cannot expect the view is used somewhere already.
If SQL optimizer recognizes the schema bound view is equal to the query done on the table then it may use the view instead of grouping and calculating the count again and again. This should be visible in query plan.
columnstore index speeds the COUNT_BIG function up significantly.
So if I found the index make query slow, I can ALSO simply go that dB, and then choose the table, go to index session, right click on the column store index and click delete ?Anyway, it should be possible to drop the indexes and the view when you decide so. This you may test easily on a small testing table.
As a side effect of the view and indexes creation you may observe significant slow down when updating the table
ASKER
This means an update at two places which is surely slower than update at one place only.
ASKER
ASKER
Microsoft SQL Server is a suite of relational database management system (RDBMS) products providing multi-user database access functionality.SQL Server is available in multiple versions, typically identified by release year, and versions are subdivided into editions to distinguish between product functionality. Component services include integration (SSIS), reporting (SSRS), analysis (SSAS), data quality, master data, T-SQL and performance tuning.
TRUSTED BY
You have to look how the underlying table is queried. If there are queries used which are equal or similar to the view definition then the view creation makes sense. If SQL optimizer recognizes the schema bound view is equal to the query done on the table then it may use the view instead of grouping and calculating the count again and again. This should be visible in query plan.
The unique clustered index helps a lot to GROUP BY clause and the columnstore index speeds the COUNT_BIG function up significantly.
I also expect the number of rows in the table is very large otherwise the contribution of the indexed view would be marginal.
As a side effect of the view and indexes creation you may observe significant slow down when updating the table. This is acceptable when the number of queries using the view significantly exceeds the number of table updates.
Anyway, it should be possible to drop the indexes and the view when you decide so. This you may test easily on a small testing table.