Books and articles about SQL Rambler's Top100 Сменить язык на: Русский 25 August 2019 15:20:27


www.sql-ex.ru
Skip Navigation Links  

 

Print  Версия для печати

На главную страницу

Архитектура суррогатных ключей для затратных операций с базами данных.

Brian Walker (оригинал: A surrogate key architecture to perform powerful database operations)
Перевод Моисеенко С.И.

Часть 1   Часть 2   Часть 3    Части 4,5  

Часть 3. Хранимые процедуры: создание и удаление ограничений и индексов

Код T-SQL в листинге 1 создает шесть системных хранимых процедур в базе данных master. Я выбрал базу данных master для удобства, но хранимые процедуры можно создать и в пользовательских базах данных (удалите префикс " sp _" из имени перед созданием хранимых процедур в пользовательской базе данных). Шесть хранимых процедур сгруппированы в три набора по две процедуры в каждом. Первая пара создает/удаляет ограничения первичных ключей. Вторая пара создает/удаляет ограничения внешних ключей. Третья пара создает/удаляет индексы на внешних ключах.

ЗАМЕЧАНИЕ: Система именования в коде T-SQL не очень помогает в понимании того, как он работает. Параметры, переменные, таблицы и столбцы именуются скорее произвольно. Я покорно прошу вашего прощения за особенности моего стиля! Эти хранимые процедуры определенно отражают мой жуткий стиль.

Эти хранимые процедуры принимают пять или шесть параметров, и все они не являются обязательными. Первые пять параметров одни и те же для всех процедур.

Первый параметр (@DBUltra) определяет, генерируется ли скрипт T-SQL или действия выполняются непосредственно. Скрипт T-SQL может использоваться для создания файла распространения скрипта. Непосредственные действия применяются к текущей базе данных. Нулевое значение (0) приводит к генерации скрипта T-SQL , в то время как значение единицы (1) вызывает немедленное выполнение действий.

Следующие четыре параметра работают совместно, чтобы сформировать комбинацию критериев выбора таблиц, используя имена.

Второй и третий параметры (@DBIntra/@ DBExtra ) - списки имен таблиц, разделенных вертикальными линиями. Параметр @DBIntra определяет имена таблиц, которые будут включены. Параметр @DBExtra определяет имена таблиц, которые будут исключены.

Четвертый и пятый параметры (@PCIntra / PCExtra ) предлагают выбор таблиц по совпадению с образцом. Параметр @PCIntra включает таблицы при использовании оператора LIKE , примененного к именам. Параметр @PCExtra исключает таблицы при использовании оператора NOT LIKE .

Действие этих четырех параметров соединяется операторами AND. Часто используется только один из параметров (или ни одного), но может оказаться полезным задать @DBExtra и/или @PCExtra в комбинации с @PCIntra, чтобы работать с желаемым подмножеством таблиц. Если параметры опущены, или заданы NULL значения, они эффективно игнорируются при выборе.

Метод, используемый для выбора таблиц в этих процедурах, не претендует на самый эффективный способ обработки строк с разделителями. Функция CHARINDEX использовалась в целях простоты, поскольку производительность не является здесь существенной проблемой при ссылках на таблицы с очень скромным числом строк при административной работе. Если такая задача выбора решается в рабочей процедуре, которая ссылается на таблицы с большим числом строк, вероятно, будет лучше парсить строку с разделителями и использовать результирующий набор для соединения.

Хранимая процедура sp_CreatePrimaryKeys создает ограничения первичных ключей для выбранных таблиц. Шестой параметр (@PCUltra) определяет, являются ли индексы поддержки ограничений первичных ключей кластерными. Значение нуль (0) создает некластерные первичные ключи, а значение единица (1) создает кластерные первичные ключи.

Вы можете создать ограничения первичного ключа для всей базы данных с помощью вызова хранимой процедуры:

EXECUTE sp_CreatePrimaryKeys 1

