Dodanie 2 grup plików dla bazy danych. Stworzenie funkcji partycjonowania i obsługa na nowej tabeli.
use test1
alter database test1 add filegroup FG_NumberPos
alter database test1 add filegroup FG_NumberNeg
alter database test1
add file (name = 'test1_FG_NPos', filename = 'T:\Program Files\Microsoft SQL Server\MSSQL16.EX1\MSSQL\DATA\test1_FG_NPos.ndf')
to filegroup FG_NumberPos
alter database test1
add file (name = 'test1_FG_NNeg', filename = 'T:\Program Files\Microsoft SQL Server\MSSQL16.EX1\MSSQL\DATA\test1_FG_NNeg.ndf')
to filegroup FG_NumberNeg
--funkcja partycjonowania
create partition function PF_RangeByNumber(integer)
as range right for values (0)
-- schemat partycjonowania
create partition scheme PS_Numbers as partition PF_RangeByNumber to (FG_NumberNeg,FG_NumberPos)
-- tabela oparta o schemat
drop table if exists test1
create table test1 (number int) on PS_Numbers(number)
--wrzut danych
declare @i int = 1
while @i < 100000
begin
insert into test1 (number)
select @i as number
set @i = @i+1
end
set @i = -100000
while @i < 0
begin
insert into test1 (number)
select @i as number
set @i = @i+1
end
-- podglad danych w partycjach
select
p.partition_id,
p.object_id,
p.partition_number,
p.rows,
o.type_desc,
o.modify_date
from
sys.partitions as p
join sys.objects as o
on o.object_id = p.object_id
where o.[name] = 'test1'