Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 Секционированная таблица, вспомнить PARTITION SCHEME и PARTITION FUNCTION  [new]
trew
Member

Откуда: Москва
Сообщений: 2646
Может кому пригодиться следующий код:

Секционированная таблица, вспомнить (кто забыл или в незнакомой базе) PARTITION SCHEME и PARTITION FUNCTION
CREATE VIEW [dbo].[PartitionRanges] AS
select pf.name as [partition_function],
       ps.name as [partition_scheme],
       1 as [partition_number],
       case when prv.value is null then NULL else '<' end as [relation],
       prv.value as [boundary_value],
       type_name(pp.system_type_id) as [type],
       fg.name as [filegroup],
       case when ps.name is null then NULL else N'IN USE' end as [status]
  from sys.partition_functions pf
  join sys.partition_parameters pp on pp.function_id = pf.function_id
  left join sys.partition_schemes ps on ps.function_id = pf.function_id
  left join sys.destination_data_spaces dds
    on dds.partition_scheme_id = ps.data_space_id and dds.destination_id = 1 
  left join sys.filegroups fg on fg.data_space_id = dds.data_space_id
  left join sys.partition_range_values prv
    on prv.function_id = pf.function_id and prv.parameter_id = 1 and
       prv.boundary_id = 1
 where pf.boundary_value_on_right = 1
 union all
select pf.name as [partition_function],
       ps.name as [partition_scheme],
       prv.boundary_id + cast(pf.boundary_value_on_right as int) as [partition_number],
       case when pf.boundary_value_on_right = 0 then '<=' else '>=' end as [relation],
       prv.value as [boundary_value],
       type_name(pp.system_type_id) as [type],
       fg.name as [filegroup],
       case when ps.name is null then NULL else N'IN USE' end as [status]
  from sys.partition_functions pf
  join sys.partition_range_values prv on
       prv.function_id = pf.function_id and prv.parameter_id = 1
  join sys.partition_parameters pp on pp.function_id = pf.function_id
  left join sys.partition_schemes ps on ps.function_id = pf.function_id
  left join sys.destination_data_spaces dds
    on dds.partition_scheme_id = ps.data_space_id and
       dds.destination_id = prv.boundary_id + cast(pf.boundary_value_on_right as int)
  left join sys.filegroups fg on fg.data_space_id = dds.data_space_id
 union all
select pf.name as [partition_function],
       ps.name as [partition_scheme],
       pf.fanout as [partition_number],
       case when prv.value is null then NULL else '>' end as [relation],
       prv.value as [boundary_value],
       type_name(pp.system_type_id) as [type],
       fg.name as [filegroup],
       case when ps.name is null then NULL else N'IN USE' end as [status]
  from sys.partition_functions pf
  join sys.partition_parameters pp on pp.function_id = pf.function_id
  left join sys.partition_schemes ps on ps.function_id = pf.function_id
  left join sys.destination_data_spaces dds
    on dds.partition_scheme_id = ps.data_space_id and dds.destination_id = pf.fanout 
  left join sys.filegroups fg on fg.data_space_id = dds.data_space_id
  left join sys.partition_range_values prv
    on prv.function_id = pf.function_id and prv.parameter_id = 1 and
       prv.boundary_id = pf.fanout - 1
 where pf.boundary_value_on_right = 0
 union all
select pf.name as [partition_function],
       ps.name as [partition_scheme],
       NULL, NULL, NULL, NULL,
       fg.name as [filegroup],
       case when dds.destination_id = pf.fanout + 1
            then N'NEXT USED' else N'NOT USED'
       end as [status]
  from sys.partition_functions pf
  join sys.partition_schemes ps on ps.function_id = pf.function_id
  join sys.destination_data_spaces dds
    on dds.partition_scheme_id = ps.data_space_id and
       dds.destination_id > pf.fanout
  join sys.filegroups fg on fg.data_space_id = dds.data_space_id 

GO
SELECT * FROM PartitionRanges


Информация о секционнированной таблице (схема, функция) ниже
SELECT DISTINCT part.object_id as ObjectID,
	OBJECT_NAME(part.object_id) as TableNm,
	idx.name as IndexNm,
	idx.type_desc as IndexType,
	col_name(idx.object_id, ic.column_id) as [partitioning_column],
	psch.name as PartitionScheme,
	pfun.name as FunctionName
FROM sys.partitions part
INNER JOIN sys.indexes idx
ON part.[object_id] = idx.[object_id] and part.index_id = idx.index_id
INNER JOIN sys.data_spaces dsp
ON idx.data_space_id = dsp.data_space_id
INNER JOIN sys.partition_schemes psch
ON dsp.data_space_id = psch.data_space_id
INNER JOIN sys.partition_functions pfun
ON psch.function_id = pfun.function_id
INNER JOIN sys.index_columns ic
    on ic.object_id = idx.object_id and ic.index_id = idx.index_id and
       ic.partition_ordinal > 0 



И когда уже все известно:
/*
Для каждой из содержащих данные секций возвращает информацию о том, 
сколько строк содержится в каждой из секций, а также минимальное и максимальное 
значения поля date50. 
Секция, которая не содержит строк, не попадет в итоговый результат.
*/
SELECT $partition.partfun01(date50) 
                  AS [Partition Number]
      , min(date50) AS [Min Order Date]
      , max(date50) AS [Max Order Date]
      , count(*) AS [Rows In Partition]
FROM dbo.TEST_PART 
GROUP BY $partition.partfun01(date50) 
ORDER BY [Partition Number]

ссылка
24 июл 12, 21:28    [12910837]     Ответить | Цитировать Сообщить модератору
 Re: Секционированная таблица, вспомнить PARTITION SCHEME и PARTITION FUNCTION  [new]
gds
Member

Откуда: Железнодорожный
Сообщений: 1842
Блог
trew,

Еще вариант dbatools.v_partition_scheme_info. По необходимости можно доработать под себя.
24 июл 12, 23:18    [12911098]     Ответить | Цитировать Сообщить модератору
 Re: Секционированная таблица, вспомнить PARTITION SCHEME и PARTITION FUNCTION  [new]
geo_sql
Member

Откуда:
Сообщений: 18
ребят а как узнать которая партиция создалась самой последней ? т.е самая свежая
24 окт 12, 10:34    [13366940]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить