SQL Server: соглашения при программировании баз данных, советы и лучшие методики

По материалам статьи Vyas Kondreddi: SQL Server Database Coding Conventions, Best Practices and Programming Guidelines
Перевод Виталия Степаненко

Базы данных являются сердцем и душой многих приложений на предприятиях, и очень важно обращать особое внимание на программирование баз данных. Я видел много случаев, когда программирование баз данных пускается на самотек, т.к. считается, что это что-то легкое и может быть сделано любым пользователем. Это неверно.

Для лучшей работы с базами данных Вам нужны настоящий администратор баз данных и специалист по программированию баз данных, причем не важно, что вы используете: Microsoft SQL Server, Oracle, Sybase, DB2, или что-то еще! Если у Вас нет специалистов по базам данных во время разработки, это часто оканчивается тем, что базы данных становятся узким местом при повышении производительности системы.
Я решил написать эту статью, чтобы соединить вместе лучшие методы программирования баз данных, так, чтобы это было полезно и администраторам, и разработчикам баз данных.

Здесь представлены советы и методики, позволяющие повысить качество разработки, производительность и простоту обслуживания. Этот список сейчас еще далеко не полный, и будет постоянно обновляться. Хочу высказать отдельное спасибо Tibor Karaszi (SQL Server MVP) и Linda (lindawie) за то, что они нашли время прочитать эту статью и выдать свои предложения по ее улучшению.

Утвердите соглашения по наименованию объектов базы данных, сделайте их стандартом для всей вашей организации, и будьте последовательны в их использовании.Это поможет сделать Ваш код более читабельным и понятным. Нажмите на эту ссылку, чтобы увидеть соглашения по наименованию объектов базы данных, которых я придерживаюсь.

Убедитесь, что Ваши данные находятся хотя бы в 3 нормальной форме. В то же время, производительность запросов не должна страдать. Небольшая денормализация помогает быстрому выполнению запросов.

Создавайте подробные комментарии в Ваших хранимых процедурах, триггерах и особенно в SQL скриптах, когда что-либо не является очевидным. Это сделает Ваш код понятным для других программистов. Не переживайте за длину Ваших комментариев, т.к. это не повлияет на производительность, в отличие от других интерпретаторов, таких, как ASP 2.0.

Не используйте выражение SELECT * в Ваших запросах. Всегда указывайте названия столбцов после оператора SELECT, например:

SELECT CustomerID, CustomerFirstName, City.

Этот способ обеспечивает меньшее количество дисковых операций и лучшую производительность.

Старайтесь избегать серверных курсоров, насколько это возможно. Всегда используйте "подход, основанный на выборках" вместо "процедурного подхода" для доступа к данным и их изменения. Часто можно избежать применения курсоров, пользуясь командой SELECT.
Если не получается избежать использования курсора, то используйте цикл WHILE. Я проводил сравнение и пришел к выводу, что цикл WHILE обычно работает быстрее, чем курсор. Но чтобы заменить курсор циклом WHILE, Вам понадобится столбец (первичный или уникальный ключ), чтобы уникально идентифицировать каждую строку. По моему мнению, первичный или уникальный ключ должна иметь каждая таблица. Нажмите на эту ссылку, чтобы увидеть несколько примеров использования цикла WHILE.

По возможности избегайте создавать временные таблицы при обработке данных, потому что создание временных таблиц означает большее количество дисковых операций. Вместо временных таблиц лучше максимально используйте дополнительные возможности SQL, представления, табличные переменные SQL Server 2000 и вторичные таблицы.

Старайтесь избегать использовать специальные символы в начале слова при поиске при помощи оператора LIKE, так как это приводит к сканированию индекса, что противоречит цели создания этого индекса. Следующее выражение приводит к сканированию индекса (index scan), тогда как второе выражение приводит к поиску в индексе (index seek):

SELECT LocationID FROM Locations WHERE Specialities LIKE '%pples' SELECT LocationID FROM Locations WHERE Specialities LIKE 'A%s'

Также избегайте использовать в поиске операторы неравенства <> и NOT, т.к. это тоже приводит к сканированию индекса.

Используйте вторичные таблицы (derived tables) когда только возможно, так как они выполняются быстрее. Обратите внимание на следующий запрос, который выдает вторую наибольшую зарплату из таблицы Employees:


