Link to home
Start Free TrialLog in
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?

Avatar of Partha Mandayam
Partha Mandayam
Flag of India image

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])
Avatar of marrowyung
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
Avatar of Partha Mandayam
Partha Mandayam
Flag of India image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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 ?

tks