Хранимая процедура sp_CreateForeignKeys создает ограничения внешних ключей для выбранных таблиц. Ограничения первичные ключей должны к этому времени уже существовать. Имеется локальная переменная, @FKey, которая используется как шаблон для нахождения столбцов внешних ключей. Звездочка в значении динамически заменяется именем предполагаемой родительской таблицы, и результат сравнивается с именами столбцов.

Вы можете создать ограничения внешних ключей для всей базы данных с помощью следующего вызова хранимой процедуры:

EXECUTE sp_CreateForeignKeys 1

Хранимая процедура sp_CreateForeignKeyIndexes создает индексы на внешних ключах для выбранных таблиц. Ограничения внешних ключей должны уже существовать. Шестой параметр (@DBAdmin) позволяет задать для индексов процент заполнения страниц ( FILLFACTOR ).

Вы можете создать индексы на внешних ключах для всей базы данных при помощи вызова следующей хранимой процедуры:

EXECUTE sp_CreateForeignKeyIndexes 1

Хранимая процедура sp_DeletePrimaryKeys удаляет ограничения первичных ключей для выбранных таблиц. Сначала должны быть удалены ограничения внешних ключей.

Хранимая процедура sp_DeleteForeignKeys удаляет ограничения внешних ключей для выбранных таблиц.

Хранимая процедура sp_DeleteForeignKeyIndexes удаляет индексы на внешних ключах для выбранных таблиц.

Эти процедуры содержат локальную переменную @TPre которая может использоваться для задания префикса в имени таблицы. Если префикс имени таблицы определяется этой переменной, то префикс можно опустить в именах таблиц, передаваемых как параметры. Особенно важно определить префикс имени таблиц (если он используется) в хранимой процедуре sp_CreateForeignKeys, в противном случае процедура будет пытаться найти префикс в именах столбцов внешнего ключа.

База данных из примера пока не наполнена какими-либо данными. Операторы INSERT , приведенные ниже, добавят некоторое количество данных для демонстрации связей между таблицами.

INSERT Region (RegionCode,RegionName,Representative) VALUES ('NW','NW USA ' ,'NW Sales')
INSERT Region (RegionCode,RegionName,Representative) VALUES ('SW',' SW USA ' ,'SW Sales')
INSERT Region (RegionCode,RegionName,Representative) VALUES ('NE',' NE USA ' ,'NE Sales')
INSERT Region (RegionCode,RegionName,Representative) VALUES ('SE',' SE USA ' ,'SE Sales')
INSERT Region (RegionCode,RegionName,Representative) VALUES ('CA',' Canada ' ,'CA Sales')
INSERT Region (RegionCode,RegionName,Representative) VALUES ('EU',' Europe ' ,'EU Sales')
INSERT Region (RegionCode,RegionName,Representative) VALUES ('DU','Down Under','DU Sales')
INSERT Region (RegionCode,RegionName,Representative) VALUES ('AO','All Others','AO Sales')

INSERT Customer (RegionID,Name,Address,Phone) VALUES (1,'Customer X','Address X','Phone X')
INSERT Customer (RegionID,Name,Address,Phone) VALUES (4,'Customer Y','Address Y','Phone Y')
INSERT Customer (RegionID,Name,Address,Phone) VALUES (7,'Customer Z','Address Z','Phone Z')

INSERT Vendor (Name,Address,Phone) VALUES ('Vendor X','Address X','Phone X')
INSERT Vendor (Name,Address,Phone) VALUES ('Vendor Y','Address Y','Phone Y')
INSERT Vendor (Name,Address,Phone) VALUES ('Vendor Z','Address Z','Phone Z')

INSERT Product (VendorID,Description,WholesaleCost,RetailPrice) VALUES (1,'Product X-1',2.00,4.00)
INSERT Product (VendorID,Description,WholesaleCost,RetailPrice) VALUES (1,'Product X-2',4.00,6.00)
INSERT Product (VendorID,Description,WholesaleCost,RetailPrice) VALUES (2,'Product Y-1',3.00,5.00)
INSERT Product (VendorID,Description,WholesaleCost,RetailPrice) VALUES (2,'Product Y-2',5.00,7.00)
INSERT Product (VendorID,Description,WholesaleCost,RetailPrice) VALUES (2,'Product Y-3',5.00,9.00)
INSERT Product (VendorID,Description,WholesaleCost,RetailPrice) VALUES (3,'Product Z-1',6.00,8.00)