SELECT MIN(Salary) 
FROM Employees
WHERE EmpID IN
(
 SELECT TOP 2 EmpID 
 FROM Employees
 ORDER BY Salary Desc
)

Тот же запрос может быть переписан с использованием вторичной таблицы, как показано ниже, и будет выполняется в 2 раза быстрее, чем предыдущий запрос:


SELECT MIN(Salary)
FROM
(
 SELECT TOP 2 Salary 
 FROM Employees
 ORDER BY Salary DESC
) AS A

Это всего лишь пример, и результаты Ваших запросов могут отличаться в различных ситуациях в зависимости от структуры базы данных, используемых индексов, объема данных, и т.п. Поэтому рассматривайте все возможные варианты написания запроса и используйте наилучший из них.

Когда Вы разрабатываете Вашу базу данных, всегда помните о производительности. Вы едва ли сможете улучшить производительность позже, когда ваша база данных будет находиться в рабочем режиме, т.к. это требует перестройки таблиц и индексов, переписывания запросов, и т.д. Используйте план выполнения запросов в Query Analyzer или команды SHOWPLAN_TEXT или SHOWPLAN_ALL для анализа Ваших запросов. Убедитесь, что Ваши отчеты выполняют поиск по индексу (index seek) вместо сканирования индекса (index scan) или таблицы (table scan).

Сканирование таблицы или индекса - это очень плохо и должно избегаться везде, где это возможно. Создавайте нужные индексы для нужных столбцов.

Добавляйте имя владельца перед именем таблицы, т.к. это повышает читабельность и позволяет избежать путаницы. Microsoft SQL Server Books Online указывает на то, что добавление имени владельца в имя таблицы даже помогает при повторном использовании плана исполнения, увеличивая производительность.

Добавляйте SET NOCOUNT ON в начало ваших SQL скриптов, хранимых процедур и триггеров при их выполнении в рабочем режиме, т.к. это убирает сообщения типа '(1 row(s) affected)' после выполнения команд INSERT, UPDATE, DELETE и SELECT. Это увеличивает производительность хранимых процедур, снижая трафик.

Используйте более читабельные ANSI-Standard объединения (joins) вместо объединений в старом стиле. С ANSI объединениями выражение WHERE используется только для фильтрации данных, тогда как с объединениями в старом стиле выражение WHERE используется и для объединения, и для фильтрации. В первом из следующих двух запросов используется объединение в старом стиле, а во втором - объединение с новым синтаксисом ANSI:


SELECT a.au_id, t.title 
FROM titles t, authors a, titleauthor ta
WHERE
a.au_id = ta.au_id AND
ta.title_id = t.title_id AND 
t.title LIKE '%Computer%'

SELECT a.au_id, t.title
FROM authors a
INNER JOIN
titleauthor ta 
ON
a.au_id = ta.au_id
INNER JOIN
titles t
ON
ta.title_id = t.title_id
WHERE t.title LIKE '%Computer%'

Не указывайте перед именами Ваших хранимых процедур "sp_". Приставка sp_ зарезервирована для системных хранимых процедур, которые поставляются вместе с SQL Server. Если процедура начинается с sp_, то SQL Server в первую очередь пытается найти ее в базе данных master, потом ищет по указанным базе данных или владельцу, после этого ищет среди процедур с владельцем dbo. Поэтому Вы действительно можете сэкономить время на поиске хранимых процедуры, избегая приставки "sp_".
Представления в основном используются, чтобы показать определенные данные определенным пользователям, которым нужны эти данные. Представления также используются для ограничения доступа к базовым таблицам через выдачу прав только на использование представлений. Еще одно полезное свойство представлений - это то, что они упрощают запросы. Заключите Ваши часто используемые сложные объединения и расчеты в представление, чтобы не повторять эти объединения и расчеты во всех Ваших запросах. Вместо этого Вы просто сделаете выборку из представления.

Используйте пользовательские типы данных (User Defined Datatypes), если какой-либо столбец постоянно встречается в таблицах, чтобы тип данных этого столбца совпадал во всех этих таблицах.

Не позволяйте Вашим клиентским приложениям запрашивать данные или манипулировать данными напрямую, используя команды SELECT или INSERT/UPDATE/DELETE. Вместо этого создайте хранимые процедуры и дайте Вашим приложениям доступ к этим процедурам. Это делает политику доступа к данным прозрачной и единой для всех модулей Вашего приложения, и одновременно сосредотачивает бизнес-логику внутри базы данных.

