research Question

DTAed result shows it suggest to CREATE VIEW xx WITH SCHEMABINDING

Avatar of marrowyung
marrowyung asked on
* SQL Server 2012 EnterpriseSQLMicrosoft SQL Server
7 Comments1 Solution25 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

Open in new window


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

Open in new window


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


ASKER CERTIFIED SOLUTION
Pavel Celba
All in One

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 1 Answer and 7 Comments.
Start Free Trial
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 7 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