Code Style SQL

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

1. НАИМЕНОВАНИЯ ТАБЛИЦ.

Именование таблиц должно подчиняться следующим правилам:

Имен таблицы не имеющей родительской таблицы должно отражать содержимое таблицы.

Пример:

• enterprise (юридические лица);
• people (физические лица);
• сlient (Корреспонденты);
• credit (Документ «Кредиты»);

Имя дочерней таблицы формируется так: имя_родительской_таблицы_имя_дочерней_таблицы. Дочерняя - та, из которой, при удалении записи в родительской таблице, удаляются все записи имеющие ссылку на удаляемую запись в родительской таблице.

Пример:

• client_account (Счета корреспондентов);
• client_personnel (Сотрудники корреспондентов)
• credit_penalty (Пени по кредиту);
• credit_percent (Проценты по кредиту)

Имя дочерней таблицы имеющей строчную часть должна содержать потфикс row: имя_родительской_таблицы_имя_дочерней_таблицы_row.

Пример:

• credit_row (Строчная часть документа);

2. НАИМЕНОВАНИЯ ПОЛЕЙ ТАБЛИЦ.

Любая таблица должна иметь первичный ключ — автоинкрементное поле с именем «id».

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

• Имя поля не должно быть в списке зарезервированных слов
• «Имя поля простого типа» — должно отражать содержимое поля.
• Поле принимающие значения 1 или 0 формируется так: имя_поля_flag. При этом имя_поля должно отражать содержимое поля, при состоянии флага равному 1.
• Поле принимающее значения из определенного списка, выглядят так: имя_поля_(type/status/other)
• «Имя поля ссылочного типа» формируется так: имя_таблицы_на_которую_ссылается_данное_поле_имя_поля_на_которое_ссылается_данное_поле. Речь идет о поле в дочерней таблице, которое ссылается на поле - внешний ключ (например поле ограничением целостности «On Delete = Cascade»).

Пример:

• payment_confirmed_flag (флаг 1 = утвержден, 0 = не утвержден);
• client_type (тип клиента, значение из определенного списка);
• client_id (FK на таблицу клиентов);

Перечень имен полей для обязательного использования, при совпадении преследуемой логики:

• parent_id — поле ссылающееся на поле id в текущей таблице
• guid — глобальный уникальный идентификатор
• comment — комментарий
• title — наименование
• short_name — сокращенное наименование (псевдоним)
• full_name — полное наименование
• doc_date — дата документа
• doc_num — номер документа
• doc_status — состояние документа
• created_at — дата создания (появления) строки в таблице
• updated_at — дата последнего обновления строки в таблице
• started_at — с какого момента времени данная строка таблицы является активной (например дата утверждения документа)
• finished_at — по какой момент времени данная строка таблицы является активной (например дата, когда пользователь более не активен)
• mult — коэффициент
• quantity — количество чего-либо, но для денег есть:
• money_amount — количество денег (например для траты в текущем месяце)
• money_total — количество денег (например оставшихся у клиента)
• price — цена товара
• cost — стоимость покупки (например всех товаров, которые в корзине)
• rate — ставка
• priority — приоритет строки по отношению к другой строке (если в таблице уже есть такое поле, но бизнес-логика отличается от необходимой, то создается новое поле с суффиксом _for_, например: priority_for_show). Следует использовать это имя, вместо зарезервированного имени position.

3. НАИМЕНОВАНИЯ ИНДЕКСОВ.

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

«Имя индекса» «Префикс индекса» + «_» + наименование таблицы + «$» + перечисление имен полей таблицы, участвующих в построении индекса, разделенных символом «$».

«Имя индекса» - можно не указывать (необязательное требование)

«Префикс индекса» может принимать одно из следующих значений:

• in обычный индекс;
• iu уникальный индекс.

Пример:

iu_credit_penalty$owner_id$doc_num (уникальный индекс в таблице «credit_penalty» по полям «owner_id» + «doc_num»)

4. НАИМЕНОВАНИЯ ОГРАНИЧЕНИЙ ЦЕЛОСТНОСТИ.

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

«Префикс ограничения целостности» + «_» + наименование таблицы + «$» + перечисление имен полей таблицы, участвующих в построении индекса, разделенных символом «$».

«Префикс ограничения целостности» может принимать одно из следующих значений:

• pk это PRIMARY KEY
• fk это FOREIGN KEY
• ck это CHECK KEY
• uk это UNIQUE KEY
• dk это DEFAULT KEY

Например внешний ключ:

fk_User$id__Post$userId

Сформирован по формуле:

fk_ + ИмяТекущейТаблицы[$ИмяПоляТекущейТаблицы[$...]]  + __ +  ИмяВнешнейТаблицы[$ИмяПоляВнешнейТаблицы[$...]]

Приведенный выше пример может быть аналогичен следующему:

fk_User__Post

но только, если является единственным по отношению к Внешней таблице.

5. НАИМЕНОВАНИЯ ТРИГГЕРОВ ТАБЛИЦ.