INSERT Purchase (CustomerID,OrderNumber,OrderDate) VALUES (1,1001,'07/01/2005')
INSERT Purchase (CustomerID,OrderNumber,OrderDate) VALUES (2,1002,'07/02/2005')
INSERT Purchase (CustomerID,OrderNumber,OrderDate) VALUES (2,1003,'07/03/2005')

INSERT PurchaseItem (PurchaseID,ProductID,Quantity,LineNumber) VALUES (1,1,1,1)
INSERT PurchaseItem (PurchaseID,ProductID,Quantity,LineNumber) VALUES (2,1,1,1)
INSERT PurchaseItem (PurchaseID,ProductID,Quantity,LineNumber) VALUES (2,3,2,2)
INSERT PurchaseItem (PurchaseID,ProductID,Quantity,LineNumber) VALUES (3,2,1,1)
INSERT PurchaseItem (PurchaseID,ProductID,Quantity,LineNumber) VALUES (3,4,2,2)
INSERT PurchaseItem (PurchaseID,ProductID,Quantity,LineNumber) VALUES (3,5,3,3)

INSERT Shipment (Carrier,TrackingNumber,ShipDate) VALUES ('UPS' ,'1','07/01/2005')
INSERT Shipment (Carrier,TrackingNumber,ShipDate) VALUES ('UPS' ,'2','07/02/2005')
INSERT Shipment (Carrier,TrackingNumber,ShipDate) VALUES ('FedEx','1','07/03/2005')
INSERT Shipment (Carrier,TrackingNumber,ShipDate) VALUES ('FedEx','2','07/05/2005')

INSERT PurchaseItemShipment (PurchaseItemID,ShipmentID) VALUES (1,1)
INSERT PurchaseItemShipment (PurchaseItemID,ShipmentID) VALUES (2,2)
INSERT PurchaseItemShipment (PurchaseItemID,ShipmentID) VALUES (3,2)
INSERT PurchaseItemShipment (PurchaseItemID,ShipmentID) VALUES (4,3)
INSERT PurchaseItemShipment (PurchaseItemID,ShipmentID) VALUES (5,4)
INSERT PurchaseItemShipment (PurchaseItemID,ShipmentID) VALUES (6,4)

INSERT Payment (PurchaseID,Method,Amount) VALUES (1,'PayPal', 4.00)
INSERT Payment (PurchaseID,Method,Amount) VALUES (2,'PayPal',14.00)
INSERT Payment (PurchaseID,Method,Amount) VALUES (3,'Check' ,16.00)
INSERT Payment (PurchaseID,Method,Amount) VALUES (3,'Check' ,16.00)
INSERT Payment (PurchaseID,Method,Amount) VALUES (3,'Check' ,15.00)

Я знаю, знаю, что сторонники естественных ключей данных укажут, что мое использование предполагаемых значений IDENTITY в предложении VALUES по сути - провал использования суррогатных ключей. Они правы в некотором смысле. Приведенные операторы INSERT используют некоторые предположения относительно родительских строк, поскольку дочерние строки не связаны с родительскими с помощью естественных данных. Однако клиенты не делают заказы с помощью пакета операторов INSERT. Клиенты используют приложение, а приложение не должно иметь никаких предположений о родительских строках. Приложение может обрабатывать значения IDENTITY динамически, но данные из моего примера должны быть статическими.

Часть 1   Часть 2   Часть 3    Части 4,5  

На главную страницу

Print  Версия для печати


Usage of any materials of this site is possible
only under condition of mandatory allocation of the direct link to a site
http://www.sqlbooks.ru
on each page where used materials are placed.

 Main   Articles    Books 
Рейтинг@Mail.ru Rambler's Top100 Alt Упражнения по SQL: обучение, тестирование, сертификация по языку SQL Copyright c 2002-2006. All rights reserved.