Створення індексів і ключів у системі SQL-сервер

 

Індекси являють собою наборы унікальних значень для деякої таблиці з відповідними посиланнями на дані, розташовані в самій таблиці. Індекси є зручним внутрішнім механізмом системи SQL-сервера, за допомогою якого здійснюється доступ до даних найбільш оптимальним способом.

Для створення індексів визначеної таблиці бази даних SQL-сервера можна скористатися одним з наступних способів:

• створити індекс за допомогою SQL-команди CREATE INDEX;

• скористатися можливостями утиліти SQL Server Enterprise Manager.

Розглянемо подробней другий спосіб створення індексів. Початковим етапом створення індексу є вибір необхідної бази даних і таблиці, для якої він буде визначатися. Виконання команди. All Tasks / Manage Indexes меню Action відобразить на екрані діалогове вікно керування індексами баз даних. Варто звернути увагу на списки даного діалогового, що випадають, вікна Databases і Table, що дозволяють переміщатися між базами даних і їхня таблиця. При тім у списку Existing indexes відображаються наявні індекси для обраних таблиць баз даних.

 

 

Рисунок 8 - Діалогове вікно керування індексами таблиці

 

У нижній частині даного діалогового вікна располоя керуючі кнопки, що виконують наступні дії:

New - створення нового індексу для обраної таблиці даних;

Edit - редагування параметрів існуючого індексу;

Delete - видалення попереднє обраного індексу;

Close - закриття діалогового вікна;

Help - одержання довідкової інформації з даного питання.

Отже, для створення нового індексу варто скористатися кнопкою New даного діалогового вікна. Ця дія приведе до відкриття іншого діалогового вікна Create New Index, за допомогою якого і встановлюються параметри індексу (див. мал. 3.30).

У поле Index name даного діалогового вікна необхідно ввести ім'я створюваного індексу, після чого визначити перелік полів, що беруть участь в індексі, у представленому списку. Для додавання визначеного полючи в індекс варто установити прапорець ліворуч від його імені. Тут також можна переглянути наступну інформацію про поле: Column - ім'я полючи, Data type - тип даних, Length - розмір, Nullable - можливість використання NULL-значень, Precision - точність і Scale - порядок значень, що вводяться.

 

Рис3.30. Діалогове вікно створення індексу

 

Група опцій Index options дозволяє настроїти додаткові параметри створюваного індексу:

Unique values - при необхідності введення у визначене поле тільки унікальних значень, варто установити дану опцію. Це дозволить здійснювати автоматичну перевірку унікальності при кожнім додаванні нового запису. Якщо буде почата спроба уведення вже наявного значення в записі даного полючи, то системою буде видане повідомлення про помилку. При цьому варто звернути увагу на заборону присутності NULL-значень у цьому полі. При використанні NULL-значень і установці даної опції можуть виникнути помилки. Тому рекомендується установити обов'язкове введення значень у поле, для якого планується створення унікального індексу;

Clustered index - у системі SQL-сервер є можливість фізичного індексування даних. Іншими словами, використання індексів приводить до створення окремої структури, що зв'язується з фізичним розташуванням даних у таблиці. Використання цієї опції дозволяє зробити так називане кластерное індексування, у результаті чого будуть відсортовані дані в самій таблиці відповідно до порядку цього індексу, і вся інформація, що додається, буде приводити до зміни фізичного порядку даних. При цьому потрібно враховувати, що в таблиці може бути визначений тільки один кластерный індекс;

Ignore duplicate values — вибір даної опції приводить до ігнорування введення повторюваних значень у проіндексованих полях. Використання даної опції разом з Unique values дозволяє ігнорувати унікальність значень для полючи. Варто звертатися з цією опцією особливо акуратно роботі з многотабличными структурами і зв'язками між ними. Звичайне використання даної опції має значення при орієнтації на розподіл даних у розроблювальних структурах;

Do not recompute statistics (not recommended) - установка опції визначає функцію автоматичного відновлення статистики для таблиці. Не рекомендується установка даної опції;

File group - за допомогою даної опції можна здійснити вибір файлової групи, у якій буде знаходитися створюваний індекс. Використання індексу з іншої файлової групи підвищує продуктивність некластерных індексів у зв'язку з паралельністю виконання процесів уведення/висновку і роботи із самим індексом. При виборі даної опції активізується список файлових груп, що дозволяє визначити необхідну групу для розміщення індексу;

Fill factor - дана можливість використовується вкрай рідко. За допомогою цієї опції здійснюється настроювання розбивки індексу на сторінки. Однак використання даної можливості може помітно оптимизировать роботу SQL-сервера. Коефіцієнт FILLFACTOR визначає у відсотком співвідношенні розмір створюваних індексних сторінок. При цьому є зворотна пропорційна залежність частоти роботи з таблицею і коефіцієнта FILLFACTOR. Іншими словами, якщо планується часта зміна, видалення і додавання інформації в таблиці бази даних, то коефіцієнт FILLFACTOR варто установити якнайменше, наприклад, 20. З іншого боку, установка коефіцієнту значення 100 рекомендується при використанні великих таблиць, звертання до яких звичайно відбувається тільки для читання;

Pad index - опція визначає заповнення внутрішнього простору індексу і використовується разом з опцією Fill factor,

Drop existing - при використанні кластерного індексу, вибір даної опції визначає його повторне створення, що дозволяє запобігти небажане відновлення кластерных індексів.

Звичайному користувачу вкрай рідко приходиться створювати БД чи таблиці усередині її. Традиційно він працює з уже готовою структурою, що вже розроблена і реалізована адміністратором БД. Проте, для повного розуміння особливостей роботи SQL на операторах DDL (мова визначення даних) варто зупинитися досить докладно. За допомогою цих операторів можна:

· створити нову БД;

· визначити структуру нової таблиці і створити цю таблицю;

· видалити існуючу таблицю;

· змінити визначення існуючої таблиці;

· визначити представлення даних;

· забезпечити умови безпеки БД;

· створити індекси для доступу до таблиць;

· керувати розміщенням даних на пристроях збереження.

Оператори DDL дозволяють користувачу не вникати в деталі, збереження інформації в БД на фізичному рівні, тому що оперують, наприклад, такими поняттями, як чи таблиці полючи. У той же час, оператори DDL мають можливість маніпуляції з фізичною пам'яттю.

Власне DDL базується на трьох командах SQL:

· CREATE - створити, що дозволяє визначити й об'єкт БД;

· DROP - видалити, застосовуваний для видалення існуючого об'єкта даних;

· ALTER - змінити, за допомогою якого можна змінити визначення об'єкта БД.

Використання команд DDL під час роботи дозволяє зробити структуру реляционной БД динамічної. Іншими словами, у СУБД можна створювати, чи видаляти змінювати таблиці, одночасно з цим забезпечуючи доступ користувачам до даних. У свою чергу, це означає, що з часом БД може рости і змінюватися, а її експлуатація може продовжуватися в той час, коли в БД додаються всі нові таблиці і додатки.

Оператори DDL у СУБД можна використовувати як в інтерактивному, так і в програмному SQL. Наприклад, якщо чи програмі користувачу потрібно таблиця для тимчасового збереження результатів, то допускається створити цю таблицю, заповнити її інформацією, виконати необхідні маніпуляції з даними і потім видалити її. У серйозних СУБД за створення нових БД відповідає тільки адміністратор, хоча не виключена можливість того, що й окремим користувачам це може бути дозволено.

Методи створення БД, застосовувані у ведучих реляционных СУБД, мають ряд розходжень. Наприклад, у Microsoft SQL Server існує оператор CREATE DATABASE, що є частиною мови визначення даних і служить для створення БД. Відповідно, оператор DROP DATABASE видаляє існуючі БД. Ці оператори можна використовувати як в інтерактивному, так і в програмному SQL.

Більшість многопользовательских БД мають досить нескладну організацію фізичної пам'яті, що забезпечує підвищення її продуктивності. Наприклад, у Microsoft SQL Server адміністратор БД може за допомогою оператора CREATE DATABASE задати один чи кілька іменованих файлів:

CREATE DATABASE <NAME_DATABASE> ON <FILE1>, <FILE2>,...

Підхід, використовуваний у SQL Server, дозволяє розподіляти вміст БД по декількох дискових томах, про що вже говорилося вище. Наступним кроком, слідом за створенням порожній БД, є заповнення її таблицями.

 

Створення таблиць

Отже, після створення: БД необхідно здійснити створення, зміна, а якщо потрібно - те й успение таблиць. Ці дії відносяться до самих таблиць, а не до даних, що у них містяться.

