Avatar of marrowyung
marrowyung
 asked on

after using MS SQL server DTA to scan a user database.

hi,

after DTA today I see a lot like this:

CREATE PARTITION FUNCTION [pf__114](int) AS RANGE LEFT FOR VALUES (0, 3, 4, 5, 6, 10, 15, 19)go
CREATE PARTITION SCHEME [_ps__5543] AS PARTITION [pf__114] TO ([PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY])
go
CREATE VIEW [dbo].[_mv_1] 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]  

CREATE NONCLUSTERED INDEX [IX_cases_owned_office_id_ssnHubLocation_currentLocation_ref_barcode] ON [dbo].[cases](
   [shipByDate] ASC,
   [id] ASC,
.
. WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF) ON [_ps__5543]([production_line_stage])

is that mean this index [IX_cases_owned_office_id_ssnHubLocation_currentLocation_ref_barcode] already creating on the data partition?

and if I don't want the index to be created on that partition, I just remove ON [_ps__5543]([production_line_stage]) ?where [_ps__5543] is referring to one of the data partition?

DatabasesMicrosoft SQL Server

Avatar of undefined
Last Comment
marrowyung

8/22/2022 - Mon
Partha Mandayam

If you don't want index then remove this statement

    CREATE NONCLUSTERED INDEX [IX_cases_owned_office_id_ssnHubLocation_currentLocation_ref_barcode] ON [dbo].[cases](   [shipByDate] ASC,
       [id] ASC,

.
. WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF) ON [_ps__5543]([production_line_stage])
marrowyung

ASKER
hi

I want index, but just don't want to create the index on top of the partition. so I change:

ON [_ps__5543]([production_line_stage])

to

ON PRIMARY ?
ASKER CERTIFIED SOLUTION
Partha Mandayam

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
marrowyung

ASKER
CREATE VIEW [dbo].[_mv_1] 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]  


why we need a view on partition ?

This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
marrowyung

ASKER
tks