Старайтесь не использовать типы данных TEXT и NTEXT для хранения текстовой информации больших объемов. Тип данных TEXT имеет некоторые врожденные проблемы. Например, Вы не можете напрямую писать или изменять текстовые данные, используя команды INSERT или UPDATE. Вместо Вы вынуждены использовать специальные команды, такие, как READTEXT, WRITETEXT и UPDATETEXT. Существует также множество ошибок, возникающих при репликации таблиц, содержащих столбцы с текстовыми данными. Поэтому если Вам не нужно хранить больше 8 килобайт текста, используйте типы данных CHAR (8000) или VARCHAR(8000).

Если у Вас есть выбор, не храните двоичные или графические файлы (Binary Large Objects - BLOBы) внутри базы данных. Вместо этого храните в базе данных путь к двоичному или графическому файлу, хранящемуся где-нибудь на сервере. Получать эти большие двоичные файлы и манипулировать ими удобнее вне базы данных. В конце концов, база данных вообще не предназначена для хранения файлов.

Используйте тип данных CHAR только для столбцов с ограничением NOT NULL. Если столбец CHAR позволяет хранить значение NULL, то это рассматривается в SQL Server 7.0+ как столбец с фиксированной длиной. Столбец с типом данных CHAR(100), в котором разрешено хранение значений NULL, занимает все 100 байт, что приводит к неоправданной потере дискового пространства. Поэтому используйте в этой ситуации VARCHAR(100). Конечно, столбцы переменной длины обрабатываются несколько дольше, чем столбцы с фиксированной длиной. Для получения оптимального варианта тщательно выбирайте между CHAR и VARCHAR в зависимости от длины данных, которые вы собираетесь хранить.

По возможности избегайте динамических SQL запросов. Динамический SQL медленнее, чем статический SQL, т.к. SQL Server вынужден создавать план исполнения каждый раз во время работы. Можно воспользоваться операторами IF и CASE, чтобы избежать использования динамического SQL. Другим большим недостатком использования динамического SQL является то, что он требует от пользователей иметь прямые права доступа ко всем объектам в динамическом запросе, например, к таблицам и представлениям. Обычно, пользователи имеют доступ к хранимым процедурам, которые ссылаются на таблицы, но не напрямую к этим таблицам. В этом случае динамический SQL не работает. Например, пользователь dSQLuser добавлен в базу данных pubs и имеет доступ к процедуре dSQLproc, но не имеет доступа ни к одной таблице в базе данных pubs. Процедура dSQLproc выполняет команду SELECT на таблице titles, и это работает. Другое выражение в этой процедуре запускает тот же SELECT на таблице titles, используя динамический SQL, и это не удается из-за следующей ошибки:


Server: Msg 229, Level 14, State 5, Line 1
SELECT permission denied on object 'titles', database 'pubs', owner 'dbo'.

Чтобы воспроизвести эту проблему, используйте следующие команды:


sp_addlogin 'dSQLuser'
GO
sp_defaultdb 'dSQLuser', 'pubs'
USE pubs
GO
sp_adduser 'dSQLUser', 'dSQLUser'
GO
CREATE PROC dSQLProc
AS
BEGIN
SELECT * FROM titles WHERE title_id = 'BU1032' --This works
DECLARE @str CHAR(100)
SET @str = 'SELECT * FROM titles WHERE title_id = ''BU1032'''
EXEC (@str) --This fails
END
GO
GRANT EXEC ON dSQLProc TO dSQLuser
GO

Теперь подключитесь к базе данных pubs, используя логин dSQLuser, и выполните процедуру dSQLproc, чтобы увидеть проблему.

Имейте в виду следующие проблемы, когда используете IDENTITY для генерирования первичных ключей. IDENTITY является очень специфической особенностью SQL Server, и у Вас могут быть проблемы при портировании Ваших приложений на другие системы управления базами данных. Столбцы с IDENTITY имеют и другие проблемы. Например, в некоторых случаях столбцы IDENTITY могут выйти за допустимые пределы в зависимости от выбранного типа данных; числа не могут быть автоматически использованы заново после удаления строк; столбцы с IDENTITY и репликация не всегда хорошо работают вместе. Поэтому используйте алгоритм для генерации первичных ключей в приложении или хранимой процедуре вставки. При использовании собственной генерации первичных ключей также возможны проблемы, такие, как одновременное создание одного и того же ключа или переполнение значений. Поэтому выберите из двух вариантов тот, который Вам больше подходит.

Минимизируйте использование NULL, т.к. это часто приводит к проблемам в приложениях, если только эти приложения специально не убирают NULL или не выводят NULL в какой-либо другой форме. Любое выражение, используемое с NULL, дает результат NULL. Функции ISNULL и COALESCE могут помочь в обработке значений NULL. Ниже показан пример, иллюстрирующий проблему:

Взгляните на таблицу Customers, которая хранит имена клиентов, и в которой второе (middle) имя клиента может быть NULL.


CREATE TABLE Customers
(
 FirstName varchar(20),
 MiddleName varchar(20),
 LastName varchar(20)
)

Сейчас добавьте клиента с именем Тони Блэр без указания второго имени:


INSERT INTO Customers
(FirstName, MiddleName, LastName) 
VALUES ('Tony',NULL,'Blair')

Следующая команда SELECT возвращает NULL вместо имени клиента:

SELECT FirstName + ' ' + MiddleName + ' ' + LastName FROM Customers

Чтобы избежать этой проблемы, используйте функцию ISNULL, как показано ниже:

SELECT FirstName + ' ' + ISNULL(MiddleName + ' ','') + LastName FROM Customers

Используйте типы данных Unicode, такие, как NCHAR, NVARCHAR или NTEXT, если Ваша база данных предназначена для хранения не только обычных английских символов, но и для других символов, использующихся в мире. Используйте эти типы данных только тогда, когда они абсолютно необходимы, т.к. они занимают в 2 раза больше места, чем типы данных, отличные от Unicode.

Всегда используйте список столбцов в Ваших командах INSERT. Это помогает избежать проблем, когда меняется структура таблицы (например, когда добавляется или удаляется столбец). Ниже приведен пример, иллюстрирующий проблему.

Возьмем следующую таблицу:


CREATE TABLE EuropeanCountries
(
 CountryID int PRIMARY KEY,
 CountryName varchar(25)
)

Следующая команда INSERT без указания списка столбцов отлично работает:


INSERT INTO EuropeanCountries
VALUES (1, 'Ireland')

Добавим новый столбец в эту таблицу:


ALTER TABLE EuropeanCountries
ADD EuroSupport bit

Выполним команду INSERT, показанную выше. Вы получите ошибку SQL Server:

Server: Msg 213, Level 16, State 4, Line 1 Insert Error: Column name or number of supplied values does not match table definition.

(Название столбца или количество переданных столбцов не соответствует определению таблицы)

Этой проблемы можно избежать, указав в команде INSERT список столбцов, как показано ниже:


INSERT INTO EuropeanCountries
(CountryID, CountryName)
VALUES (1, 'England')

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

Всегда обращайтесь к таблицам в одном и том же порядке во всех Ваших хранимых процедурах и триггерах. Это помогает избежать мертвых блокировок (deadlocks). Также существуют следующие способы избежать мертвых блокировок: Делайте Ваши транзакции максимально короткими. Затрагивайте как можно меньше данных во время транзакций. Никогда не ждите ввода данных пользователем посреди транзакции. Не используйте хинты блокировки данных на высоком уровне или ограничивающие уровни изоляции данных, если только они не являются абсолютно необходимыми. Сделайте в Ваших приложениях обработку мертвых блокировок, чтобы эти приложения могли повторить транзакцию в случае, если выполнение предыдущей транзакции прервалось из-за ошибки 1205. В приложениях немедленно обрабатывайте все результаты, возвращаемые SQL Server, чтобы снять все блокировки с обработанных записей.

Выведите такие задачи, как манипуляции со строками, сложения строк, подсчет количества записей, изменения регистра, изменения типов, и т.д., в приложения, если эти операции потребляют больше ресурсов ЦП на сервере баз данных. Также старайтесь делать первичную проверку данных в приложениях во время ввода данных. Это уменьшает сетевой трафик.
Если Вашей системе предстоит работать с разными системами управления базами данных, то избегайте работать с битами в T-SQL, т.к. такие функции очень специфичны в каждой системе управления базами данных. Более того, использование битовых масок для хранения различных состояний определенного объекта противоречит правилам нормализации.

