До питання про ідентифікаторах
Автоінкременти і все, все, все ...
Іван Бодягін
Про що мова
Унікальна ідентифікація записів в таблиці, є
практично основою реляційних СУБД. Взагалі в реляційної теорії
передбачається, що якщо два записи ні чим один від одного не відрізняються, то це
явна надмірність, і кількість таких записів можна скоротити до одного.
Власне питань цієї самої ідентифікації, яких виникає на диво
багато, і присвячений цей FAQ.
Ідентифікація
Як вже говорилося унікальна ідентифікація записів --
це основа реляційних СУБД. Взагалі це питання досить тісно пов'язаний з
давнім теоретичним суперечкою «Сурогатні ключі» vs. «Природні ключі». Але
оскільки існує чудова стаття Анатолія Тенцера 
"Природничі
ключі проти штучних ключів ", то не думаю, що тут має сенс висвітлювати
це питання. Незалежно від використовуваного підходу, дуже часто, з тих чи
інших причин, дані, що відносяться безпосередньо до додатка, не вдається
завідомо однозначно розділити за записами. І логічно, що в таких випадках
вдаються до допомоги сервера, який генерує штучні ідентифікатори для
кожного запису.
Особливої уваги заслуговує термін «унікальний».
Унікальність серверного ідентифікатора має свої межі. У якихось випадках
гарантується унікальність в межах таблиці, тобто якщо не робити
спеціальних зусиль, то є шанс отримати однакові ідентифікатори в різних
таблицях, що не завжди прийнятно. У якихось випадках цей ідентифікатор
гарантовано є унікальним в межах БД, чого теж не завжди буває достатньо. І,
нарешті, є ідентифікатори, що гарантують унікальність в межах всіх СУБД
взагалі ...
Спосіб створення і вигляд цього ідентифікатора стандартом
не обумовлений, та й взагалі стандарт подібними дрібницями не займається, тому
кожен виробник СУБД реалізує цю функціональність так, як йому зручно. У
підсумку, ми маємо приголомшливий набір різних способів, вивудити з сервера
унікальний ідентифікатор, біда тільки в тому, що в кожного сервера цей спосіб
свій. Однак, незважаючи на все різноманіття підходів, практично в усіх СУБД,
в тому чи іншому вигляді, реалізована автоматична генерація послідовних
ідентифікаторів, це так звані «автоінкременти». Іншими словами, при зверненні
до сервера за таким ідентифікатором гарантується, що повернене число буде
більше, ніж число, отримане при попередньому зверненні.
ПРИМІТКА
Говорячи «більше», я кілька спрощую. У
принципі, у більшості СУБД, ніщо не заважає задати і негативне
приріст. Тобто, правильніше було б сказати, що кожне наступне число
відрізняється від попереднього на величину заданого приросту, яке цілком
може бути негативним, але думаю, що в даному випадку краще спростити.
Microsoft SQL Server
Ця СУБД надає два способи отримати забезпечити
унікальність запису. Один працює в межах таблиці і є
автоінкрементним, а інший зазіхає на унікальність «взагалі», і в принципі
подібне рішення покриває практично всі потреби в унікальних
ідентифікаторах.
Автоінкремент
Почнемо зі звичайного автоінкремента. На відміну від
більшості інших СУБД, тут ця функціональність реалізована не у вигляді
окремої функції, а у вигляді властивості таблиці, тому і гарантується
унікальність лише в межах таблиці, а не БД чи примірника сервера. Для того
щоб не боліла голова про унікальність записів, один з стовпців в таблиці
оголошується ідентифікатором, після цього, при додаванні кожної нової записи, в
цьому стовпці автоматично з'являється число, яке явно більше, ніж те,
яке було Згенеровано для попередньої доданої запису. Взагалі тут є
ряд тонкощів, але про них трохи пізніше.
Отже, при оголошенні стовпця унікальним, за допомогою
ключового слова IDENTITY встановлюється початкове значення та величина, на
яку буде це значення збільшуватися при додаванні кожного запису.
IDENTITY [(seed, increment)]
Тут, як не складно здогадатися, seed - це саме
перше значення, а increment - це величина, яка кожного разу буде додаватися
до попереднього значення. За замовчуванням seed і increment дорівнюють одиниці, тобто
виставити у стовпця властивість IDENTITY, рівноцінно виставлянню IDENTITY (1,1).
Ключове слово IDENTITY може бути вказано при створенні CREATE TABLE, або
зміні таблиці ALTER TABLE. При цьому тип стовпця повинен бути tinyint,
smallint, int, bigint, decimal (p, 0) або numeric (p, 0), тобто автонікремент
повинен бути целочисленным. Слід також пам'ятати, що тільки один стовпець
може бути оголошений IDENTITY. Таким чином, створення таблиці з
автоінкрементним полем виглядає приблизно так:
CREATE
TABLE Ident_table (ID int IDENTITY (1, 1), some_values varchar (50))
Тут стовпець ID оголошений автоінкрементним, і при
кожному додаванні нового запису в таблицю, в нього автоматично буде записуватися
нове число, хоча б на одиницю більше того, що було записано при попередньому
додаванні.
INSERT INTO Ident_table (some_values) VALUES ( 'value 1')
INSERT INTO Ident_table (some_values) VALUES ( 'value 2')
SELECT * FROM Ident_table
--- результат:
ID some_values
----------- ------------------------------------ --------------
1 value 1
2 value 2
(2 row (s) affected)
При цьому явно писати значення в автоінкрементним поле
не можна, відбудеться помилка, і оператор вставки не відпрацює.
INSERT
INTO Ident_table (ID, some_values) VALUES (3, 'value 2')
--- результат:
Cannot
insert explicit value for identity column in table 'Ident_table'
when
IDENTITY_INSERT is set to OFF.
Однак прямо в повідомленні про помилку є підказка як
це обмеження обійти. Для того щоб писати прямо в автоматичний
стовпець, необхідно встановити властивість IDENTITY_INSERT в ON.
SET IDENTITY_INSERT Ident_table ON
GO
INSERT INTO Ident_table (ID, some_values) VALUES (5, 'value 5')
SELECT * FROM Ident_table
--- результат:
ID some_values
----------- ------------------------------------ --------------
1 value 1
2 value 2
5 value 3
(3 row (s) affected)
Але тут інша тонкість, якщо при відключеній
Автоматичне не вказати явно, яке значення необхідно вставити в
автоінкрементним поле, то вставка знову-таки успіхом не увінчалася.
INSERT
INTO Ident_table (some_values) VALUES ( 'value 4')
--- результат:
Explicit
value must be specified for identity column in table 'Ident_table'
when
IDENTITY_INSERT is set to ON.
Таким чином, можливо два варіанти заповнення
автоінкрементним стовпця, або цим займається сервер, і тоді неможливо явно
змінити значення в цьому полі, або це робиться уручну, але тоді це поле
обов'язково до заповнення.
ПОПЕРЕДЖЕННЯ
Слід пам'ятати, що якщо
автоінкрементним поле заповнюється самостійно і на це поле не встановлено
ніяких додаткових вимог унікальності, то запросто можна записати
туди кілька однакових значень.
Якщо ж тепер знову повернути серверу можливість
вставляти номери в автоінкрементним поле, то таке значення буде більше,
на число вказане в increment при створенні стовпця, найбільшого значення в
цьому полі. Не важливо вручну було введено це найбільше значення або
Згенеровано сервером.
SET IDENTITY_INSERT Ident_table ON
GO
INSERT INTO Ident_table (some_values) VALUES ( 'value 4')
SELECT * FROM Ident_table
--- результат:
ID some_values
----------- ------------------------------------ --------------
1 value 1
2 value 2
5 value 3
6 value 4
(4 row (s) affected)
Все це звичайно добре, але, як правило, просто
унікально ідентифікувати запис недостатньо, необхідно ще пов'язати цю
запис із записом з іншої таблиці за цією самою ідентифікатором. А для цього
треба вміти отримувати цей ідентифікатор відразу ж після його створення. Для
виконання цього завдання в Microsoft SQL Server існують 3 функції: @ @ IDENTITY,
SCOPE_IDENTITY () і IDENT_CURRENT ().
Функція @ @ IDENTITY повертає останнє значення,
записане північчю в автоматичний стовбець в поточній сесії. Що це
означає? Якщо між викликом INSERT і викликом @ @ IDENTITY встигне пролізти
вставка з іншої сесії, то @ @ IDENTITY поверне ідентифікатор, який був
записаний при першому вставці. Тобто, при користуванні @ @ IDENTITY немає
необхідності піклуватися про те, що паралельні вставки будуть заважати один
одного при отриманні правильних ідентифікаторів, сервер все зробить сам.
---- Session 1:
INSERT INTO Ident_table (some_values) VALUES ( 'value 5')
---- Session 2:
INSERT INTO Ident_table (some_values) VALUES ( 'value 6')
---- Session 1:
SELECT @ @ IDENTITY as [Last ID in session]
SELECT * FROM Ident_table
--- результат:
Last ID in session
----------------------------------------
7
(1 row (s) affected)
ID some_values
----------- ----------------------------
... ...
6 value 4
7 value 5
8 value 6
(6 row (s) affected)
Все чудово, але в подібному підході з сесіями
є один, досить серйозний недолік. Якщо на таблиці з автоінкрементним
стовпцем висить який-небудь тригер на вставку, який, у свою чергу, щось
кому-то вставляє, то @ @ IDENTITY поверне не значення, записане сервером
оригінальну таблицю, а те значення, яке буде записано після другого
вставки в тригері, тому що формально це все ще та сама сесія.
Для того щоб уникнути таких неприємностей, служить
SCOPE_IDENTITY (), який повертає значення, записане сервером автоматичний
стовпець не тільки в рамках сесії, але і в рамках поточного пакета (batch).
- ще одна табличка з автоінкрементом
CREATE TABLE Ident2 (ID int IDENTITY (0, -2), value varchar (50))
GO
- тригер на вставку до
первісної табличці
CREATE TRIGGER IdentTrigger ON Ident_table
FOR INSERT
AS
INSERT INTO Ident2 (value) VALUES (GetDate ())
GO
- додавання ще одного запису
INSERT INTO Ident_table (some_values) VALUES ( 'value 7')
- насолода результатом ...
SELECT @ @ IDENTITY as [Last ID in session (@ @ IDENTITY)]
SELECT SCOPE_IDENTITY () as [Last ID in batch (SCOPE_IDENTITY ())]
SELECT * FROM Ident_table
--- результат:
Last ID in session (@ @ IDENTITY)
----------------------------------------
0
Last ID in batch (SCOPE_IDENTITY ())
----------------------------------------
9
ID some_values
----------- ----------------------------
... ...
8 value 6
9 value 7
(7 row (s) affected)
IDENT_CURRENT (), у свою чергу, не звертає ні якого
уваги ні на сесії, ні на пакети, а просто повертає останнє число
записане сервером автоінкрементним полі у зазначеній таблиці. Природно, в
даному випадку можна запросто отримати ідентифікатор, згенерована сервером
для іншої сесії.
--- Session 1:
INSERT INTO Ident_table
(some_values) VALUES ( 'value 8')
--- Session 2:
INSERT INTO Ident_table
(some_values) VALUES ( 'value 9')
--- Session 1:
SELECT @ @ IDENTITY as [Last ID in session],
SCOPE_IDENTITY () as [Last ID
in batch],
IDENT_CURRENT ( 'Ident_table')
as [Last ID in IdentTable]
SELECT * FROM Ident_table
SELECT * FROM Ident2
--- результат:
Last ID in session Last ID in
batch Last ID in IdentTable
------------------
---------------- ---------------------
-2 10 11
ID some_values
----------- ------------------------------------ ----------
... ...
9 value 7
10 value 8
11 value 9
(9 row (s) affected)
ID value
-----------
----------------------------------------------
0 1961-02-01 19:15
-2 1961-02-01 19:30
(2 row (s) affected)
Власне цей приклад і демонструє відмінності всіх
трьох способів отримання записаного сервером значення автоінкремента. Ці
способи покривають практично всі потреби в автоінкрементах, які можуть
виникнути при розробці.
Однак слід враховувати ще ряд нюансів. По-перше,
очевидно, що ніхто не гарантує відсутність «дірок» при автоматичному
генерації значень у стовпці. А, по друге, генерація нового значення для
автоінкремента виконується в не явною автономної транзакції. Це означає, що
якщо сама по собі операція додавання запису не увінчається успіхом, або
транзакція, в якій буде проводитися додавання, закінчиться скасуванням, то
сервер наступне автоінкрементним значення згенерує так, як ніби-то
попереднє додається новий запис відбулося успішно. І таким чином,
утворюється розрив в автоматичній нумерації.
- початок транзакції з
вставкою
BEGIN TRAN
INSERT INTO Ident_table (some_values) VALUES
( 'value 10')
- відкат, нова запис не
додається
ROLLBACK
- А тут вставка «по
чесному »
INSERT INTO Ident_table (some_values) VALUES ( 'value 11')
- Дивимось що вийшло
SELECT * FROM Ident_table
--- результат:
ID some_values
----------- ------------------------------------ --------------
... ...
10 value 8
11 value 9
13 value 11
(10 row (s) affected)
І можна спостерігати розрив в ідентифікації записів.
І ще один нюанс, навіть якщо видалити всі записи з
таблиці, наступні ідентифікатори, що повертаються сервером, не обнулені, а
будуть продовжувати збільшуватися, як ніби записи не віддалялися. У деяких
випадках може знадобитися обнулити серверний генератор послідовностей або
проініціалізувати його яким-небудь іншим значенням. Для цих цілей
існує системна функція DBCC CHECKIDENT. З її допомогою можна перевірити
лічильник «на правильність» і/або проініціалізувати його новим значенням. Якщо
ж необхідно скинути лічильник в початкове значення при видаленні всіх записів,
то можна скористатися не оператором DELETE, а оператором TRUNCATE. У цьому
випадку лічильник автоматично скинути в початкове значення, але слід
пам'ятати, що TRUNCATE не можна користуватися, якщо на таблицю посилаються зовнішні
ключі інших таблиць.
Існують так само спеціальні команди, за допомогою
яких можна дізнатися початкове значення генератора певної таблиці та
приріст, тобто ті значення, які були встановлені за умов згадування IDENTITY.
Це IDENT_INCR і IDENT_SEED відповідно.
Таким чином, у світлі всього вищесказаного, самий
типовий сценарій застосування автоінкремента виглядає приблизно так:
DECLARE @ PrimaryKey int
BEGIN TRAN
INSERT INTO MasterTbl (<... some fields ...>) VALUES (<...
some values ...>)
SET @ PrimaryKey = SCOPE_IDENTITY ()
INSERT INTO DetailTbl (ForeignKey, <... some other fields ...>)
VALUES (@ PrimaryKey, <... some other values ...>)
COMMIT
Глобальний ідентифікатор
Ідентифікатор в межах таблиці - це звичайно здорово,
але аж ніяк не межа мрій. У деяких випадках зовсім не зайвою була б
можливість отримати запис, гарантовано унікальну в межах бази даних,
примірника сервера або навіть у межах всіх серверів підприємства. Для
унікальності в межах БД тип даних int, може ще і згодиться, але от якщо
брати щось більш глобальне, то чотирьох мільярдів унікальних значень може
і не вистачити. Max (int) - це багато, але не так багато як хотілося б, проблема в
те, що призначаючи нове автоінкрементним поле, яке по ідеї повинно бути
гарантовано унікальним за будь-яких обставин, доводиться думати про інших
унікальних полях, щоб жодним чином діапазони їх ідентифікатор не
перетнулися, а звідси і абсолютно неприродні обмеження.
Для виходу з подібної ситуації Microsoft пропонує
використовувати тип даних uniqueidentifier - 16 байтного число, яке, будучи
сгенеренним за допомогою спеціальної функції, є гарантовано унікальним
за будь-яких обставин. Вся принадність такого підходу полягає в тому, що
такий ідентифікатор, будучи отриманим на одному сервері, явно не
перетнеться з іншими подібними ж ідентифікаторами, отриманими на інших
серверах. Унікальний ідентифікатор виходить за допомогою функції NewID ().
Типовий сценарій роботи з таким ідентифікатором виглядає приблизно так:
DECLARE @ PrimaryKey uniqueidentifier
BEGIN TRAN
SET @ PrimaryKey = NewID ()
INSERT INTO MasterTbl (PrimaryKey, <... some fields ...>) VALUES
(@ PrimaryKey, <... some values ...>)
INSERT INTO DetailTbl (ForeignKey, <... some other fields ...>)
VALUES (@ PrimaryKey, <... some other values ...>)
COMMIT
Для розробників під ADO.Net є ще один зручний
привід використати такий ідентифікатор. Бо, насправді, це
звичайний GUID, то при додаванні записів у від'єднаних набір даних (Dataset),
можна абсолютно спокійно отримати цей ідентифікатор на клієнті і бути
впевненим, що на сервері такого немає.
Таким чином, використовувати звичайний автоінкремент
зручно тільки в межах таблиці, а GUID, у всіх інших випадках. Якщо ж,
хоча б у віддаленій перспективі, передбачається якусь подобу реплікації
або синхронізації даних між різними базами або серверами, то краще в
як ідентифікатора відразу використовувати GUID. Це серйозно зменшить
кількість головного болю.
Продуктивність
Досить часто виникає питання про продуктивність
цих двох способів. Природно, що в загальному випадку робота з identity швидше,
ніж з GUID. Але помітно це стає тільки на великих обсягах даних, порядку
десятків мільйонів записів на таблицю. На таких обсягах виграш від
використання identity становить від 5 до 20% в залежності від складності
запитів, але ці дані дуже приблизні і всерйоз на них орієнтуватися
не варто. Якщо стоїть проблема вибору, то краще поміряти на конкретних даних і
структурі бази.
Основне уповільнення при роботі з GUID виникає через
того, що він довший. Але зовсім не тому, що чотири байти порівняти швидше,
ніж шістнадцять. Справа в тому, що, як правило, за ідентифікатором будується
індекс, для більш ефективного пошуку. Вузли індексу зберігаються на диску
посторінково, кожна сторінка має фіксовану довжину. І чим більше ключів
влазить на одну сторінку, тим менше звернень до диска доводиться робити при
пошук якого-небудь значення, і, як наслідок, тим ефективніше пошук, оскільки
дискові операції найдорожчі. Таким чином, ефективність пошуку за індексом
залежить від розміру ключа, а при використанні GUID ключ довше.
Timestamp (rowvesion)
Строго кажучи, існує ще один тип даних,
призначений для ідентифікації, але ідентифікації дещо іншого роду. Тип
даних rowversion призначений для ідентифікації версії рядка в межах бази
даних.
ПРИМІТКА
Взагалі можна вказувати як timestamp
(стара назва), так і rowversion, але Microsoft рекомендує використовувати
rowversion, оскільки, по-перше, це більш точно відображає суть цього типу
даних, а по-друге, ключове слово timestamp зарезервовано в стандарті для
іншого типу.
Якщо в таблиці є поле типу rowversion (воно, як
і identity, може бути тільки одне на таблицю), то значення в цьому полі буде
автоматично змінюватися, при зміні будь-якого іншого поля в записі. Таким
чином, запам'ятавши попереднє значення, можна визначити - змінювалася запис, або
ні, не перевіряючи всіх полів. Для кожної бази SQL сервер здійснює окремий лічильник
rowversion. При внесенні зміни в будь-яку таблицю з стовпцем такого типу,
лічильник збільшується на одиницю, після чого нове значення зберігається в
зміненої рядку. Поточне значення лічильника в базі даних зберігається в
змінної @ @ DBTS. Для зберігання даних rowversion використовується 8 байт, тому
цей тип цілком може бути представлений, як varbinary (8) або binary (8).
Номер рядка
Прохання підказати спосіб вивести номер рядка
є лідером хіт-параду. Це питання по суті своїй не коректний і, тим не
менше, зустрічається найчастіше. Але і питалися теж можна зрозуміти, адже
попросити вивести номер рядка цілком природно. Але справа в тому, що в
реляційної теорії взагалі немає такого поняття як «номер рядка», в цьому сенсі
запису в таблиці абсолютно рівноцінні. Більш того, якщо замислитися, то
стає ясно, що в принципі немає таких завдань, які вимагали б нумерації
рядків на сервері перед відправленням їх клієнту. Немає сенсу просити номер у
сховища даних, оскільки ця інформація не постійна і залежить від порядку
відображення, а, отже, не знаходиться в компетенції сховища.
Клієнтського ж програми, що займається відображенням отриманої інформації, не
становить ні якого праці при отриманні строк пронумерувати їх в потрібному
порядку.
Безумовно, в кожному сервер баз даних існує
спосіб, можливо відносно легкий, ізвернуться і пронумерувати рядки за потрібне
чином перед відправленням їх клієнтові, але подібних рішень треба по можливості
уникати.
ANSI SQL
Цей спосіб пронумерувати виводяться запису на сервері
по ідеї має працювати на будь-яких СУБД, мінімально що задовольняють вимогам
стандарту ANSI SQL.
SELECT
(SELECT
count (*) FROM NumberingTable WHERE OrderedValue <= X. OrderedValue), X. *
FROM
NumberingTable X ORDER BY OrderedValue
Спосіб цей всім хороший, крім двох речей: по-перше,
необхідно, щоб стовпець, по якому треба сортувати (OrderedValue) був
унікальним, а по друге, працює ця конструкція дуже повільно, причому
швидкість падає з кожною новою записом.
Microsoft SQL Server
Цілком стандартним рішенням для цього сервера є
приміщення в тимчасову таблицю з автоінкрементом, первинного ключа вибірки,
яку необхідно пронумерувати. А потім, результуюча вибірка об'єднується
з тимчасовою таблицею і сортується в порядку зростання автоінкремента.
SET NOCOUNT ON
DECLARE @ tmp TABLE (ID int IDENTITY, OuterID int)
INSERT INTO @ tmp (OuterID)
SELECT [ID] FROM sysobjects ORDER BY [Name]
SELECT T. [ID], SO .* FROM sysobjects SO INNER JOIN @ tmp T ON SO. [ID] =
T. OuterID
ORDER BY T. [ID]
Oracle
Тут можна відбутися більш простим запитом, але теж
не зовсім тривіальним. Ця СУБД дає деякий доступ до своєї внутрішньої
інформації, і всередині у неї записи пронумеровані. Але проблема в тому, що сервер
нумерує рядка для своїх потреб до сортування, тому доводиться робити
вкладений запит з сортуванням.
SELECT
RowNum, U. * FROM
(SELECT
* FROM user_tables ORDER BY tablespace_name) U
Але ще раз хочу нагадати, що якщо є можливість
позбавити сервер від нумерації, значить треба сервер від неї рятувати. У додатку
має бути щось сильно не так, якщо потрібно подібна функціональність.
для перегляду
Досить часто, особливо при побудові web-сайтів,
доводиться стикатися із завданням для перегляду записів. У деяких
СУБД є спеціальні параметри для подібного висновку, а в деяких все
доводиться робити самостійно. Але в будь-якому випадку серверу доводиться
виконувати приблизно один і той же обсяг роботи. Спочатку необхідно вибрати всі
запису, потім відсортувати їх, а потім надіслати клієнту потрібний діапазон.
Очевидно, що видача діапазону без сортування сенсу не має, так як СУБД не
гарантує видачу записів в будь-якому певному порядку.
Microsoft SQL Server
Тут можна придумати досить багато рішень і всі вони
будуть по-своєму гарні.
По-перше, в даному випадку, на диво ефективним
може виявитися застосування динамічних запитів. При вдалому збігу
обставин (потрібні індекси в потрібних місцях, і досить прості вибірки
нехай і на великому обсязі даних) цей спосіб є найшвидшим.
DECLARE @ Page int, @ PageSize int, @ MaxRecord varchar (10), @ Count
varchar (10)
- номер сторінки
SET @ Page = 10
- розмір сторінки
SET @ PageSize = 20
SET @ MaxRecord = cast ((@ Page * @ PageSize + @ PageSize) as varchar (10))
SET @ Count = cast (@ PageSize as varchar (10))
EXECUTE ( 'SELECT * FROM
(SELECT TOP '+ @ Count +' *
FROM
(SELECT TOP '+ @ MaxRecord
+ '* FROM sysobjects
ORDER BY name ASC) SO1
ORDER BY name DESC) SO2
ORDER BY name ')
Однак при такому підході слід бути уважним,
оскільки у разі не оптимально написаного запиту продуктивність падає
досить різко. Втім, на таких обсягах, де це буде помітно, до написання
будь-якого запиту треба підходити вдумливо і акуратно.
Якщо ж ви відчуваєте підсвідомий страх і
неприязнь до динамічним запитам, то є ще кілька способів. Наприклад, можна
зробити практично те ж саме, що й наведений вище запит, але розкласти
його по частинах, скориставшись тимчасовою таблицею. З таблиці (або таблиць),
яку потрібно відобразити посторінково, береться поле, однозначно ідентифікує
кожен запис, і скидається в потрібному порядку в тимчасову табличку з
автоінкрементним полем. Причому скидається не всі, а тільки до останнього запису
відображуваної сторінки. А результуюча вибірка робиться з об'єднанням з
вищезгаданої тимчасової таблицею, обмежуючи діапазон із автоінкрементним
полю.
SET NOCOUNT ON
DECLARE @ Page int, @ PageSize int, @ MaxRecord int
- номер сторінки
SET @ Page = 10
- розмір сторінки
SET @ PageSize = 20
- створення тимчасового сховища
DECLARE @ pg TABLE (RowNum int IDENTITY, OuterID int)
- максимальна кількість
записів, що потрібно забрати
- з іншої таблиці
SET @ MaxRecord =
@ Page * @ PageSize + @ PageSize
- установка кількості
записів оброблюваних запитом
SET ROWCOUNT @ MaxRecord
- запис відсортованих
даних в змінну
INSERT INTO @ pg (OuterID)
SELECT ID FROM OriginalTable ORDER BY SortValue ASC
- тепер потрібні записи для
однієї сторінки
SET ROWCOUNT @ PageSize
- ось ці дані йдуть на
клієнта
SELECT O. * FROM OriginalTable O INNER JOIN @ pg P
ON O. ID = P. OuterID
WHERE RowNum> @ MaxRecords - @ PageSize
ORDER BY P. RowNum
- зняття обмежень на
кількість записів
- обробляється одним
запитом
SET ROWCOUNT 0
Чи не становить жодних проблем написати подібну
збережену процедуру, яка розбивала б на станиці будь-яку комбінацію
таблиць подібним чином.
Ознайомившись з цими методами, може виникнути
абсолютно законне питання - а чи не можна реалізувати все те ж саме, але без
динамічних запитів і без тимчасових таблиць? Точнісінько те ж саме не можна,
оскільки ключове слово TOP не розуміє змінних, а жорстко зашивати в запит
номер і розмір сторінки сенсу не має. Змінні розуміє оператор ROWCOUNT,
який робить те ж саме, що й TOP, але область дії цього оператора
поширюється і на підзапит, що в даному випадку не годиться, тому й
доводиться використовувати тимчасову таблицю.
Але можна використовувати курсори, і за допомогою них
здійснювати зсув до потрібного запису і виробляти необхідну вибірку.
SET NOCOUNT ON
DECLARE @ Page int, @ PageSize int, @ MinRecord int, @ MaxRecord int
- номер сторінки
SET @ Page = 10
- розмір сторінки
SET @ PageSize = 20
SET @ MinRecord = @ Page * @ PageSize
SET @ MAXRecord = @ Page * @ PageSize + @ PageSize
SET ROWCOUNT @ MaxRecord
- створення курсору
DECLARE @ Cursor CURSOR
SET @ Cursor = CURSOR SCROLL KEYSET READ_ONLY FOR
SELECT * FROM OriginalTable
ORDER BY SortValue
OPEN @ Cursor
- зміщення до потрібного запису
FETCH ABSOLUTE @ MinRecord FROM @ Cursor
DECLARE @ i int
SET @ i = 0
- виор в циклі потрібного
кількості
WHILE @ i <@ PageSize
BEGIN
FETCH NEXT FROM @ Cursor
SET @ i = @ i + 1
END
CLOSE @ Cursor
DEALLOCATE @ Cursor
SET ROWCOUNT 0
Цей спосіб трохи швидше, ніж попередній, але має
тим недоліком, що повертає не один набір записів, а кожний запис у
окремому наборі.
Можна так само скомбінувати два останніх способу і
позиціонувати за допомогою курсору, а потім зчитувати в тимчасову таблицю
потрібну кількість записів. Отриманий гібрид буде працювати досить швидко,
а в деяких випадках це найшвидший варіант, і буде найбільш стійкий до
різного роду вольностям у запиті.
Що стосується відносної швидкості всіх методів, то
тут можна сміливо використовувати те, до чого душа лежить більше, різниця складає
не більше декількох відсотків.
Обмеження першого вибірки останньої записом
відображуваної сторінки, дозволяє досить сильно підвищити швидкість виконання
запиту, тому що в переважній більшості випадків далі другого-третього
сторінки користувачі не заглядають. Для дуже великих вибірок, критичних до
часу звернення до останніх сторінках, можна придумати хитру процедуру,
яка вміла б розрізняти початкові та кінцеві сторінки, і до останніх
застосовувати зворотну сортування при вибірці. Таким чином, до першого і останнім
сторінкам був би найбільш швидкий доступ.
Oracle
У Оракл проблема для перегляду вирішується
дещо простіше. Стандартний спосіб, відповідний для переважної більшості
завдань виглядає приблизно так:
SELECT * FROM
(SELECT A. *, RowNum R FROM
(SELECT * FROM user_tables
ORDER BY table_name) A
WHERE RowNum <: MaxRecord)
WHERE R> =: MinRecord
Однак при великому бажанні, так само можна придумати
хитру процедуру, яка по-різному обробляла б початкові і кінцеві
станиці великої вибірки.
Yukon
У новій версії Microsoft SQL Server спеціальних
ключових слів, для подібної функціональності не додалося, але, тим не менше,
посторінковий вибірку можна трохи спростити. У цій версії ключове слово TOP
стало розуміти змінні, і навіть запити, і посторінковий висновок можна
організувати приблизно так:
SELECT
* FROM
(SELECT
TOP (@ PageSize) * FROM
(SELECT TOP (@ Page * @ PageSize + @ PageSize)
* FROM sys.objects
ORDER BY name ASC) SO1
ORDER BY name DESC) SO2
ORDER BY name
Швидкості це не додасть, оскільки сервер все одно
виконує приблизно одну й ту ж роботу, але кількість натискань на клавіатуру
дещо зменшиться, що, можливо, продовжить термін її служби. Ну і код виглядає
компактніше, що звичайно приємно.
Спростити посторінковий висновок навряд чи можливо.
Існують СУБД, в яких запроваджено спеціальний синтаксис для виведення даних
посторінково, але це не більше ніж syntactic sugar, тому що виробляються ті ж
дії, що і в прикладах вище, просто частина реалізації залишається за кадром.
Список літератури
Для підготовки даної роботи були використані
матеріали з сайту http://www.rsdn.ru/