Таблиці створюються командою CREATE TABLE. Ця команда створює порожню таблицю, тобто не утримуючих записів. Очевидно, що значення в неї можна ввести, наприклад, за допомогою команди INSERT. Головне в команді CREATE TABLE - це визначення імені таблиці й описи набору імен полів, що вказуються у відповідному порядку. Крім того, цією командою також обмовляються типи даних і розміри полів таблиці.

Очевидно, що в кожній таблиці повинне бути, принаймні одне поле.

Синтаксис команди CREATE TABLE наступний:

CREATE TABLE <TABLE NAME>

(<COLUMN NAME> <DATA TYPE> [(<SIZE>)],

<COLUMN NAME> <DATA TYPE> [(<SIZE>)]...);

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

Значення аргументу розміру залежить від типу даних. Якщо його не вказувати, то СУБД сама буде призначати значення автоматично. Треба сказати, що для числових значень це часто буває кращим виходом, тому що в цьому випадку всі полючи такого типу одержать той самий розмір, і будуть виключені проблеми їхньої загальної сумісності. Крім того, використання аргументу розміру деякими числовим даними не зовсім просте питання - якщо потрібно зберігати великі числа, те необхідно переконатися тім, що полючи досить великі для розміщення даних.

У той же час, тип даних CHAR вимагає обов'язкової вказівки розміру. Аргумент розміру - це ціле число, що визначає максимальну кількість символів, що може вмістити поле. Фактично, кількість символів такого полючи може бути від нуля (якщо поле має значення NULL) до цього числа, умовчанню аргумент розміру дорівнює 1, а це означає, що поле може містити тільки один символ.

Крім того, таблиці належать користувачу, що їх створив, а імена всіх таблиць, що належать даному користувачу, повинні відрізнятися друга від друга точно так само, як і на всіх полів усередині даної таблиці. Однак різні таблиці можуть використовувати однакові імена полів, навіть якщо вони: належать тому самому користувачу. Наприклад, поле з ім'ям SNUM є присутнім у таблицях STUDENTS і USP, нітрохи не заважаючи один одному.

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

SA.STUDENTS

має на увазі, що звертання йде до таблиці STUDENTS, створеної користувачем з ідентифікатором дозволу (ID) SA.

Приведемо приклад команди, що створить структуру таблиці STUDENTS:

CREATE TABLE STUDENTS (SNUM INTEGER, SEAM CHAR (20), SIMA CHAR (10), SOTCH CHAR (15) STIP DECIMAL);

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

Після того, як таблиця була створена, її можна змінити. Команда ALTER TABLE є широко доступним засобом для того, щоб змінити визначення існуючої таблиці. Найчастіше з її допомогою додають полючи до таблиці, хоча вона може чи видаляти змінювати їхні розміри. Типовий синтаксис цієї команди для додавання стовпця до таблиці, такий:

ALTER TABLE <TABLE NAME> ADD <COLUMN NAME> <DATA TYPE> <SIZE>;

Варто пам'ятати, що поле буде додано з NULL значеннями для всіх записів таблиці. Крім того, нове поле стане останнім один по одному в таблиці. Допускається додавання відразу кілька нових полів, відокремивши їхніми комами в одній команді.

Наприклад, для додавання до таблиці STUDENTS двох полів для збереження інформації про курс і спеціальність студента, можна скористатися наступною командою:

ALTER TABLE STUDENTS ADD COURS INTEGER, SPEC CHAR (10);

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

ALTER TABLE не діє, коли таблиця повинна бути перевизначена, однак при розробці БД не варто виключати необхідність цієї дії. Крім того, зміна структури таблиці в той момент, коли вона знаходиться у використанні, також чревате втратою інформації - наприклад, запит може зазнати невдачі з тієї причини, що якогось полючи в таблиці просто вже не існує. З цих причин краще розробляти БД так, щоб використовувати ALTER TABLE тільки крайньому випадку.

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

Синтаксис команди для видалення таблиці (за умови, вона є порожній) наступний:

DROP TABLE <TABLE NAME>;

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

Наприклад, для видалення таблиці STUDENTS, у якій записи попередньо вилучені, просто вводиться наступне: DROP TABLE STUDENTS;

Таким чином, використання розглянутих DDL дозволяє створювати нові, змінювати структуру існуючих і видаляти порожні таблиці БД.