Нові можливості T-SQL в MS SQL Server 2005
Гайдар Магдануров
Передмова
В MS SQL Server 2005 з'явилося безліч нововведень,
що дозволяють ще більш продуктивно використати цю СУБД. Багато хто з них, такі
як нові типи даних, інтеграція з платформою. NET, підтримка XML, нові
функції ранжирування, покращення в системі безпеки та інше, вже були раніше
описані в журналі RSDN Magazine [1, 2, 3, 4, 5]. У цій статті будуть розглянуті
нові оператори та функції роботи з даними. У зв'язку із прийдешнім в листопаді виходом
фінальної версії, наведена інформація є надзвичайно актуальною для всіх
розробників, які використовують SQL Server.
Крім опису нових можливостей в статті наведені
приклади коду, що реалізують подібну функціональність, але працюють у попередніх
версіях SQL Server, тому ця стаття може бути корисна не тільки читачам,
планують використання SQL Server 2005, але й користувачам більш ранніх
версій цієї СУБД.
Демонстраційна база даних
У статті не будуть розглядати стандартні
демонстраційні бази даних, що поставляються з SQL Server (Northwind,
AdventureWorks). Для найбільшої наочності прикладів, створимо невелику базу
даних деякого уявного магазину, що торгує ноутбуками.
Користувачеві цієї бази даних необхідно: мати
інформацію про моделі, що є в наявності на складі, здійснювати вибірку
моделей на підставі ціни і марки виробника, також, користувачеві необхідно
мати можливість оформляти замовлення клієнтів і складати чергу замовлень на
виконання.
Для реалізації цих бажань буде використана база
даних складається з чотирьох таблиць: Brands - довідник виробників,
що дозволяє відстежувати відносини компаній з точки зору «батьківська-дочірня»;
Products - таблиця наявних на складі моделей, що містить необхідну
інформацію про ціну, кількість, назву та базової конфігурації; Orders --
таблиця, що містить інформацію про замовлення; QrdersQueue - таблиця, що реалізує
функціональність черги замовлень.
CREATE TABLE [dbo]. [Brands] (
[BrandID] [int] IDENTITY (1,1)
NOT NULL,
[Name] [nvarchar] (32) NOT NULL,
[ParentID] [int] NULL DEFAULT
((0))
)
CREATE TABLE [dbo]. [Orders] (
[OrderID] [int] IDENTITY (1,1)
NOT NULL,
[Date] [datetime] NOT NULL,
[ProductID] [int] NOT NULL,
[Quantity] [int] NOT NULL
DEFAULT ((1))
)
CREATE TABLE [dbo]. [Products] (
[ProductID] [int] IDENTITY (1,1)
NOT NULL,
[BrandID] [int] NOT NULL,
[Model] [nvarchar] (32) NOT
NULL,
[Configuration] [nvarchar] (128)
NOT NULL,
[Price] [money] NOT NULL,
[Quantity] [numeric] (18, 0) NOT
NULL
)
CREATE TABLE [dbo]. [OrdersQueue] (
[QueueID] [int] IDENTITY (1,1)
NOT NULL,
[OrderID] [int] NOT NULL
)
Нові можливості T-SQL
На початку статті будуть розглянуті нові функції й оператори
T-SQL в SQL Server 2005 і приклади їх використання, зміни, зачепили
яким він був раніше оператор TOP, після чого розказано про нову можливість
обробки помилок у T-SQL.
Загальні табличні вирази
Загальні табличні вирази (Common Table Expressions,
CTE) дозволяють визначати часові іменований набір даних, функціонально
схожий на подання (View), доступний в межах пакету (batch).
Для спрощення подальшого викладу, введемо більш
простий термін віртуальне уявлення замість дослівного англійського перекладу
загальне табличне вираз.
Віртуальні подання дозволяють використовувати
одного разу оголошене у процедурі подання замість вкладених запитів, як
це доводилося робити в більш ранніх версіях SQL Server, що значно покращує
читаність T-SQL коду. Для оголошення подання використовується синтаксис
WITH ІмяПредставленія (ІмяПоля, ІмяПоля,
...) AS (Підзапит)
де число полів в оголошенні подання повинне
відповідати числу одержуваних полів у підзапит.
Припустимо, необхідно здійснити вибірку всіх моделей,
ціна яких перевищує середню ціну в модельному ряду свого виробника.
Завдання можна вирішити, використовуючи віртуальне
подання
WITH AvgPrice (BrandID, Price) AS
(SELECT BrandID, AVG (Price) FROM Products GROUP BY BrandID)
SELECT P. Model FROM Products AS P INNER JOIN AvgPrice AS A ON
P. BrandID = A. BrandID AND P. Price> A. Price
або використовуючи вкладений підзапит
SELECT
P. Model FROM Products AS P
INNER JOIN (SELECT BrandID, AVG (Price) FROM
Products GROUP BY BrandID) AS A ON
P. BrandID = A. BrandID AND P. Price>
A. Price
Як видно з прикладу, використання CTE і вкладених
підзапитів еквівалентно, але, читач легко може уявити ситуацію, коли
одне і те ж віртуальне представлення даних потрібно використовувати кілька разів
в збереженої процедурою, в цьому випадку один раз оголошене подання з
використанням CTE потребує значно менше SQL коду.
Рекурсія з використанням віртуальних уявлень
Одним з основних переваг віртуальних уявлень
(CTE), є просте і наочне побудова рекурсивних виразів.
Досить часто зустрічаються таблиці з ієрархічною
структурою даних ( «дерева»). У випадку, коли необхідно отримати рівень
вкладеності елемента, кращим рішенням буде використання рекурсивного запиту
з використанням віртуального представлення. Припустимо, в магазині, що використовує
демонстраційну базу даних, вирішили додати можливість задавати «ступінь
спорідненості »фірм, для визначення дочірніх компаній відомих брендів.
Для таблиці Brands потрібно створити віртуальний
уявлення, що використовує рекурсію для отримання рівня вкладеності:
WITH C (BrandID, [Name], ParentID, NestingLevel) AS
(
SELECT B. BrandID, B. [Name],
B. ParentID, 1 FROM Brands AS B WHERE ParentID = 0
UNION ALL
SELECT B. BrandID, B. [Name],
B. ParentID, (NestingLevel + 1) FROM Brands AS B
INNER JOIN C ON C. BrandID =
B. ParentID
)
SELECT * FROM C
Результатом виконання запиту буде таблиця, наприклад
така:
BrandID Name ParentID NestingLevel
----------------------------------------------- ---
1 Parent1 0 1
2 Parent2 0 1
3 Child1 1 2
4 Child11 3 3
5 Child12 3 3
Загальний принцип побудови рекурсивного вираження
WITH ІмяCTE (Визначення)
AS
(
SELECT ... - Вибірка з початковим умовою,
UNION ALL - Об'єднання результатів
SELECT ... - Вибірка определяющаяя крок
рекурсії
INNER JOIN CTE.ДочернійID =
ІмяТабліци.РодітельскійID - Приєднання «по батькові»
)
Без використання віртуального уявлення, для
досягнення того ж результату доведеться написати значно складніший
запит:
DECLARE @ CurrentID int
DECLARE @ Level int
SELECT TOP (1) @ CurrentID = BrandID FROM Brands ORDER BY BrandID
DECLARE @ StackTable TABLE (ID int, Level int)
DECLARE @ OutputTable TABLE (ID int, [Name] nvarchar (32), ParentID int,
Level int)
INSERT INTO @ StackTable VALUES (@ CurrentID, 1)
SET @ Level = 1
WHILE @ Level> 0
BEGIN
IF EXISTS (SELECT * FROM
@ StackTable WHERE Level = @ Level)
BEGIN
SELECT @ CurrentID = ID FROM @ StackTable WHERE Level = @ Level
INSERT INTO @ OutputTable
SELECT BrandID, [Name], ParentID, @ Level AS Level FROM Brands
WHERE BrandID = @ CurrentID
DELETE FROM @ StackTable
WHERE Level = @ Level AND ID = @ CurrentID
INSERT @ StackTable
SELECT BrandID, @ Level + 1 FROM Brands WHERE ParentID = @ CurrentID
IF @ @ ROWCOUNT> 0
SET @ Level = @ Level + 1
END
ELSE
SET @ Level = @ Level - 1
END
SELECT * FROM @ OutputTable ORDER BY ID
Як бачить читач, код без використання віртуальних
уявлень більш громіздкий і складний для розуміння. Незважаючи на те, що
подібний код нескладно написати, досить просто допустити прикру помилку, на
пошуки якої може піти багато часу.
Крім того, рекурсія з використанням віртуального
уявлення значно перевершує в продуктивності підхід з використанням
тимчасових таблиць. Так, для тестового набору зі ста тисяч записів в таблиці
Brands, час виконання коду з використанням віртуального представлення
виявилося в чотири рази менше, ніж час виконання коду без використання
той. Приблизно те ж відношення отримано і для менших наборів даних.
Оператори EXCEPT і INTERSECT
Оператори EXCEPT і INTERSECT дозволяють здійснити
вибірку даних, спільних або різних для декількох наборів даних. Синтаксис
нових операторів абсолютно аналогічний оператору UNION.
Припустимо, необхідно отримати BrandID виробників,
моделі яких не присутні в таблиці Products. Тоді, застосувавши оператор
EXCEPT наступним чином:
SELECT B. BrandID FROM Brands B
EXCEPT
SELECT P. BrandID FROM Products P
можна досягти того ж результату, що і при
використанні оператора EXITS в комбінації з оператором заперечення NOT:
SELECT
B. BrandID FROM Brands B
WHERE
NOT EXISTS (SELECT P. BrandID FROM Products P WHERE P. BrandID = B. BrandID)
Аналогічно, для того, щоб отримати BrandID виробників,
чиї моделі присутні в таблиці Products. Можна використовувати оператор
INTERSECT:
SELECT
B. BrandID FROM Brands B
INTERSECT
SELECT
P. BrandID FROM Products P
а можна і оператор EXIST без заперечення:
SELECT
B. BrandID FROM Brands B
WHERE
EXISTS (SELECT P. BrandID FROM Products P WHERE P. BrandID = B. BrandID)
або ж зовсім звичний синтаксис INNER JOIN:
SELECT
DISTINCT B. BrandID FROM Brands B
INNER JOIN Products P ON B. BrandID =
P. BrandID
Очевидно, що нові оператори дозволяють зменшити
кількість коду, необхідного для реалізації вибірки, причому виграш в обсязі
коду зростає пропорційно числу полів у вибірці. Також, не менш важливим
перевагою є краща читаність коду.
Продуктивність запиту при використанні нових
операторів, практично не відрізняється від продуктивності запитів з EXISTS і
JOIN. Число читань (Reads) і час виконання (Duration) мало відрізняються в
обох випадках.
Оператор APPLY
Іноді, при написанні складних процедур для роботи з
даними, зручно використовувати функції, повертають не скалярний значення, а
таблицю. У попередніх версіях SQL Server не було можливості в конструкціях
JOIN використовувати як аргументи викликається функції параметри з
зовнішнього запиту. Наприклад, створимо процедуру GetProductDetails:
CREATE
FUNCTION GetProductDetails (@ ProductID int) RETURNS TABLE
AS
RETURN
SELECT P. Model, P. Configuration, P. Price FROM Products P
WHERE P. ProductID = @ ProductID
Наступний код, який використовує GetProductDetails приведе
до помилки:
- Увага! Цей код не працює
SELECT
O. [Date], P. Model, P. Configuration, P. Price FROM Orders O
OUTER JOIN GetProductDetails (O. ProductID) AS
P ON P. ProductID = O. ProductID
У SQL Server 2005 для подібного використання функцій
призначений оператор APPLY. Використовуючи його замість JOIN можна досягти бажаного
результату:
SELECT
O. [Date], P. Model, P. Configuration, P. Price FROM Orders O
OUTER APPLY GetProductDetails (O. ProductID)
AS P
Оператор APPLY використовується в комбінації з двома
ключовими словами: CROSS і OUTER. Функціональність CROSS APPLY аналогічна INNER
JOIN - у випадку, якщо процедура не повертає результат, то рядок не потрапляє
в результуючий набір даних, OUTER APPLY працює аналогічно OUTER JOIN --
якщо процедура не повертає результат, то рядок все-таки потрапляє в
результуючий набір, а в колонках, відповідних одержуваним з процедури
даними будуть знаходитися значення NULL.
Виходячи з плану виконання запиту, функція
GetProductDetails виконується для кожного параметра, що отримується із зовнішнього
запиту, що призводить до драматичних наслідків з точки зору
продуктивності. Таким чином, використання APPLY для значних обсягів
даних може бути далеко не найкращим рішенням. Тому, необхідно
ретельно перевіряти продуктивність запитів, що використовують APPLY на реальних
обсягах даних і, відповідно до цього, робити висновок про розумність застосування
цього оператора при побудові запиту.
Інструкція TABLESAMPLE
Іноді буває необхідно отримати деяку вибірку
записів з бази даних, що відображає характер даних, що містяться в базі. Для
здійснення подібної вибірки в SQL Server 2005 добавлена інструкція
TABLESAMPLE, яка в якості параметру приймає кількість рядків або
відносна кількість рядків у відсотках від загального числа в таблиці.
Використовується інструкція наступним чином:
SELECT СпіскоПолей FROM ІмяТабліци
TABLESAMPLE (КолічествоПроцентов PERCENT)
або, якщо потрібно вибрати певну кількість рядків
SELECT СпіскоПолей FROM ІмяТабліци
TABLESAMPLE (ЧіслоСтрок ROWS)
Проте варто відзначити, що буде повернуто НЕ
задану кількість строк (ROWS) або відсотків (PERCENT), а лише приблизно
відповідне заданому кількість.
Якщо необхідно отримувати вибірку постійного
змісту протягом кількох повторюваних запитів, то потрібно вказати після
інструкції TABLESAMLE додатково інструкцію REPEATTABLE:
SELECT СпіскоПолей FROM ІмяТабліци
TABLESAMPLE (ЧіслоСтрок ROWS) REPEATTABLE (ЧіслоПовтореній)
Функція OUTPUT
Нова функція OUTPUT служить для повторного
використання даних запиту. За допомогою цієї функції можна отримати змінені
в поточному запиті дані та будуть використані для вставки в іншу таблицю, або
повернути ці дані в викликає код.
Уявімо, що в магазині, що використовує демонстраційну
базу даних, відбулася радісна подія, і вся партія, що надійшли в продаж
ноутбуків була закуплена великим замовником в момент вступу. У цьому випадку
необхідно дані, що вставляються в таблицю Products, помістити також і в таблицю
Orders. У SQL Server 2005 це можна зробити в одному запиті до бази даних:
INSERT
INTO Products (BrandID, Model, Configuration, Price, Quantity)
OUTPUT GETDATE (), inserted.ProductID,
inserted.Quantity
INTO Orders ([Date], ProductID, Quantity)
VALUES (@ BrandID, @ Model, @ Configuration,
@ Price, @ Quantity)
Читач легко побачить з прикладу, що для доступу до
змінним даними (вставляються в таблицю в даному прикладі) використовується
ідентифікатор (ім'я псевдо таблиці), що вказує на характер операції,
що проводиться з даними. Можливе використання наступних ідентифікаторів:
inserted, для команди INSERT
deleted, для команди DELETE
У разі використання функції OUTPUT в запиті
UPDATE, змінені дані будуть доступні в псевдо таблиці inserted, а дані,
які піддалися зміні в псевдо таблиці deleted.
Функція OUTPUT не може бути використана в запиті
INSERT, в якому вставка проводиться до подання даних (View), а також для
вставки змінених даних до подання або табличну функцію.
Також варто пам'ятати про те, що OUTPUT не гарантує,
що елементи будуть завантажувати в таблицю в тому ж порядку, в якому відбувається
застосування змін. При цьому, якщо в процесі виконання запиту UPDATE
змінюються будь-які змінні або параметри, то OUTPUT повертає модифіковані
значення параметрів або змінних, тобто такі значення, які змінні
або параметри мали до виконання запиту.
Застосування функції OUTPUT
Вибірка вставлених даних
Іноді буває зручно отримати в якості результату виконання
процедури, вставляють дані в таблицю, результуючу рядок, особливо коли
цей рядок містить колонку з унікальним значенням. Використовуючи OUTPUT це можна
наступним чином:
DECLARE
@ TempBrands TABLE (BrandID int, [Name] nvarchar (32))
INSERT
INTO Brands ([Name]) OUTPUT inserted .* INTO @ TempBrands
VALUES (@ Name)
SELECT
* FROM @ TempBrands
Відзначимо, що цей приклад показує роботу з одного
рядком, оскільки при втавке великої кількості рядків, поряок проходження
може бути порушений (як уже було написано вище, OUTPUT не гарантує порядок
рядків) і використовувати значення BrandID в зухвалій коді без Додаткова
перевірок буде проблематично.
Звичайно, немає жодної проблеми в тому, щоб отримати в
результаті запиту BrandID, не використовуючи OUTPUT, оскільки зазвичай всі дані
вже є в зухвалій процедуру коді (вони ж і передаються як
аргументів самої процедури), за винятком елемента з унікальним значенням.
INSERT
INTO Brands ([Name]) VALUES (@ Name)
SELECT @ @ IDENTITY
Реалізація функціональності черги
Функція OUTPUT також дозволяє зручно реалізувати
функціональність черги, «витягуючи» з таблиці запис, при цьому видаляти її.
Наприклад, якщо буде потрібно функціональність черг на виконання замовлення, то
необхідно буде створити таблицю-чергу, наприклад
CREATE
TABLE [Queue] (
[QueueID] [int] IDENTITY (1,1) NOT NULL,
[OrderID]
[int] NOT NULL
)
і за допомогою неї реалізувати необхідну
функціональність, використовуючи функцію OUTPUT:
DECLARE @ Queue TABLE (QueueID int, OrderID int)
DELETE TOP 1 FROM [Queue] ORDER BY QueueID
OUTPUT deleted.QueueID, deleted.OrderID INTO @ Queue
SELECT * FROM @ Queue
Без використання функції OUTPUT, код виходить
трохи більш громіздким:
DECLARE @ Queue TABLE (QueueID int, OrderID int)
INSERT INTO @ Queue (QueueID, OrderID) SELECT TOP 1 [Queue]. QueueID,
[Queue]. OrderID FROM [Queue]
DELETE [Queue] FROM [Queue] AS Q1
INNER JOIN @ Queue AS Q2 ON
Q1.QueueID = Q2.QueueID
SELECT * FROM @ Queue
Опції PIVOT і UNPIVOT
Магазин ноутбуків з успіхом використовує
демонстраційну базу даних протягом багатьох років, і накопичив величезну
статистику з продажу ноутбуків. Природно бажання знати, для порівняння,
обсяги продажів за різні роки і загальну суму прибутку. Для того, щоб з таблиць
Orders і Products отримати необхідну власників магазину інформацію кращим
способом є використання ключового функції PIVOT, дозволяющей як би
«Розвернути» дані в таблиці.
SELECT Model, [2005], [2004] FROM (
SELECT P. Model, DATEPART (year,
O. [Date]) AS [Year], O. Quantity FROM Orders O
INNER JOIN Products P ON
P. ProductID = O. ProductID
) AS C
PIVOT (SUM (Quantity) FOR [Year] IN ([2005], [2004])) AS PVT
З використанням віртуального представлення код можна
написати трохи інакше:
WITH C (Model, [Year], Quantity) AS (
SELECT P. Model, DATEPART (year,
O. [Date]) AS [Year], O. Quantity FROM Orders O
INNER JOIN Products P ON
P. ProductID = O. ProductID
)
SELECT Model, [2005], [2004] FROM C
PIVOT (SUM (Quantity) FOR [Year] IN ([2005], [2004])) AS PVT
Результатом виконання даного коду в демонстраційній
базі даних буде таблицю з трьома колонками: Model, 2005 і 2004. Наприклад:
Model 2005 2004
-----------------------------------
A75-S206 10 24
M40-110 17 38
S215SR 2 10
T2XRP 35 12
V6800V 12 4
У попередніх версіях SQL Server, де не була
реалізована функція PIVOT і CTE, щоб досягти необхідного результату, довелося
б писати код на зразок наведеного нижче.
SELECT
C. Model,
SUM (CASE C. [Year] WHEN 2005 THEN C. Quantity
ELSE 0 END) AS [2005],
SUM (CASE C. [Year] WHEN 2004 THEN C. Quantity
ELSE 0 END) AS [2004]
FROM
(
SELECT P. Model, DATEPART (year, O. [Date]) AS
[Year], O. Quantity FROM Orders O
INNER JOIN Products P ON P. ProductID =
O. ProductID
) AS C
GROUP
BY C. Model
Читач, навіть недосвідчений у програмуванні на T-SQL,
напевно відзначить складність роботи з кодом, написаний без використання
функції PIVOT, у випадку більш складних запитів. У той же час, PIVOT є
лише синтаксичної оболонкою для наведеної вище конструкції. Подивившись плани
виконання прикладу, наведеного вище та прикладу, з використанням функції
PIVOT, можна переконатися в їх ідентичності.
Функція UNPIVOT виконує процедуру зворотний PIVOT,
«Розгортаючи» назад таблицю даних, що піддалися «обробці» функцією PIVOT
, В початковий стан. Покладемо, що для ведення статистики є наступна
таблиця
CREATE TABLE [Statistics] (
[Model] [nvarchar] (32) NOT
NULL,
[2005] [int] NOT NULL,
[2004] [int] NOT NULL
)
що містить дані, отримані в ході виконання
попереднього запиту - приклад використання функції PIVOT.
SELECT
* FROM [Statistics]
UNPIVOT (TotalQuantity FOR [Year] IN ([2005],
[2004])) AS PVT
Результатом виконання буде таблиця з трьох стовпців:
Model, TotalQuantity, Year.
Model TotalQuantity Year
------------------------------------------
A75-S206 10 2004
A75-S206 24 2005
M40-110 17 2004
M40-110 38 2005
Оновлений оператор TOP
Оператор TOP широко використовується для обмеження числа
рядків, що повертаються командою SELECT. У попередніх версіях SQL Server, оператор
TOP брав як параметр тільки константу. У SQL Server 2005
параметром цього оператора може бути змінна, вираз чи вкладений
питання.
Наприклад, у такий спосіб можна здійснити вибірку
такого числа моделей деякого виробника, що відповідає середньому
кількістю моделей кожного виробника в таблиці Products:
SELECT
TOP (SELECT AVG (AvgNum) * FROM
(SELECT
COUNT (*) AS AvgNum FROM Products GROUP BY BrandID) AS NumTable) P. Model FROM
Products P
Для використання в якості параметра оператора TOP
змінної, вирази або вкладеного запиту необхідно укладати її в круглі
дужки:
SELECT
TOP (@ Num) * FROM ІмяТабліци
При використанні константи, дужки не обов'язкові в
команді SELECT, але обов'язкові при використанні з командами, що змінюють
дані, наприклад:
DELETE
TOP (10) FROM Orders ORDER BY Date DESC
У попередніх версіях SQL Server для використання
змінної як параметр оператора TOP доводилося вдаватися до складних
конструкціям або динамічному створення запиту.
DECLARE @ DynamicQuery varchar (100)
SET @ DynamicQuery = 'SELECT TOP' + CAST (@ N AS varchar) + '* FROM
Products '
EXECUTE (@ DynamicQuery)
Розбиття даних на сторінки з використанням
оператора TOP
Найбільш простим способом використання оператора TOP
для розбиття на сторінки є використання зміщення по колонці ID.
Процедура приймає параметр - кількість записів на сторінці і «останній» ID,
отриманий у попередньому запиті. При цьому, завдяки новим можливостям
оператора TOP, можна варіювати кількість записів на сторінці, використовуючи
таку просту процедуру.
SELECT
TOP (@ N) * FROM ІмяТабліци WHERE КолонкаID> @ ID
Більш загальний підхід, який не потребує збереження
«Останнього» ID попереднього запиту і формально не залежить від значення ID
виглядає так:
SELECT
* FROM (SELECT TOP (@ N * (@ PageNum + 1)) * FROM ІмяТабліци
ORDER
BY КолонкаID) AS PTable
WHERE
КолонкаID NOT IN (SELECT TOP (@ N *
@ PageNum) КолонкаID FROM ІмяТабліци
ORDER BY КолонкаID)
Слід звернути увагу читача, що запит буде
виконувати своє завдання лише за умови того, що колонка КолонкаID містить
унікальні значення. Таким чином, для забезпечення розбиття даних за допомогою
такої процедури потрібно мати в таблиці колонку IDENTITY.
Для того, щоб подібна процедура працювала в більш ранніх
версіях SQL Server, необхідно використовувати динамічне створення запиту:
DECLARE @ Query nvarchar (200)
SET @ Query = 'SELECT * FROM (SELECT TOP' + CAST (@ N * (@ PageNum + 1)
AS nvarchar) +
'* FROM ІмяТабліци ORDER BY КолонкаID) AS P
WHERE КолонкаID NOT IN (SELECT TOP '+ CAST (@ N * @ PageNum
AS nvarchar) +
'КолонкаID FROM ІмяТабліци ORDER BY КолонкаID)'
EXECUTE (@ Query)
Обробка помилок в SQL
У зв'язку з інтеграцією SQL Server з платформою. NET,
мови якої підтримують гнучкий механізм обробки виключень, розробники
SQL Server включили до T-SQL давно бажану SQL-програмістами можливість
обробки виключень. Поточна реалізація в SQL Server 2005 дозволяє
обробляти некритичні помилки за допомогою схожого на що став вже стандартним
синтаксис TRY ... CATCH.
BEGIN
TRY
- «Небезпечний» запит
END
TRY
BEGIN
CATCH
- Обробка помилки
END
CATCH
Механізм обробки помилок у T-SQL, звичайно, не такий
гнучкий, як в. NET мовами, але, тим не менше, дозволяє зробити досить
багато, аналізуючи код помилки, що повертається функцією @ @ ERROR. Наприклад, якщо
таблиця Products задана так, що не дозволяє зберігати від'ємне значення в
колонці Quantity:
CREATE TABLE [Products] (
[ProductID] [int] IDENTITY (1,1)
NOT NULL,
[BrandID] [int] NOT NULL,
[Model] [nvarchar] (32) NOT
NULL,
[Configuration] [nvarchar] (128)
NOT NULL,
[Price] [money] NOT NULL,
[Quantity] [int] NOT NULL,
CHECK ([Quantity]> = 0)
)
то можна використовувати наступний код для зміни
кількості наявних на складі ноутбуків і створення замовлення:
BEGIN TRY
BEGIN TRAN - Створення транзакції
INSERT INTO Orders ([Date],
ProductID, Quantity, [Year]) VALUES (GETDATE (), @ ProductID, @ Quantity, @ Year)
SET @ OrderID = @ @ IDENTITY
UPDATE Products2 SET Quantity =
Quantity - @ Quantity WHERE ProductID = @ ProductID
UPDATE Orders SET Quantity =
@ Quantity WHERE OrderID = @ OrderID
COMMIT - Якщо немає помилок, то підтверджуємо транзакцію
END TRY
BEGIN CATCH
DECLARE @ Err int
SET @ Err = @ @ ERROR
ROLLBACK - Відкат транзакції
IF @ ERR = 547
BEGIN
SELECT 'Недостатньо ноутов на складі' AS
Error
END
ELSE
BEGIN
SELECT 'Невідома помилка' AS Error, @ Err
AS ErrorNumber
END
END CATCH
Висновок
Нехай нові можливості Transact-SQL в SQL Server 2005
не вносять нічого принципово нового в пристрій SQL Server, але дозволяють
значно швидше створювати більш зрозумілий і легко читається код, спрощуючи,
таким чином, життя розробника.
На закінчення, автор настійно рекомендує читачеві
ознайомитися зі статтями з списку літератури, оскільки в них описані
кардинальні зміни основної концепції SQL Server.
Список літератури
Іван Бодягін, Нові можливості MS SQL Server 2004
"Yukon", RSDN Magazine # 6-2003
Антон Злагостев, MS SQL Server 9 "Yukon". Інтеграція з
. NET, RSDN Magazine # 6-2003
Іван Бодягін, версійність в "Yukon", RSDN Magazine
# 6-2003
Іван Бодягін, MS SQL 2005: віконні функції, RSDN
Magazine # 6-2004
Олексій Ширшов, Використання XML спільно з SQL,
RSDN Magazine # 2-2004
Для підготовки даної роботи були використані
матеріали з сайту http://www.rsdn.ru/