Всегда добавляйте параметр @Debug в Ваши хранимые процедуры. Этот параметр может быть типа BIT. Если в этот параметр передается значение 1, то выводите все промежуточные результаты и содержимое переменых, используя SELECT или PRINT, а если передается 0 - ничего не выводите. Это помогает быстро отлаживать хранимые процедуры, т.к. Вам не нужно добавлять и удалять команды PRINT/SELECT до и после возникновения проблем.

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

Убедитесь, что Ваши хранимые процедуры всегда возвращают значение, показывающее их статус. Стандартизируйте возвращаемые хранимыми процедурами значения для успешного и неуспешного завершения работы процедуры. RETURN должен возвращать только статус выполнения, но не данные. Если Вам нужно возвратить данные, используйте параметры OUTPUT.

Если Ваша хранимая процедура всегда возвращает результат в виде однострочного набора данных, то лучше возвращайте данные, используя параметры OUTPUT вместо SELECT, т.к. ADO обрабатывает возвращаемые параметры быстрее, чем наборы данных, возвращаемые командой SELECT.

Всегда проверяйте значение глобальной переменной @@ERROR сразу после выполнения команд манипулирования данными (таких, как INSERT/UPDATE/DELETE), так, чтобы Вы могли откатить транзакцию в случае возникновения ошибки (в случае ошибки значение @@ERROR будет больше 0). Это важно, потому что по умолчанию SQL Server не откатывает все предыдущие изменения в транзакции, если какая-либо команда вызовет ошибку. Такое поведение может быть изменено с помощью выполнения SET XACT_ABORT ON. Переменная @@ROWCOUNT также играет важную роль в определении количества строк, обработанных в предыдущей команде манипулирования данными или выборки данных, на основе чего можно определить, нужно ли выполнить или откатить транзакцию.

Чтобы сделать команды SQL более читабельными, начинайте каждое выражение с новой строки и делайте отступы, когда требуется. Ниже показан пример такого подхода:


SELECT title_id, title
FROM titles
WHERE title LIKE '%Computer%' AND
    title LIKE '%cook%'

Хотя мы и пережили проблему 2000 года, всегда храните 4-значное значение года в датах (особенно, когда используете столбцы типов cCHAR или INT), а не 2-значное, чтобы избежать путаницы и проблем. Это не является проблемой для столбцов типа DATETIME, т.к. век хранится, даже если вы укажете 2 знака у года. Но всегда лучше указывать 4 знака даже для столбцов типа DATETIME.

Как и во всех языках программирования, не используйте команду GOTO, или используйте ее редко. Чрезмерное использование GOTO может превратить код в трудно читаемый и трудно понимаемый.

Не забывайте создавать ограничения на уникальность на Ваших альтернативных ключах.

Всегда будьте последовательны в использовании верхнего и нижнего регистров в Вашем коде. Если сервер не чувствителен к регистру, то Ваш код будет работать, но может не заработать на сервере, чувствительном к регистру, если в Вашем коде нет последовательности в использовании регистра. Например, если вы создаете таблицу (или базу данных) в SQL Server, которая имеет регистро-зависимый или двоичный порядок сортировки, то все ссылки на эту таблицу должны использовать тот же регистр, который использовался в команде CREATE TABLE. Если в команде CREATE TABLE вы назвали таблицу 'MyTable', а в команде SELECT используете 'mytable', то Вы получите ошибку 'object not found'.

Хотя в языке T-SQL нет понятия констант (как, например, в языке C), переменные могут выполнять те же функции. Использование в Ваших запросах переменных вместо конкретных значений повышает читабельность и сопровождение Вашего кода. Взгляните на следующий пример:


SELECT OrderID, OrderDate
FROM Orders
WHERE OrderStatus IN (5,6)

Этот же запрос может быть переписан в более читабельный вид, как показано ниже:


DECLARE @ORDER_DELIVERED, @ORDER_PENDING
SELECT @ORDER_DELIVERED = 5, @ORDER_PENDING = 6

SELECT OrderID, OrderDate
FROM Orders
WHERE OrderStatus IN (@ORDER_DELIVERED, @ORDER_PENDING)

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


SELECT OrderID, OrderDate
FROM Orders
ORDER BY 2

SELECT OrderID, OrderDate
FROM Orders
ORDER BY OrderDate

Ну вот и все, друзья. Я буду обновлять эту статью, когда у меня будет что добавить нового. Мне будут интересны ваши отзывы на нее, так что можете посылать их на мой email. Удачного вам программирования баз данных!

[В начало]