[Зв'язок] Огляд розділених таблиць SQL Server

Офіційна документація

Кроки високого рівня для створення таблиці розділів

1. Створіть групу файлів (подібно до oracle tablespace), звісно, її не можна створити, всі розділи можна зібрати в одну групу файлів

2. Встановити функцію розбиття та розподілити дані відповідно до діапазону

3. Встановіть схему розділів, асоціюйте функцію розділу, а також асоціюйте групу файлів, функція розділення розділяє дані на кілька діапазонів, потрібно асоціювати кілька груп файлів, звісно, ви також можете помістити ці діапазони в одну групу файлів

4. Створіть таблицю та асоціюйте схему зонування

Помилка зіткнулася

Коли ви безпосередньо клацаєте правою кнопкою кліку по таблиці для експорту структури таблиці, ви не можете експортувати інформацію про розділ, а експортувати інформацію про розділ таблиці можна лише клацнувши правою кнопкою миші на script database-task-generate

Деякі висновки з таблиці поділу:

1. Розділені поля не обов'язково потребують індексації

2. Розділені поля можуть створюватися як кластеризовані індекси або нокластеризовані індекси

3. Розділені поля, незалежно від того, чи є вони кластеризованими індексами, чи без кластеризації, перебудовуються у кластеризовані індекси без відповідних схем розбиття, і розділена таблиця стає нерозділеною таблицею

4. Перетворіть звичайну таблицю на розділену, просто створіть кластерний індекс у таблиці і використовуйте схему розбиття на кластеризованому індексі. Наприклад, коли поле 1 нерозділеної таблиці створює кластеризований індекс і асоціюється зі схемою розбиття, таблиця перетворюється на розбиту таблицю, і поле є полем 1.

5. Перетворити розділену таблицю на нормальну таблицю, якщо є поле розділу з індексом, перебудувати розділене поле як кластеризоване індекс і не асоціюватися зі схемою розділів; якщо поле розділення не має індексу, поле розділу може створити новий кластеризований індекс і не асоціюватися зі схемою розбиття

6. Змінити нормальну таблицю на таблицю розділів або таблицю розділів на звичайну можна досягти лише за допомогою кластеризованих індексів, оскільки з кластеризованими індексами є таблиці організації індексів, а перерозподіл таблиць здійснюється через реконструкцію кластеризованих індексів. Якщо звичайна таблиця стає розділеною, реконструюйте поле розбиття у кластеризований індекс і асоціюйте його зі схемою розбиття, а якщо таблиця розбиття стає нормальною таблицею — реконструюйте поле розбиття у кластеризований індекс без асоціювання схеми розбиття.

7. Щоб створити таблицю розділів, обмеження на унікальність має містити стовпець розділу

8. При створенні схеми розділів необхідно переконатися, що кількість груп файлів відповідає сегменту діапазону розділів функції розділу, і не має значення, чи є ім'я групи файлів дубльованим.

9. Функції розділення та схеми розбиття знаходяться в базі даних, а не охоплюють всю екземпляр

10. Таблиця розділів занадто велика, щоб займати багато місця на диску, і її розмір не змінюється після видалення деяких полів

Кроки створення таблиці розділів

1.1. Приклад створення групи файлів

alter database test1 додати filegroup part1;

alter database test1 додати filegroup part1000;

alter database test1 додати filegroup part2000;

alter database test1 додати filegroup part3000;

alter database test1 додати файлову групу part4000;

1.2. Встановити приклад файлу та асоціювати групу файлів

ЗМІНИТИ БАЗУ ДАНИХ test1 ДОДАТИ ФАЙЛ(NAME = test1part1,FILENAME = 'G:\test1part1.ndf',SIZE = 5MB,MAXSIZE = 100MB,FILEGROWTH = 5MB) ДО FILEGROUP PART1;

ЗМІНИТИ БАЗУ ДАНИХ test1 ДОДАТИ ФАЙЛ(NAME = test1part1000,FILENAME = 'G:\test1part1000.ndf',SIZE = 5MB,MAXSIZE = 100MB,FILEGROWTH = 5MB) ДО FILEGROUP PART1000;

ALTER DATABASE test1 ДОДАТИ ФАЙЛ(NAME = test1part2000,FILENAME = 'G:\test1part2000.ndf',SIZE = 5MB,MAXSIZE = 100MB,FILEGROWTH = 5MB) ДО FILEGROUP PART2000;

ЗМІНИТИ БАЗУ ДАНИХ test1 ДОДАТИ ФАЙЛ(NAME = test1part3000,FILENAME = 'G:\test1part3000.ndf',SIZE = 5MB,MAXSIZE = 100MB,FILEGROWTH = 5MB) ДО FILEGROUP PART3000;

ЗМІНИТИ БАЗУ ДАНИХ test1 ДОДАТИ ФАЙЛ(NAME = test1part4000,FILENAME = 'G:\test1part4000.ndf',SIZE = 5MB,MAXSIZE = 100MB,FILEGROWTH = 5MB) ДО FILEGROUP PART4000;

2. Приклад встановлення функції розбивання, функція розбиття називається partfun1

CREATE PARTITION FUNCTION partfun1 (int)

ЯК ЛІВИЙ ДІАПАЗОН ДЛЯ ЗНАЧЕНЬ ('1000', '2000', '3000', '4000')

--ЗНАЧЕННЯ ('1000', '2000', '3000', '4000') означає, що таблиця буде поділена на 5 зон, які поділяються за розміром значень у полях таблиці, п'ять зон є найменшими — 1000, 1000-2000, 2000-3000, 3000-4000, 4000-найбільші

3. Приклад створення схеми, асоціюйте функцію поділу partfun1 та асоціюйте групу файлів

CREATE PARTITION SCHEME partschema1

AS PARTITION partfun1

TO (частина 1, частина 1000, частина 2000, частина 3000, частина 4000);

--Побудовано на кількох групах файлів: part1, part1000, part2000, part3000, part4000

CREATE PARTITION SCHEME partschema2

AS PARTITION partfun1

ДО (ЧАСТИНА1,[ПЕРВИННА],[ПЕРВИННА],[ПЕРВИННА],[ПЕРВИННА]);

--Побудований на групі файлів part1, [PRIMARY], замінити частину 1 на [PRIMARY], щоб це було подібно до створення на групі файлів [PRIMARY]

СТВОРИТИ СХЕМУ РОЗДІЛІВ partsscheme3

AS PARTITION partfun1

ALL TO (частина 1);

--все побудовано на групі файлів part1

СТВОРИТИ СХЕМУ РОЗДІЛІВ partschema4

AS PARTITION partfun1

ВСІ ДО ([ПЕРВИННІ]);

--все побудовано на файловій групі [PRIMARY]

4. Приклад створення таблиці розділів

СТВОРИТИ ТАБЛИЦЮ parttable1(

[ID] [int] НЕ NULL,

[IDText] [нварчар] (макс) НУЛЬ

[Дата] [час побачення] NULL)

ON [partschema1](ID);

вставити у значення parttable1 (1,'1',getdate()-4);

вставити у значення parttable1 (1001,'1001',getdate()-3);

вставити у значення parttable1 (2001,'2001',getdate()-2);

вставити у значення parttable1 (3001,'3001',getdate()-1);

вставити у значення parttable1 (4001,'4001',getdate());

5. Перевірити дані таблиці розділів

ВИБРАТИ * З parttable1;

--Повертає всі рядки таблиці розбиття

ВИБЕРІТЬ окремі $PARTITION. [partfun1] (4) З PARTTABLE1;

--Повертає, до якого розбивання рядка з значенням ID поля 4 належить

ВИБРАТИ * З parttable1, де $PARTITION. [partfun1] (ID)=2

--Повертає всі рядки у другому розділі, ID — це ID поля розділу

Примітка: Ви не можете використовувати SELECT * FROM parttable1, де $PARTITION. [partfun1] (ID)=2 має результат, тобто це розділена таблиця, а останній тест 7 цієї статті — нерозділена таблиця, але SELECT * FROM parttable1, де $PARTITION. [partfun1] (ID)=2 все ще має результат

Нові розділи

1. Вкажіть групу файлів, яку можна використовувати для схеми розділів (додайте групу файлів для схеми розділів).

2. Модифікувати функцію розділу (додати діапазон даних функції розділення)

ЗМІНИТИ СХЕМУ РОЗПОДІЛУ partschema1 НАСТУПНЕ ВИКОРИСТАННЯ [ОСНОВНА]

ALTER PARTITION FUNCTION partfun1() РОЗДІЛЕНИЙ ДІАПАЗОН ('4500')

Виберіть p.partition_number,p.рядки з sys.indexes i inner join sys.partitions p on p.object_id = i.object_id та i.object_id = object_id('parttable2') порядком 1

--Перший оператор: якщо схема розділу використовує ALL TO ([PRIMARY]), цей оператор не потрібно виконувати

--До другого оператора додається новий розділ, діапазон становить 4000-4500

--Третє твердження перевіряє, чи існує новий розділ і чи є рядки

Видалити\Об'єднати розділ

ALTER PARTITION FUNCTION partfun1() ДІАПАЗОН ЗЛИТТЯ ('2000')

Розділ 1000-2000 було видалено і об'єднано з 1000-3000

--Неможливо виконати ALTER TABLE TABLENAME DROP PARTITIONNAME PARTITIONNAME як Oracle;

Видаліть таблицю розділів і відповідну групу файлів

Порядок видалення такий: видалити таблицю розділів, видалити схему розділів, видалити функцію розділу, і нарешті видалити групу файлів, і відповідний файл видаляється після видалення групи файлів

Приклад перетворення розділеної таблиці в нормальну таблицю та нормальної таблиці на розділену таблицю

СКИНУТИ ТАБЛИЦЮ1;

СТВОРИТИ ТАБЛИЦЮ parttable1(

[Id] [int] ІДЕНТИЧНІСТЬ(1,1) НЕ NULL,

[Ім'я] [варчар] (16) НЕ НУЛЬ,

[Id2] [інтелект] НЕ НУЛЬ.

) ON partschema1(Id2);

вставити у значення parttable1 ('1',1);

вставити у значення таблиці 1 ('1001',1001);

вставити у значення parttable1 ('2001',2001);

вставити у значення таблиці 1 ('3001',3001);

вставити у значення parttable1 ('4001',4001);

1. Унікальне обмеження, створене на таблиці розділів, має містити стовпець розділу.

ALTER TABLE parttable1 ДОДАТИ ОБМЕЖЕННЯ PK_prattable1_id КЛАСТЕРИЗОВАНИЙ ПЕРВИННИЙ КЛЮЧ ([ID] ASC)

Стовпець помилки 'Id2' — це стовпець розбиття індексу 'PK_prattable1_id'. Стовпці розбиття для унікального індексу мають бути підмножиною індексного ключа.

2. Створіть новий кластеризований індекс у стовпці розділу id2, і parttable1 все одно залишається розділеною таблицею

створити кластеризований індекс CI_prattable1_id2 на parttable1(id2);

3. Стовпець розділу id2 створює некластеризований індекс, і parttable1 все ще є розділеною таблицею

Drop index CI_prattable1_id2 на parttable1;

створити некластеризований індекс NCI_prattable1_id2 на parttable1(id2);

4. Нерозділений стовпець ідентифікатора створює кластеризований індекс, parttable1 або розділену таблицю, що вказує на те, що нерозділений стовпець може бути індексним стовпцем кластера

створити кластеризований індекс CI_prattable1_id на parttable1(id);

5. Стовпець розділу ID2 перебудовується у некластеризований індекс і не використовує схему розбиття, parttable1 все ще є таблицею розділів

створити некластеризований індекс NCI_prattable1_id2 на parttable1(id2) З (DROP_EXISTING = ON) НА [ПЕРВИННІЙ];

6. Стовпець розділу id2 реконструюється у кластеризований індекс без умови ON, і parttable1 все одно залишається таблицею розділів

Drop index CI_prattable1_id на parttable1;

Drop index NCI_prattable1_id2 на parttable1;

створити кластеризований індекс CI_prattable1_id2 на parttable1(id2);

створити кластеризований індекс CI_prattable1_id2 на parttable1(id2) WITH (DROP_EXISTING = ON);

7. Розділений стовпець id2 перебудовується у кластеризований індекс з умовою ON, але без використання схеми розбивання, і parttable1 стає нерозділеною таблицею

створити кластеризований індекс CI_prattable1_id2 на parttable1(id2) з (DROP_EXISTING = ON) НА [ПЕРВИННІЙ];

8. Стовпець розділу id2 перебудовується у кластеризований індекс, і за допомогою схеми розбиття parttable1 стає розділеною таблицею

створити кластеризований індекс CI_prattable1_id2 на parttable1(id2) WITH (DROP_EXISTING = ON) на partschema1(Id2);

9. Після видалення кластеризованого індексу з 8 вище, parttable1 все ще залишається розділеною таблицею

Drop index CI_prattable1_id2 на parttable1;

10. Стовпець розділу ID2 створюється як кластеризований індекс і не використовує схему розбиття, а parttable1 стає нерозділеною таблицею

створити кластеризований індекс CI_prattable1_id2 на parttable1(id2) ON [PRIMARY];

11. Після видалення кластеризованого індексу наведених вище 10, parttable1 все ще залишається нерозділеною таблицею

Drop index CI_prattable1_id2 на parttable1;

12. Стовпець розділу id2 створюється як некластеризований індекс, хоча використовується схема розбиття, вона все одно залишається нерозділеною таблицею

створити некластеризований індекс NCI_prattable1_id2 на parttable1(id2) на partschema1(Id2);

Якщо розбиту таблицю перетворити на звичайну, обмеження первинного ключа видаляється, і індекс кластера перебудовується або перебудовується у первинний ключ для полів оригінального первинного ключа, але він не пов'язаний зі схемою розбиття

ALTER TABLE Table_name DROP CONSTRAINT PK_NAME З (>

СТВОРИТИ КЛАСТЕРИЗОВАНИЙ ІНДЕКС PK_NAME НА Table_name(стовпець) З (НА [ОСНОВНИЙ];

або

ЗМІНИТИ ТАБЛИЦЮ Table_name ДОДАТИ ОБМЕЖЕННЯ PK_NAME ПЕРВИННИЙ КЛЮЧ КЛАСТЕРИЗОВАН(стовпець) З (НА [ОСНОВНИЙ];

Якщо звичайну таблицю перетворюють на розділену таблицю, якщо оригінальний первинний ключ потрібно зберегти, обмеження первинного ключа видаляється, і тоді первинний ключ створюється без встановлення його як агрегованого індексу, після чого створюється новий агрегований індекс, і схема розбиття використовується в агрегованому індексі

ALTER TABLE Table_name DROP CONSTRAINT PK_NAME З (>

ЗМІНИТИ ТАБЛИЦЮ Table_name ДОДАТИ ОБМЕЖЕННЯ PK_NAME ПЕРВИННИЙ КЛЮЧ НЕКЛАСТЕРИЗОВАНИЙ(стовпець) З (НА [ПЕРВИННИЙ];

--Створити первинний ключ, але не встановити як кластеризований індекс

СТВОРИТИ КЛАСТЕРИЗОВАНИЙ ІНДЕКС index_name НА Table_name(стовпець) НА Схемі Розділів (Поле Розділу)

--Створити новий кластеризований індекс, у якому використовується схема розбиття

Запитуйте загальну кількість рядків і розмір розділеної таблиці, наприклад, таблиця — CRM. EmailLog

Виконавчий sp_spaceused 'CRM. EmailLog;

Запитуйте інформацію про розділену таблицю, скільки рядків у кожному розділі, наприклад, таблиця — CRM. EmailLog

Виберіть Convert(Varchar(50), ps.name

) як partition_scheme,

p.partition_number,

Перетворення (Варчар(10), ds2.name

) як групу файлів,

convert(varchar(19), isnull(v.value, ''), 120) як range_boundary,

str(p.rows, 9) як ряди

З sys.indexes I

Приєднати sys.partition_schemes ps на i.data_space_id = ps.data_space_id

Приєднуйтесь до sys.destination_data_spaces dds

on ps.data_space_id = dds.partition_scheme_id

Приєднуйтесь до sys.data_spaces DS2 на dds.data_space_id = ds2.data_space_id

Join sys.partitions p on dds.destination_id = p.partition_number

і p.object_id = i.object_id і p.index_id = i.index_id

Приєднатися sys.partition_functions PF на ps.function_id = pf.function_id

ЛІВОРУЧ ПРИЄДНУЙСЯ до системи. Partition_Range_values v на pf.function_id = v.function_id

і v.boundary_id = p.partition_number - pf.boundary_value_on_right

ДЕ i.object_id = object_id('crm. EmailLog')

та i.index_id в (0, 1)

Порядок за p.partition_number

Функції розбиття запитів

вибрати * з sys.partition_functions

Переглянути архітектуру розділів

Вибрати * з sys.partition_schemes