research Question

DTAed result shows it suggest to CREATE VIEW xx WITH SCHEMABINDING

Avatar of marrowyung
marrowyung asked on
Microsoft SQL ServerSQL* SQL Server 2012 Enterprise
6 Comments1 Solution21 ViewsLast Modified:
hi,

I DTA a MS SQL Server database using query store and I see the output is :

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

I knew we need to CREATE VIEW xxxx WITH SCHEMABINDING so that the view can be indexed, but I don't see any reason any code in our DB use this view (but it suggest),

any reason for it?

and it also suggest column store index:

CREATE NONCLUSTERED COLUMNSTORE INDEX

if once we create that index, and it perform even slower, can we just drop that columnstore index to reverse the change?


Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 1 Answer and 6 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 6 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros