· 6 years ago · Dec 19, 2019, 01:28 PM
1USE [DynPartitions]
2GO
3/****** Object: UserDefinedFunction [pmf_dynpart_sql].[f_conf_is_partition_unique] Script Date: 19.12.2019 13:43:03 ******/
4SET ANSI_NULLS ON
5GO
6SET QUOTED_IDENTIFIER ON
7GO
8
9create FUNCTION [pmf_dynpart_sql].[f_conf_is_partition_unique]
10(
11 @partition_function sysname
12 ,@current_value sql_variant
13 ,@next_value sql_variant
14)
15RETURNS nvarchar(3)
16AS
17BEGIN
18
19 declare @return nvarchar(10) = 'NO';
20
21 if @partition_function = 'pf_planning_version'
22 begin
23
24 declare @current_pv_date date;
25 declare @next_pv_date date;
26
27 set @current_pv_date = convert(date, convert(char(8), @current_value));
28 set @next_pv_date = convert(date, convert(char(8), @next_value));
29
30 if datediff(day, @current_pv_date, @next_pv_date) = 1
31 set @return = 'YES';
32 end
33
34 return @return;
35END
36GO
37/****** Object: UserDefinedFunction [pmf_dynpart_sql].[f_get_partfunction_id] Script Date: 19.12.2019 13:43:03 ******/
38SET ANSI_NULLS ON
39GO
40SET QUOTED_IDENTIFIER ON
41GO
42
43create FUNCTION [pmf_dynpart_sql].[f_get_partfunction_id]
44(
45 @partition_function sysname
46)
47RETURNS int
48AS
49BEGIN
50 return (
51 select function_id
52 from sys.partition_functions pf
53 where pf.name = @partition_function
54 )
55END
56GO
57/****** Object: UserDefinedFunction [pmf_dynpart_sql].[f_get_partfunction_name] Script Date: 19.12.2019 13:43:03 ******/
58SET ANSI_NULLS ON
59GO
60SET QUOTED_IDENTIFIER ON
61GO
62
63create FUNCTION [pmf_dynpart_sql].[f_get_partfunction_name]
64(
65 @table sysname
66)
67RETURNS sysname
68AS
69BEGIN
70 return (
71 select pf.name
72 from sys.indexes i
73 inner join sys.partition_schemes ps on i.data_space_id = ps.data_space_id
74 inner join sys.partition_functions pf on ps.function_id = pf.function_id
75 where object_id = object_id(@table)
76 and i.type = 1
77 )
78END
79GO
80/****** Object: UserDefinedFunction [pmf_dynpart_sql].[f_get_pc_name_for_table] Script Date: 19.12.2019 13:43:03 ******/
81SET ANSI_NULLS ON
82GO
83SET QUOTED_IDENTIFIER ON
84GO
85
86create FUNCTION [pmf_dynpart_sql].[f_get_pc_name_for_table]
87(
88 @table sysname
89)
90RETURNS sysname
91AS
92BEGIN
93 return (
94 select c.name
95 from sys.index_columns ic
96 inner join sys.columns c
97 on c.object_id = ic.object_id
98 and c.column_id = ic.column_id
99 where ic.object_id = object_id(@table)
100 and ic.partition_ordinal > 0
101 )
102END
103GO
104/****** Object: UserDefinedFunction [pmf_dynpart_sql].[f_part_get_planning_version_ids_dbo_t_fact_example1] Script Date: 19.12.2019 13:43:03 ******/
105SET ANSI_NULLS ON
106GO
107SET QUOTED_IDENTIFIER ON
108GO
109
110CREATE FUNCTION [pmf_dynpart_sql].[f_part_get_planning_version_ids_dbo_t_fact_example1]()
111 RETURNS @pvs table(
112 planning_version_id int
113 )
114 AS
115 BEGIN
116 with rec as (
117 select planning_version_id = min(planning_version_id)
118 from dbo.t_fact_example1
119
120 union all
121
122 select planning_version_id
123 from (
124 select
125 planning_version_id = f.planning_version_id
126 ,rn = row_number() over (order by f.planning_version_id)
127 from dbo.t_fact_example1 f
128 inner join rec on f.planning_version_id > rec.planning_version_id
129 ) a
130 where rn = 1
131 )
132 insert into @pvs (planning_version_id)
133 select
134 planning_version_id
135 from rec
136 option (maxrecursion 0)
137
138 RETURN
139 END
140
141GO
142/****** Object: UserDefinedFunction [pmf_dynpart_sql].[f_part_get_planning_version_ids_dwh_t_fact_example1] Script Date: 19.12.2019 13:43:03 ******/
143SET ANSI_NULLS ON
144GO
145SET QUOTED_IDENTIFIER ON
146GO
147
148create FUNCTION [pmf_dynpart_sql].[f_part_get_planning_version_ids_dwh_t_fact_example1]()
149 RETURNS @pvs table(
150 planning_version_id int
151 )
152 AS
153 BEGIN
154 with rec as (
155 select planning_version_id = min(planning_version_id)
156 from dwh.t_fact_example1
157
158 union all
159
160 select planning_version_id
161 from (
162 select
163 planning_version_id = f.planning_version_id
164 ,rn = row_number() over (order by f.planning_version_id)
165 from dwh.t_fact_example1 f
166 inner join rec on f.planning_version_id > rec.planning_version_id
167 ) a
168 where rn = 1
169 )
170 insert into @pvs (planning_version_id)
171 select
172 planning_version_id
173 from rec
174 option (maxrecursion 0)
175
176 RETURN
177 END
178
179GO
180/****** Object: UserDefinedFunction [pmf_dynpart_sql].[f_part_get_test_ids_dbo_t_fact_example2] Script Date: 19.12.2019 13:43:03 ******/
181SET ANSI_NULLS ON
182GO
183SET QUOTED_IDENTIFIER ON
184GO
185
186CREATE FUNCTION [pmf_dynpart_sql].[f_part_get_test_ids_dbo_t_fact_example2]()
187 RETURNS @pvs table(
188 test_id int
189 )
190 AS
191 BEGIN
192 with rec as (
193 select test_id = min(test_id)
194 from dbo.t_fact_example2
195
196 union all
197
198 select test_id
199 from (
200 select
201 test_id = f.test_id
202 ,rn = row_number() over (order by f.test_id)
203 from dbo.t_fact_example2 f
204 inner join rec on f.test_id > rec.test_id
205 ) a
206 where rn = 1
207 )
208 insert into @pvs (test_id)
209 select
210 test_id
211 from rec
212 option (maxrecursion 0)
213
214 RETURN
215 END
216
217GO
218/****** Object: UserDefinedFunction [pmf_dynpart_sql].[f_part_get_test_ids_dwh_t_fact_example2] Script Date: 19.12.2019 13:43:03 ******/
219SET ANSI_NULLS ON
220GO
221SET QUOTED_IDENTIFIER ON
222GO
223
224create FUNCTION [pmf_dynpart_sql].[f_part_get_test_ids_dwh_t_fact_example2]()
225 RETURNS @pvs table(
226 test_id int
227 )
228 AS
229 BEGIN
230 with rec as (
231 select test_id = min(test_id)
232 from dwh.t_fact_example2
233
234 union all
235
236 select test_id
237 from (
238 select
239 test_id = f.test_id
240 ,rn = row_number() over (order by f.test_id)
241 from dwh.t_fact_example2 f
242 inner join rec on f.test_id > rec.test_id
243 ) a
244 where rn = 1
245 )
246 insert into @pvs (test_id)
247 select
248 test_id
249 from rec
250 option (maxrecursion 0)
251
252 RETURN
253 END
254
255GO
256/****** Object: View [pmf_dynpart_sql].[v_conf_partition_elements_actual] Script Date: 19.12.2019 13:43:03 ******/
257SET ANSI_NULLS ON
258GO
259SET QUOTED_IDENTIFIER ON
260GO
261
262
263
264
265
266
267
268
269CREATE view [pmf_dynpart_sql].[v_conf_partition_elements_actual]
270as
271 select distinct
272 partition_function = 'pf_planning_version'
273 ,table_name = f.t
274 ,element = convert(sql_variant,f.e)
275 from (
276
277 select t = 'dbo.t_fact_example1', e = planning_version_id from pmf_dynpart_sql.f_part_get_planning_version_ids_dbo_t_fact_example1()
278 union all
279 select t = 'dwh.t_fact_example1', e = planning_version_id from pmf_dynpart_sql.f_part_get_planning_version_ids_dwh_t_fact_example1()
280 union all
281 select t = 'dbo.t_fact_example2', e = test_id from pmf_dynpart_sql.f_part_get_test_ids_dbo_t_fact_example2()
282 union all
283 select t = 'dwh.t_fact_example2', e = test_id from pmf_dynpart_sql.f_part_get_test_ids_dwh_t_fact_example2()
284
285 ) f
286
287
288GO
289/****** Object: View [pmf_dynpart_sql].[v_conf_partition_elements_target] Script Date: 19.12.2019 13:43:03 ******/
290SET ANSI_NULLS ON
291GO
292SET QUOTED_IDENTIFIER ON
293GO
294
295
296
297
298CREATE view [pmf_dynpart_sql].[v_conf_partition_elements_target]
299as
300 select
301 partition_function = 'pf_planning_version'
302 ,element = convert(sql_variant,pv.planning_version_id)
303 from dbo.t_dim_planning_version pv
304
305 union
306
307 select
308 partition_function = 'pf_planning_version'
309 ,element = convert(sql_variant,pv.planning_version_id)
310 from dwh.t_dim_planning_version pv
311
312
313
314GO
315/****** Object: View [pmf_dynpart_sql].[v_conf_pivot_elements_target] Script Date: 19.12.2019 13:43:03 ******/
316SET ANSI_NULLS ON
317GO
318SET QUOTED_IDENTIFIER ON
319GO
320
321
322
323CREATE view [pmf_dynpart_sql].[v_conf_pivot_elements_target]
324as
325 select distinct
326 partition_function = 'pf_planning_version'
327 ,element = convert(sql_variant,iif(diff_months < 6, cal.d, cal.m))
328 ,partition_number = $partition.pf_planning_version(iif(diff_months < 6, cal.d, cal.m))
329 ,data_compress
330 from (
331 select
332 [day] = DATEFROMPARTS(LEFT(pv.planning_version_id,4), LEFT(RIGHT(pv.planning_version_id,4),2), RIGHT(pv.planning_version_id,2))
333 ,m = pv.planning_version_id-RIGHT(pv.planning_version_id,2)+1
334 ,d = pv.planning_version_id
335 ,diff_months = datediff(month, DATEFROMPARTS(LEFT(pv.planning_version_id,4), LEFT(RIGHT(pv.planning_version_id,4),2), RIGHT(pv.planning_version_id,2)), dbo.f_current_plandate())
336 ,data_compress = iif(DATEFROMPARTS(LEFT(pv.planning_version_id,4), LEFT(RIGHT(pv.planning_version_id,4),2), RIGHT(pv.planning_version_id,2)) < convert(date,getdate()), 2, 0)
337 from dbo.t_dim_planning_version pv
338
339 union
340
341 select
342 [day] = DATEFROMPARTS(LEFT(pv.planning_version_id,4), LEFT(RIGHT(pv.planning_version_id,4),2), RIGHT(pv.planning_version_id,2))
343 ,m = pv.planning_version_id-RIGHT(pv.planning_version_id,2)+1
344 ,d = pv.planning_version_id
345 ,diff_months = datediff(month, DATEFROMPARTS(LEFT(pv.planning_version_id,4), LEFT(RIGHT(pv.planning_version_id,4),2), RIGHT(pv.planning_version_id,2)), dbo.f_current_plandate())
346 ,data_compress = iif(DATEFROMPARTS(LEFT(pv.planning_version_id,4), LEFT(RIGHT(pv.planning_version_id,4),2), RIGHT(pv.planning_version_id,2)) < convert(date,getdate()), 2, 0)
347 from dwh.t_dim_planning_version pv
348
349 union all
350
351 select
352 [day]
353 ,m = year([day])*10000+month([day])*100+01
354 ,d = year([day])*10000+month([day])*100+day([day])
355 ,diff_months = 0
356 ,data_compress = iif([day] < convert(date,getdate()), 2, 0)
357 from (
358 select [day] = dateadd(day,1,dbo.f_current_plandate())
359 ) next
360
361 union all
362
363 select
364 [day] = 0
365 ,m = 0
366 ,d = 0
367 ,diff_months = 0
368 ,data_compress = 2
369 ) cal
370
371GO
372/****** Object: StoredProcedure [pmf_dynpart_sql].[p_data_delete] Script Date: 19.12.2019 13:43:03 ******/
373SET ANSI_NULLS ON
374GO
375SET QUOTED_IDENTIFIER ON
376GO
377CREATE procedure [pmf_dynpart_sql].[p_data_delete] (
378 @table sysname
379 ,@element sql_variant
380)
381as
382begin
383
384 declare @partition_function sysname = pmf_dynpart_sql.f_get_partfunction_name( @table );
385 declare @function_id int = pmf_dynpart_sql.f_get_partfunction_id( @partition_function );
386
387 declare @current_part_value sql_variant;
388 declare @next_part_value sql_variant;
389
390 declare @sql nvarchar(max);
391
392 set @sql = '
393 select @current_part_value = prv.value, @next_part_value = n.value
394 from sys.partition_range_values prv
395 left join sys.partition_range_values n
396 on prv.function_id = n.function_id
397 and prv.boundary_id = n.boundary_id - 1
398 where prv.function_id = ##function_id##
399 and prv.boundary_id = $partition.##pf##( ##element## ) - 1
400 ';
401
402 set @sql = replace(@sql, '##function_id##', @function_id);
403 set @sql = replace(@sql, '##pf##', @partition_function);
404 set @sql = replace(@sql, '##element##', convert(nvarchar(50),@element));
405
406 exec sp_executesql
407 @stmt = @sql
408 ,@params = N'@current_part_value sql_variant out, @next_part_value sql_variant out'
409 ,@current_part_value= @current_part_value output
410 ,@next_part_value= @next_part_value output
411 ;
412
413 if pmf_dynpart_sql.f_conf_is_partition_unique( @partition_function, @current_part_value, @next_part_value) = 'YES'
414 begin
415 print 'truncate mode';
416 exec pmf_dynpart_sql.p_data_delete_by_switch @element = @element, @table = @table;
417 end
418 else
419 begin
420 print 'delete mode';
421 exec pmf_dynpart_sql.p_data_delete_by_delete @element = @element, @table = @table;
422 end
423
424 exec pmf_dynpart_as.p_part_set_changed @table = @table, @element = @element;
425
426end
427GO
428/****** Object: StoredProcedure [pmf_dynpart_sql].[p_data_delete_by_delete] Script Date: 19.12.2019 13:43:03 ******/
429SET ANSI_NULLS ON
430GO
431SET QUOTED_IDENTIFIER ON
432GO
433CREATE procedure [pmf_dynpart_sql].[p_data_delete_by_delete] (
434 @table sysname,
435 @element sql_variant
436)
437as
438begin
439
440 if object_id( @table, 'U' ) is null
441 begin
442 print 'Table "' + @table + '" not found. Nothing deleted.';
443 return;
444 end
445
446 declare @sql nvarchar(max);
447
448 set @sql = 'delete from ##table## where ##col## = ##element##';
449
450 set @sql = replace(@sql, '##element##', convert(nvarchar(50),@element));
451 set @sql = replace(@sql, '##table##', @table);
452 set @sql = replace(@sql, '##col##', pmf_dynpart_sql.f_get_pc_name_for_table(@table));
453
454 execute( @sql );
455end
456GO
457/****** Object: StoredProcedure [pmf_dynpart_sql].[p_data_delete_by_switch] Script Date: 19.12.2019 13:43:03 ******/
458SET ANSI_NULLS ON
459GO
460SET QUOTED_IDENTIFIER ON
461GO
462CREATE procedure [pmf_dynpart_sql].[p_data_delete_by_switch] (
463 @table sysname
464 ,@element sql_variant
465)
466as
467begin
468
469if object_id( @table, 'U' ) is null
470 begin
471 print 'Table "' + @table + '" not found. Nothing deleted.';
472 return;
473 end
474
475 declare @table_aux sysname;
476 set @table_aux = @table + '_aux';
477
478 if object_id( @table_aux, 'U' ) is null
479 begin
480 print 'Table "' + @table_aux + '" not found. Falling back to delete by delete.';
481 exec pmf_dynpart_sql.p_data_delete_by_delete @element = @element, @table = @table;
482 return;
483 end
484
485 declare @sql nvarchar(max);
486
487 declare @part_nr int;
488 declare @data_compression nvarchar(20);
489
490 exec pmf_dynpart_sql.p_get_partition_info
491 @table = @table
492 ,@element = @element
493 ,@part_nr = @part_nr out
494 ,@data_compression = @data_compression out
495 ;
496
497
498 -- pre sql 2016 syntax (using aux tables)
499 --set @sql = '
500 --begin tran
501
502 --truncate table ##table_aux##;
503
504 --ALTER TABLE ##table_aux## REBUILD WITH (DATA_COMPRESSION = ##data_compress##)
505
506 --alter table ##table## switch partition ##part_nr## to ##table_aux##;
507
508 --truncate table ##table_aux##;
509
510 --commit;
511 --';
512
513 -- new syntax using truncate partition
514 set @sql = '
515 truncate table ##table## with (partitions (##part_nr##));
516 ';
517
518 --set @sql = replace(@sql, '##element##', convert(nvarchar(50),@element));
519 set @sql = replace(@sql, '##table##', @table);
520 --set @sql = replace(@sql, '##table_aux##', @table_aux);
521 set @sql = replace(@sql, '##part_nr##', @part_nr);
522 --set @sql = replace(@sql, '##data_compress##', @data_compression);
523
524 --print(@sql);
525 execute( @sql );
526
527end
528GO
529/****** Object: StoredProcedure [pmf_dynpart_sql].[p_delete_residual_data] Script Date: 19.12.2019 13:43:03 ******/
530SET ANSI_NULLS ON
531GO
532SET QUOTED_IDENTIFIER ON
533GO
534
535
536
537CREATE procedure [pmf_dynpart_sql].[p_delete_residual_data]
538AS
539BEGIN
540
541 declare @table_name sysname;
542 declare @element sql_variant;
543
544 declare c cursor local static forward_only read_only for
545 with elements_to_store as (
546 -- list of elements per partition_function to keep
547 select
548 partition_function
549 ,element = convert(sql_variant, element)
550 from pmf_dynpart_sql.v_conf_partition_elements_target
551
552 ), elements_from_facts as (
553 -- list of used elements in tables
554 select
555 partition_function
556 ,table_name
557 ,element = convert(sql_variant, element)
558 from pmf_dynpart_sql.v_conf_partition_elements_actual
559 where element is not null
560
561 )
562 select
563 --f.partition_function
564 f.table_name
565 ,f.element
566 from elements_from_facts f
567 where not exists (
568 select *
569 from elements_to_store s
570 where f.partition_function = s.partition_function
571 and f.element = s.element
572 )
573
574 open c;
575
576 while 1=1
577 begin
578 fetch next from c into @table_name, @element
579 if @@FETCH_STATUS != 0
580 break
581
582 print 'deleting data for table '+ convert(nvarchar(255),@table_name) + ' and element ' + convert(nvarchar(255),@element);
583 exec pmf_dynpart_sql.p_data_delete @table = @table_name, @element = @element;
584
585 end
586
587 close c;
588 deallocate c;
589
590END
591
592
593
594GO
595/****** Object: StoredProcedure [pmf_dynpart_sql].[p_get_partition_info] Script Date: 19.12.2019 13:43:03 ******/
596SET ANSI_NULLS ON
597GO
598SET QUOTED_IDENTIFIER ON
599GO
600
601
602
603-- exec [pmf_dynpart_sql].[p_get_partition_info] @table = 'dbo.tbl_b_fact_profit_and_loss', @element = 20151026
604CREATE procedure [pmf_dynpart_sql].[p_get_partition_info] (
605 @table sysname
606 ,@element sql_variant
607 ,@part_nr int out
608 ,@data_compression nvarchar(20) out
609)
610as
611begin
612
613 declare @sql nvarchar(max);
614
615 --declare @part_nr int;
616 --declare @data_compression nvarchar(20);
617
618 declare @parameter_definition nvarchar(500);
619
620 SET @sql = N'
621 SELECT @part_nr_OUT = partition_number, @data_compression_OUT = data_compression_desc
622 FROM sys.partitions p
623 inner join sys.tables t on p.object_id = t.object_id
624 inner join sys.schemas s on t.schema_id = s.schema_id
625 where s.name + ''.'' + t.name = ''##table##''
626 and partition_number = $partition.##pf##(##element##)
627 ';
628
629 set @parameter_definition = N'@part_nr_OUT int OUTPUT, @data_compression_OUT nvarchar(20) OUTPUT';
630
631 set @sql = replace(@sql, '##table##', @table);
632 set @sql = replace(@sql, '##element##', convert(nvarchar(50),@element));
633 set @sql = replace(@sql, '##pf##', pmf_dynpart_sql.f_get_partfunction_name( @table ));
634
635 -- 1
636 --insert into table (.....)
637
638
639 execute sp_executesql
640 @sql
641 ,@parameter_definition
642 ,@part_nr_OUT = @part_nr OUTPUT
643 ,@data_compression_OUT = @data_compression OUTPUT;
644
645 --print @part_nr
646 --print @data_compression
647
648end
649
650GO
651/****** Object: StoredProcedure [pmf_dynpart_sql].[p_partfunction_element_add] Script Date: 19.12.2019 13:43:03 ******/
652SET ANSI_NULLS ON
653GO
654SET QUOTED_IDENTIFIER ON
655GO
656CREATE procedure [pmf_dynpart_sql].[p_partfunction_element_add] (
657 @partition_function sysname
658 ,@element sql_variant
659)
660as
661begin
662
663 declare @function_id int = pmf_dynpart_sql.f_get_partfunction_id( @partition_function );
664
665 if exists (
666 select *
667 from sys.partition_range_values prv
668 where prv.function_id = @function_id
669 and prv.value = @element
670 )
671 begin
672 print 'Element already existing. Nothing changed.';
673 return;
674 end
675
676 declare @ps_name sysname;
677
678 select @ps_name = ps.name
679 from sys.partition_schemes ps
680 where ps.function_id = @function_id
681 ;
682
683 declare @sql nvarchar(max);
684
685 set @sql = 'alter partition scheme ' + @ps_name + ' next used [primary];'
686 exec( @sql );
687
688 set @sql = 'alter partition function ' + @partition_function + '() split range( ' + convert(nvarchar(50),@element) + ' );'
689 exec( @sql );
690
691 print 'Added new pivot element ''' + convert(nvarchar(50),@element) + ''' in partition function ' + @partition_function;
692
693 --declare @function_id int = pmf_dynpart_sql.f_get_partfunction_id( @partition_function );
694
695 --if exists (
696 -- select *
697 -- from sys.partition_range_values prv
698 -- where prv.function_id = @function_id
699 -- and prv.value = @element
700 --)
701 --begin
702 -- print 'Element already existing. Nothing changed.';
703 -- return;
704 --end
705
706 --declare @ps_name sysname;
707
708 --select @ps_name = ps.name
709 --from sys.partition_schemes ps
710 --where ps.function_id = @function_id
711 --;
712
713 --declare @sql nvarchar(max);
714
715 --set @sql = 'alter partition scheme ' + @ps_name + ' next used [primary];'
716 --exec( @sql );
717
718 --set @sql = 'alter partition function ' + @partition_function + '() split range( ' + convert(nvarchar(50),@element) + ' );'
719 --exec( @sql );
720
721 --print 'Added new pivot element ''' + convert(nvarchar(50),@element) + ''' in partition function ' + @partition_function;
722
723end
724GO
725/****** Object: StoredProcedure [pmf_dynpart_sql].[p_partfunction_element_remove] Script Date: 19.12.2019 13:43:03 ******/
726SET ANSI_NULLS ON
727GO
728SET QUOTED_IDENTIFIER ON
729GO
730create procedure [pmf_dynpart_sql].[p_partfunction_element_remove] (
731 @partition_function sysname
732 ,@element sql_variant
733)
734as
735begin
736
737 declare @function_id int = pmf_dynpart_sql.f_get_partfunction_id( @partition_function );
738
739 if not exists (
740 select *
741 from sys.partition_range_values prv
742 where prv.function_id = @function_id
743 and prv.value = @element
744 )
745 begin
746 print 'Element not existing. Nothing changed.';
747 return;
748 end
749
750 declare @sql nvarchar(max);
751
752 set @sql = 'alter partition function ' + @partition_function + '() merge range( ' + convert(nvarchar(50),@element) + ' );'
753 exec( @sql );
754
755 print 'Removed pivot element ''' + convert(nvarchar(50),@element) + ''' in partition function ' + @partition_function;
756
757
758end
759GO
760/****** Object: StoredProcedure [pmf_dynpart_sql].[p_prepare_from_dim] Script Date: 19.12.2019 13:43:03 ******/
761SET ANSI_NULLS ON
762GO
763SET QUOTED_IDENTIFIER ON
764GO
765CREATE procedure [pmf_dynpart_sql].[p_prepare_from_dim]
766as
767begin
768
769
770 declare @partition_function sysname;
771 declare @element sql_variant;
772 declare @action char(1);
773
774 declare c cursor local fast_forward for
775 with conf as (
776 select
777 partition_function
778 ,element
779 from pmf_dynpart_sql.v_conf_pivot_elements_target conf
780 ), part as (
781 select
782 partition_function = pf.name
783 ,element = value
784 from sys.partition_range_values prv
785 inner join sys.partition_functions pf on prv.function_id = pf.function_id
786 )
787 select
788 partition_function = isnull(d.partition_function, p.partition_function)
789 ,element = isnull(d.element, p.element)
790 ,act = case when d.element is null then 'R' else 'A' end
791 from conf d
792 full outer join part p
793 on d.element = p.element
794 and d.partition_function = p.partition_function
795 where d.element is null
796 or p.element is null
797
798 open c;
799
800 while 1=1
801 begin
802 fetch next from c into @partition_function, @element, @action;
803 if @@FETCH_STATUS != 0
804 break;
805
806 if @action = 'A'
807 exec pmf_dynpart_sql.p_partfunction_element_add @partition_function = @partition_function, @element = @element;
808 else if @action = 'R'
809 exec pmf_dynpart_sql.p_partfunction_element_remove @partition_function = @partition_function, @element = @element;
810 else
811 print 'Invalid status';
812 end
813
814 close c;
815 deallocate c;
816
817
818
819 -- compression
820
821 declare c cursor local fast_forward for
822 with act as (
823 select
824 table_name = s.name + '.' + t.name
825 ,partition_number = p.partition_number
826 ,data_compress = p.data_compression
827 from sys.partitions p
828 inner join sys.tables t on p.object_id = t.object_id
829 inner join sys.schemas s on t.schema_id = s.schema_id
830 --where t.name = 'tbl_b_fact_profit_and_loss'
831 -- 1-256
832 )
833 , tar as (
834 select *
835 from [pmf_dynpart_sql].[v_conf_pivot_elements_target]
836 )
837 select
838 a.table_name
839 ,a.partition_number
840 ,data_compress =
841 case t.data_compress
842 when 1 then 'ROW'
843 when 2 then 'PAGE'
844 else 'NONE'
845 end
846 from act a
847 inner join tar t on a.partition_number = t.partition_number
848 where a.data_compress != t.data_compress
849
850
851 open c;
852
853 declare @table_name sysname;
854 declare @partition_number int;
855 declare @data_compress nvarchar(20);
856
857
858 while 1=1
859 begin
860 fetch next from c into @table_name, @partition_number, @data_compress;
861 if @@FETCH_STATUS != 0
862 break;
863
864 declare @sql nvarchar(max);
865
866 set @sql = 'ALTER TABLE ##table## REBUILD PARTITION = ##partition_number## WITH (DATA_COMPRESSION = ##data_compress##)';
867
868 set @sql = replace(@sql, '##table##', @table_name);
869 set @sql = replace(@sql, '##partition_number##', @partition_number);
870 set @sql = replace(@sql, '##data_compress##', @data_compress);
871
872 execute( @sql );
873
874 print @sql;
875
876 end
877
878 close c;
879 deallocate c;
880
881
882end
883GO