Именование триггера таблицы должно подчиняться следующим правилам: «t» + «Префикс типа триггера» + «_» + «наименование таблицы» + при необходимости «бизнес-описание».

«Префикс типа триггера» может иметь длину от двух до четырех символов.

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

• a «after» триггер, срабатывает после изменения в основной таблице;
• b «before» триггер, срабатывает перед изменением в основной таблице;
• i «instead of» триггер, заменяет собой SQL выполняемый над основной таблицей.

Второй и (для комбинированного триггера) последующие символы — опциональные, это комбинация в алфавитном порядке:

• d «delete» триггер;
• i «insert» триггер;
• u «update» триггер.

Рекомендации:

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

Пример:

• tai_client («after-insert» триггер по таблице «client»);
• tbu_client («before-update» триггер по таблице «client»);
• tadiu_client («after-delete, insert, update» триггер по таблице «client»)
• tbu_client_update_first_name_and_middle_name («before-update» триггер по таблице «client» который обновляет поля first_name, middle_name);
• tau_section_update_documents : section - таблица в которой срабатывает триггер, update_documents - то, что делает триггер (обновляет документы при изменении раздела на сайте), au - триггер запустится после того, как будет обновлена строка в таблице section

6. НАИМЕНОВАНИЕ ПЕРЕМЕННЫХ

Имя переменной языка “Transact SQL” == «Имя переменной», где «Типизированный префикс» имеет следующие значения:

Имя переменной языка “Transact SQL” == «Имя переменной», где «Типизированный префикс» имеет следующие значения:
•	@vc 	== varchar
•	@ch 	== char
•	@i 	== int, tinyint
•	@dt	== datetime, smalldatetime
•	@f 	== float
•	@m 	== money
•	@cur	== cursor
•	@tbl 	== table

Ниже описаны примеры того, как делать не нужно, с объяснением, почему этого следует придерживаться.

Не используйте имя б.д.

Мы используем три имени баз данных: OrdersFromCACHE, OrdersFromCACHE_test, OrdersFromCACHE_dev, поэтому, если в миграции будет указано имя, то такая миграция не сработает на проде. Пример неправильного запроса:

SELECT * INTO table_2 FROM OrdersFromCACHE_dev.dbo.table_1

Производительность

В данном параграфе объясняются неочевидные моменты производительности.

COLLATE

COLLATE приводит к ухудшению производительности запросов. От COLLATE необходимо избавляться:

COLLATE DATABASE_DEFAULT

Если Вашему запросу необходимо написать COLLATE чтобы он работал - имейте в виду, вы делаете что-то неправильно.

Табличные переменные

В запросах и хранимых процедурах не используйте табличные переменные:

DECLARE @permanent TABLE(serv_id VARCHAR(255), doc_id INT)

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

SELECT field1, field2
INTO #tmp_table
FROM table

Плохой SQL

Ниже представлен пример плохого SQL-запроса

-- абстрактное имя результирующего набора, правильней было бы: base_price
WITH t1 as (
-- Подобный запрос может быть переиспользован, поэтому мы в команде поступаем так:
-- 1. создаем PHP-функцию функцию, которая возвращает SQL
-- 2. создаем PHP-функцию функцию, которая возвращает значение используя функцию 1
    SELECT
      top 1 mpb.price_id
    FROM
        [dbo].[med_PriceBind] mpb
    WHERE mpb.date_start < @fr_price_date
        AND mpb.office_id = @branch_id
    ORDER BY mpb.date_start DESC
)
SELECT
-- каждое поле должно быть объявлено в отдельной строке + должен быть отступ слева (4 пробела)
e.DateReg, p.MobileNumber,
count(1)-- не используйте указание поля по номеру (это плохо читается и путает разработчиков незнакомых с данной фичей)
,full_name_weight+partial_name_weight-- отсутствие пробела вокруг плюса, делает код трудночитаемым
-- нельзя делать подзапросы в SELECT-e, это сильно влияет на производительность (тут нужно было сделать JOIN):
(SELECT id FROM other_table WHERE other_table.order_num = oe.order_num) AS ot_id
-- сокращение должно быть по заглавным буквам имени таблицы (правильно: ote)
FROM OrdersToExport oe
-- использование круглых скобок без необходимости
INNER JOIN Patients p ON (p.AID = oe.PatID)
INNER JOIN t1 ON t1.price_id = oe.base_price_id
-- поле order_num является строковым, использование числовых сравнений к строковым, не применяет индекс
-- второе условие выборки должно быть перенесено на след. строку (все что начинается с AND, включая AND)
WHERE oe.order_num = 12345678 AND oe.Status = 1
AND (-- очень странно видеть select в WHERE, тут явно надо было использовать LEFT или INNER JOIN
    select sum(mad.total_cost)
    from med_appointmentdetail mad
    where mad.order_id = oe.order_num
) = :orderVal-- имя параметра очень странное, совсем не совпадает с именем параметра в таблице