«Успешные люди делают то, что неуспешные не хотят делать. Не стремитесь, чтобы было легче, стремитесь, чтобы было лучше».
Джим Рон
Пришло время поговорить о том, как правильно писать код на языке T-SQL. Если в предыдущих разделах мы учились правильно оформлять код, то в этом разделе представлены правила, которые помогут Вам правильно конструировать запросы и инструкции на T-SQL.
Если код будет хорошо оформлен, но сам по себе он написан плохо, его все равно будет достаточно сложно понять и сопровождать.
В этом разделе я дам Вам простые советы для правильного написания кода, чтобы его сопровождение не вызывало у Вас затруднения.
Ставьте точку с запятой в конце инструкции
В языке T-SQL ставить точку с запятой в конце инструкции необязательно, это требуется только в некоторых конструкциях, таких как MERGE или перед WITH. Но чтобы сделать Ваш код на T-SQL более понятным, чтобы было без каких-либо проблем сразу видно, где начало и где конец той или иной инструкции, необходимо всегда явно отделять одну инструкцию от другой точкой с запятой (;).
В языке T-SQL ставить точку с запятой в конце - это правило хорошего тона! К тому же это действительно избавляет Ваш код от возможных проблем с компиляцией. Если Вы всегда ставите точку с запятой, с подобными проблемами Вы никогда не столкнётесь.
Даже простой SQL запрос заканчивайте точкой с запятой
SELECT ProductId, ProductName
FROM Goods;При перечислении столбцов не используйте *
Всем известно, что в SQL, для того чтобы в запросе вывести все столбцы из источника, вместо перечисления столбцов можно использовать символ звездочки *. Но в действующих приложениях или процедурах так делать ни в коем случае нельзя, обязательно перечисляйте все столбцы вручную.
Использование символа звездочки, во-первых, снижает производительность запросов, а во-вторых, это создает благоприятные условия для возникновения ошибок, и, в-третьих, затрудняет чтение кода, так как мы просто не знаем, какие именно столбцы задействованы в этом запросе.
Например, если использовать * для запросов, которые возвращают данные для каких-то дальнейших расчетов, изменение столбцов в источнике, допустим для каких-то других задач, может вызвать ошибку в работе такого процесса, а в случае передачи конкретных столбцов этого можно избежать.
Не надо писать так
SELECT * FROM Goods;Лучше перечисляйте столбцы, которые будут задействованы, в таком случае оптимизатор построит и выберет оптимальный план выполнения запроса.
SELECT ProductId, ProductName
FROM Goods;Исключение.
Звездочку допускается использовать только в текстовых запросах, когда для разработки запроса или инструкции Вам необходимо посмотреть на фактические данные.
Для определения псевдонимов используйте ключевое слово AS
Абсолютно все используют псевдонимы (Alias) для определения столбцов в SELECT, а также для источников данных в секции FROM, но мало кто для этого использует ключевое слово AS, так как оно необязательно.
Но использование ключевого слова AS для псевдонимов придает ясность и очевидность SQL запросу, его определение станет наглядным.
В следующем примере за названием столбца сразу идет псевдоним, и, если таких столбцов будет много, можно и запутаться, где псевдоним, а где название столбца.
SELECT ProductId ProdId, ProductName ProdName
FROM Goods;Если указать ключевое слово AS, то становится сразу понятно, что где.
SELECT ProductId AS ProdId,
ProductName AS ProdName
FROM Goods;Поэтому рекомендуется использовать ключевое слово AS для псевдонимов, пусть оно и необязательно, но оно увеличивает читабельность SQL запроса, а как результат, снижает сложность, что собственно нам и нужно. Это еще одно правило хорошего тона при работе с T-SQL!
Не ставьте скобки в условии там, где этого делать не нужно
Скобки в секции WHERE требуются для логического разделения предикатов, но в то же время можно заключать все предикаты в скобки, однако так делать не рекомендую, ведь когда много скобок, можно просто запутаться в них. Поэтому ставьте скобки только там, где это требуется.
Например, в следующем запросе они точно не нужны
SELECT ProductId, Category
FROM dbo.Goods
WHERE (Price > 100)
AND (Category = 1);А вот в этом запросе они, наоборот, повышают читабельность, так как логически разделяют предикаты
SELECT ProductId, Category
FROM dbo.Goods
WHERE (Price > 100 AND Category = 1)
OR Category = 2;
Избегайте «магических чисел»
К магическим числам можно отнести обычные числа, которые встречаются в Ваших T-SQL инструкциях, например, 100, 204 или 1986 без каких-либо объяснений.
Например
DECLARE @NumRow INT = 1;
WHILE @NumRow <= 123
BEGIN
INSERT INTO Goods (ProductName, Price)
VALUES (ProductName, Price);
SET @NumRow = @NumRow + 1;
ENDТеперь скажите, что такое 123? Понятно, что цикл будет выполняться указанное количество раз, но зачем? Почему именно 123? Я думаю, все программисты, которые впервые будут читать этот код, не поймут, что это за число. Поэтому такие случаи допускать нельзя.
Чтобы улучшить конкретно этот код, необходимо объявить переменную и использовать в цикле именно ее, при этом использовать понятное имя переменной, и прокомментировать ее назначение. Иными словами, все подобные «магические числа» нужно комментировать.
В следующем примере мы уже видим, для чего нужно число 123.
DECLARE @NumProduct INT = 1,
@TotalProductsGroup INT = 123; --Всего товаров в группе для добавления
WHILE @NumProduct <= @TotalProductsGroup
BEGIN
INSERT INTO Goods (ProductName, Price)
VALUES (ProductName, Price i;
SET @NumProduct = @NumProduct + 1;
ENDТакже, в случаях если одно и то же магическое число, без использования специальной переменной, будет использоваться и дальше по коду, при необходимости его изменить Вам придётся делать изменения в нескольких местах, что также добавляет определенные трудности. Например, Вы должны знать, в каких именно местах нужно вносить изменения. Если Вы просто забудете изменить это число в одном месте, или измените какое-нибудь число по ошибке, Ваша инструкция будет работать неправильно, а Вы об этом узнаете не сразу.
Таким образом, исключение магических чисел дает несколько преимуществ:
> Улучшение читабельности;
> Проще вносить изменения. Если использовать переменные, Вам достаточно изменить переменную в одном месте, и не нужно знать, где она используется дальше по коду;
> Повышение надежности. Использование переменных вместо магических чисел снижает вероятность появления ошибок, так как Вы теперь не измените по ошибке какое-нибудь число, которое изменять не следовало бы.
Избегайте «магических строк»
К магическим строкам относятся любые литеральные строки, например, «Отдел информационных технологий» или любые другие подобные строки, которые часто используются, как текст сообщений. В любое время такая литеральная строка может измениться, и Вам придётся во всех местах, где используется эта строка, вносить изменения. Поэтому такие строки нужно также исключать из своих инструкций, и создавать для них отдельные переменные, чтобы не было разбросанных по всему коду различных текстовых строк.
Например, не нужно писать так
IF @DepartmentName = 'Отдел информационных технологий'
PRINT 'Какой-то код';Лучше создайте отдельную переменную
DECLARE @EnteredDepartmentName VARCHAR(100) = 'Отдел информационных технологий';
IF @DepartmentName = @EnteredDepartmentName
PRINT 'Какой-то код';Кроме удобства изменения таких строк, Вы еще можете сократить занимаемое ими место в коде. Например, если в нескольких местах встречается одна и та же строка, скажем на 100 символов, заменив ее на название переменной, Ваш код станет заметно меньше и приобретет более читабельный вид.
Переменные следует инициализировать и использовать сразу
Когда Вы будете использовать переменные в своих инструкциях, сразу после объявления инициализируйте их. Язык T-SQL поддерживает конструкцию, которая позволяет одновременное объявление переменной и ее инициализацию. Иными словами, задавайте всем переменным значения по умолчанию, даже если дальше по коду этой переменной будет присвоено какое-нибудь расчётное значение, это позволит Вам избежать значения NULL в переменных и непредвиденного результата.
Также стоит располагать инструкцию объявления переменной непосредственно перед ее использованием, это убережёт Вас от случайного, неправомерного изменения переменной в промежутке между ее инициализацией и использованием. Даже если очевидно, что код ничего не сделает с переменной в этом промежутке, все равно сократите разрыв между местом объявления переменной и местом ее использования до минимального, ведь не факт, что в дальнейшем, спустя время, в процессе редактирования кода Вы или кто-то другой не переопределите переменную по каким-нибудь причинам, что сделает алгоритм всех действий ошибочным.
Пример плохого использования переменных, как делает большинство программистов.
DECLARE @DepartmentId INT;
--Много кода
SET @DepartmentId = 1;
--Много кода
IF @DepartmentId = 1
PRINT 'Использование переменной';Пример хорошего использования переменных, где переменные сразу инициализируются и используются.
--Много кода
DECLARE @DepartmentId INT = 1;
IF @DepartmentId = 1
PRINT 'Использование переменной';
--Много кодаВо втором случае сразу видно, каким значением инициализирована переменная, если ее необходимо переопределить, то код, который будет присваивать ей новое значение, необходимо располагать после инициализации и перед непосредственным использованием.
В самом начале своей работы с языком T-SQL, я думал, что будет удобней объявлять все переменные в одном месте в самом начале процедуры. Но как оказалось, такой подход в дальнейшем усложнял весь процесс сопровождения этой процедуры, ведь в случае внесения изменений в алгоритм, мне приходилось каждый раз перемещать глаза вверх (а если процедура большая, то и делать прокрутку), в начало процедуры, чтобы посмотреть, каким именно значением у меня инициализирована переменная, далее, просматривать весь последующий код, чтобы убедиться, что значение этой переменной не было изменено. Что лично мне было крайне неудобно, поэтому после мне пришлось осуществлять рефакторинг кода таких процедур.
Число параметров процедуры не должно превышать 7
Процедура или функция должна содержать не более 7 параметров. Почему именно 7? Потому что уже достаточно давно ученые проводили исследования, которые показали, что человек, как правило, не может следить более чем за семью элементами информации сразу. Поэтому если в процедуре будет более 7 параметров, Вы просто не сможете удержать их в голове одновременно, тем самым повысится сложность всей процедуры. Существуют, конечно же, исключения и среди людей, которые могут держать в голове больше семи элементов, и среди процедур, в которые, как ни крути, нужно передавать больше семи параметров, но в большинстве случаев семь параметров более чем достаточно, поэтому я рекомендую конструировать свои процедуры так, чтобы они принимали не более 7 параметров.
Передавайте параметры в процедуры по названию параметра, не опираясь на их последовательность
Многие передают параметры в процедуры, просто перечисляя их в определенной последовательности, т.е. так как они объявлены.
Вот как выглядит типичный вызов процедуры
EXEC Add_NewProduct 'Монитор', 2, 8000Программист, который не знает последовательность параметров этой процедуры, сразу и не определит, для чего предназначено каждое из этих значений.
Поэтому обязательно указывайте название параметров при вызове процедуры, особенно если параметров в хранимой процедуре достаточно много, и некоторые из них являются необязательными, т.е. их можно и не передавать, иными словами, не опирайтесь только на последовательность, во-первых, так код становится непонятным, во-вторых, Вы можете ошибиться в последовательности и передать значения параметру, которое предназначается совершенно для другого параметра.
Например
EXEC Add_NewProduct @ProductName = 'Монитор',
@CategoryId = 2,
@Price = 8000Вот еще одна причина, почему нужно вытягивать передачу параметров в процедуры по вертикали, об этом правиле я говорил в разделе про оформление кода, так как в таких случаях мы четко видим каждый параметр и его значение.
Исключение.
Если параметров всего 1 или 2, и они очевидные, то можно название параметров и не указывать, а использовать последовательность передачи значений.
Минимизируйте использование типов данных REAL и FLOAT
REAL и FLOAT - это приблизительные числа, за счет этого, когда Вам придётся суммировать, округлять и сравнивать подобные числа, у Вас будут возникать расхождения, поэтому крайне не рекомендуется использовать REAL и FLOAT в финансовых приложениях, в подобных случаях лучше использовать NUMERIC или DECIMAL, MONEY или SMALLMONEY.
Используйте максимально компактные конструкции
Для повышения удобочитаемости своих инструкций используйте максимально компактные конструкции, ведь абсолютно эквивалентный алгоритм действий в T-SQL можно записать по-разному.
Так, например, в условии не нужно использовать много операторов OR, лучше используйте один IN.
Не нужно писать так
SELECT ProductId, ProductName
FROM Goods
WHERE Price = 100 OR Price = 200;Лучше пишите вот так
SELECT ProductId, ProductName
FROM Goods
WHERE Price IN (100, 200);Как видите, результат один, но во втором случае нам не нужно читать еще одно дополнительное условие.
Также стоит использовать вместо нескольких операторов AND один BETWEEN, например, в следующей инструкции условие можно и нужно заменить.
SELECT ProductId, ProductName
FROM Goods
WHERE Price >= 100 AND Price <= 200;На один предикат BETWEEN.
SELECT ProductId, ProductName
FROM Goods
WHERE Price BETWEEN 100 AND 200;
Все это поможет Вам писать компактный код, который будет удобно читать и изменять.
Используйте комментарии
Всегда комментируйте свои SQL инструкции там, где у Вас или у другого программиста, который будет читать эти инструкции, могут возникнуть уточняющие вопросы. Никаких неясностей в коде быть не должно!
Как ставятся комментарии, я думаю, Вы знаете. Если нужно поставить однострочный комментарий, используйте два символа тире --, если многострочный, то конструкцию /*текст*/.
Например
DECLARE @ProductId INT = 1; --Однострочный комментарий
IF @ProductId = 1
BEGIN
/*
Многострочный комментарий
*/
PRINT 'Первое условие';
ENDИспользуйте хранимые процедуры для взаимодействия с внешним приложением
Для взаимодействия клиентского приложения и базы данных минимизируйте использование обычных прямых инструкций, даже если эти инструкции очевидны. Для этого Вам нужно разработчикам клиентского приложения давать только название процедуры, описание, для чего она нужна, и описание параметров, а если Вы сами этот разработчик, то также используйте только хранимые процедуры.
Тем самым у Вас происходит сокрытие информации. Вы скрываете детали реализации, и разработчик клиентского приложения не знает, что там делает эта процедура, ему это и не надо, и, соответственно, он не сможет внести неправомерные изменения в инструкции, тем более что для них самих использование процедур также является предпочтительней, т.е. им легче программировать, так у них формируется полноценная абстракция их классов, в случае с ООП, а также происходит предотвращение дублирования кода, ведь им не нужно в нескольких местах писать одинаковые SQL инструкции, они могут просто вызвать процедуру.
Поэтому рекомендовано использовать подход с использованием хранимых процедур, так у Вас будет несколько преимуществ:
> Повышенная безопасность - тот, кто будет использовать хранимую процедуру, сможет делать только то, что ему разрешено;
> Сокрытие сложности - тот, кто будет использовать хранимую процедуру, не будет знать, насколько сложно реализована конкретная SQL инструкция;
> Легкое управление - для того чтобы внести изменения в алгоритм выполняемой инструкции, Вам не нужно вносить изменения в клиентское приложение (перекомпилировать и так далее), Вам достаточно внести изменение в хранимую процедуру, а пользователи этой процедуры вполне возможно и не узнают, что она была изменена;
> Плюсы для клиентских разработчиков (формирование абстракции, предотвращение дублирования кода);
> Уменьшение сетевого трафика - текст вызова хранимой процедуры значительно меньше текста всей инструкции, которая реализует алгоритм, поэтому пусть и немного, но Вы снизите объем сетевого трафика.
Используйте представления
Продолжая разговор о повышении безопасности, сокрытии информации и сложности, можно добавить, что представления также относятся к эффективному инструменту, который помогает достичь этих целей. Иными словами, используйте представления, у них также есть определенные преимущества, а именно:
> Сокрытие сложности реализации задачи от пользователя;
> Обеспечение эффективных путей доступа к данным;
> Обеспечение корректности производных данных;
> Более легкое управление. Чтобы внести изменения в алгоритм, формирующий данные, которые возвращает представление, не требуется изменять код везде, где используется этот алгоритм, достаточно изменить код в одном определении представления.
Минимизируйте использование курсоров
Если есть возможность выполнить задачу или реализовать алгоритм без использования курсоров, то делайте это без курсоров. Курсоры используйте только в тех случаях, когда другого решения у Вас нет.
В реляционных базах данных мы работаем со множеством, поэтому большинство задач в SQL можно решить, нацеливая свои инструкции на множество, не прибегая к перебору каждой строки данных, что собственно и делает курсор.
Если в базе данных приложения будет задействовано слишком много курсоров, это заметно замедлит работу всего приложения. Иными словами, используйте курсоры только в самых крайних случаях.
Динамический код используйте с осторожностью
В T-SQL есть возможность формировать динамический код, т.е. инструкции, которые будут сформированы в процессе выполнения всего кода. Это полезно в тех случаях, когда мы заранее не знаем определенные параметры, скажем, их количество, как в случае с динамическим PIVOT. Но такой код снижает безопасность приложения. В такой динамический код можно легко встроить вредоносный код, это называется «SQL-инъекцией».
Поэтому ни в коем случае не выполняйте динамический код напрямую из приложения! А во все инструкции, хранящиеся в базе данных и содержащие динамический код (от него никуда не денешься), добавляйте проверку входящих параметров.
Хранимая процедура должна выполнять одну конкретную задачу
Разбивайте процедуры, которые выполняют много различных (не связанных операций) на несколько. Процедура должна выполнять конкретную задачу, а ее имя должно четко описывать эту задачу.
При разработке процедуры добивайтесь наилучшей связности, а таковой является функциональная связность, она предполагает решение одной и только одной задачи.
Если цель Вашего алгоритма - какая-то одна глобальная задача, которая содержит много различных операций, выносите их в отдельные процедуры и функции, а в основной процедуре просто вызывайте их. Тем самым Вы снижаете сложность отдельно взятых процедур и всего алгоритма. Напомню, что главной целью хорошего стиля программирования является как раз снижение сложности!
При этом плодить ненужные функции и процедуры не стоит, если их будет слишком много, то спустя время Вы просто забудете, что каждая из них делает, и для чего создавалась.
Тип данных значения по умолчанию должен совпадать с типом данных столбца
Это означает, что, когда Вы определяете значение по умолчанию, нужно указывать такое значение, которое соответствует типу данных столбца, для которого Вы и создаете это значение по умолчанию. С первого взгляда очевидно, но многие так не делают, даже я когда-то так не делал. Самый простой пример, у столбца тип данных FLOAT, т.е. с плавающей точкой, а в значении по умолчании указано целочисленное значение, например, 0, ошибки-то не возникнет, но SQL Server каждый раз будет выполнять неявное преобразование типа данных, что добавляет пусть и незначительные, но лишние расходы.
У таблицы должен быть первичный ключ
Таблица, у которой нет ключа, называется кучей, ведь данные в ней лежат просто в куче, без какого-либо порядка и идентификации. С такими таблицами очень сложно работать, поэтому по возможности создавайте первичные ключи для всех таблиц, если не существует веских оснований, чтобы оставить таблицу в виде кучи.
Здесь возникает один очень серьезный, дискуссионный вопрос - какой ключ использовать в виде первичного ключа?
Этот вопрос относится скорей к проектированию базы данных, но мне все равно хочется высказаться по этому поводу.
Самым лучшим ключом, конечно же, является естественный ключ, но использовать такие ключи в современном мире становиться все трудней, поэтому самым эффективным способом замены естественных ключей является использование целочисленных, так называемых суррогатных, ключей с автоинкрементом, т.е. указанным свойством IDENTITY.
Джо Селко в уже вышеупомянутой книге очень резко отнесся к такому подходу, называя таких программистов id-иотами, обосновывая все это тем, что такая привычка пришла из времен работы с магнитными лентами, и что таким программистам просто хочется знать, в каком порядке строки добавлялись в таблицу, т.е. точно так же, как раньше был важен порядок добавления записей в конец магнитной ленты. Но, к сожалению, я не работал с магнитными лентами, и мне не нужно знать, в каком порядке строки добавляются в БД, мне нужно обеспечить таблицу уникальным ключом, который бы автоматически генерировался, и его можно было эффективно использовать в своих инструкциях. Самый лучший вариант, который обеспечит максимальную производительность, — это столбец с целочисленным типом данных, у которого есть свойство IDENTITY.
Вводить естественные ключи имеет место тогда, когда они ни при каких обстоятельствах не будут изменены и будут уникальны на протяжении всего своего существования, до непосредственного удаления. Например, какие-то внешние, общепринятые коды или идентификаторы, включая лицевые счета, в этом случае - да, лучше в качестве первичного ключа использовать этот естественный ключ.
Но определять естественные ключи для внешних неуникальных самих по себе данных, которые будут снижать производительность, и которые будет просто неудобно использовать в разработке, я думаю не целесообразно, в этом случае я считаю, лучше выбрать меньшее зло, т.е. какой-то суррогатный идентификатор. При этом ограничения накладывать с помощью соответствующих объектов БД.
Тот же самый Селко показывает примеры, где использование естественного ключа предпочтительней, но таблицы, на которых показан пример (пример, связанный с футбольной командой) просто неправильно спроектированы, т.е. там нарушение третьей нормальной формы, так как атрибут дублируется в нескольких таблицах. Также в этих примерах предполагается, что ключом будет выступать номер социального страхования, при этом этот номер может измениться, так зачем его использовать в качестве первичного ключа? При изменении первичного ключа Вы просто теряете всю историю, и чтобы ее реализовать, потребуется создание какого-то неудобного функционала.
Дискуссия по поводу, какие ключи использовать, естественные или суррогатные, ведется уже давно, т.е. единого мнения по этому поводу нет. Я же считаю, что использовать естественные ключи — это, конечно же, хорошо, но только в определённых случаях, а в большинстве случаев суррогатные ключи предпочтительней.
Делайте проверки на ноль
Как Вы знаете, делить на ноль нельзя. В T-SQL, если поделить на ноль, возникнет ошибка, и прервёт выполнение инструкции. Поэтому каждый раз, когда Вы будете использовать операцию деления, анализируйте знаменатель, и, если по каким-либо не ведомым причинам там может оказаться 0, обязательно делайте соответствующие проверки. Это могут быть проверки, которые в случае обнаружения ошибки, например, продолжат операцию со значением по умолчанию, если это возможно, или переведут инструкцию в другое направление, допустим с целью повторного определения значения знаменателя .
Минимизируйте использование подсказок оптимизатору
В языке T-SQL есть возможность явно управлять планом запроса, прибегая к так называемым подсказкам оптимизатору, т.е. указывать hints или хинты. Это полезно в тех случаях, когда оптимизатор использует неоптимальный план выполнения запроса, но так происходит очень редко.
Такие хинты поддерживают многие конструкции в T-SQL, но их стоит использовать только в самых крайних случаях, так как оптимизатор запросов Microsoft SQL Server обычно выбирает наилучший план выполнения запроса, также использовать такие подсказки рекомендуется только опытным программистам T-SQL, которые могут проверить производительность инструкции с указанием разных хинтов и без них, и четко определить, что эффективней.
Поэтому без каких-либо на это веских причин, если производительность инструкции Вас полностью устраивает, использовать подсказки не следует! Со временем, с накоплением данных, Ваша инструкция с хинтом может оказаться неэффективной и снизить производительность приложения, а Вы не будете понимать, почему так произошло, ведь в это время Вы никакие изменения не вносили и найти причину снижения производительности Вам будет трудно.
Для проверки данных используйте ограничения вместо триггеров
Когда Вы будете проектировать БД и приложение, для проверки данных или их исправления в процессе занесения или создания, используйте ограничения, а не триггеры. Решение с триггером, конечно же, исправит ошибочную ситуацию и подойдет Вам, но истинная Ваша цель в том, чтобы не допускать в принципе таких ситуаций.
Исключение.
Используйте триггеры только тогда, когда это реализовать с помощью ограничения невозможно, например, в случае с реализацией сложных бизнес-правил.
Используйте синтаксис SQL-92 для объединений JOIN
Синтаксис SQL-92 предполагает, что объединение нужно выносить в отдельные секции JOIN с указанием конкретного типа объединения (INNER, CROSS, LEFT и т.д.), а прежний синтаксис, его до сих пор также можно использовать, предполагает простое перечисление таблиц в секции FROM и соответствующих условий объединения в секции WHERE.
Но для повышения читабельности кода рекомендую использовать именно SQL-92, т.е. конкретно указывать тип объединения. Так Вы сразу будете видеть, как объединяются таблицы, и какие при этом столбцы задействованы.
Пример, в котором использован старый синтаксис
SELECT G.ProductName, C.CategoryName
FROM Goods G, Categories C
WHERE G.Category = C.CategoryId
AND G.Price > 100Пример, в котором использован новый, более подробный синтаксис
SELECT G.ProductName, C.CategoryName
FROM Goods G
INNER JOIN Categories C ON G.Category = C.CategoryId
WHERE G.Price > 100В первом случае в секции WHERE у Вас и условие объединения, и условия для фильтрации, во втором случае Вы уже четко видите, где и какое объединение используется в запросе, какие поля задействованы для объединения, а какие для фильтрации, за счет применения INNER JOIN и ON.
Но это простой пример, и, может быть, здесь не видно ощутимой разницы, но, если объединений будет много, при этом все условия будут в секции WHERE, Вам будет очень сложно разобраться в них, например, в формировании логики запроса, т.е. какое условие относится к объединению данных, а какое к их фильтрации.
Программисты в «возрасте» могут и возразить, ведь они-то привыкли к такому синтаксису, и им покажется данное правило не очень полезным, но в действительности я могу с уверенностью сказать, что использование явных, подробных инструкций (практически везде), без каких-либо сокращений, облегчает чтение и понимание этих SQL инструкций. Пусть по логике написания объединения понятно, что это за объединение, но использование SQL-92, т.е. указание LEFT, INNER и так далее, повышает читабельность всей инструкции, так даже неопытные программисты смогут быстро разобраться в работе этой инструкции.
Если задачу можно решить на T-SQL, то делайте это на T-SQL
В Microsoft SQL Server есть возможность использования других языков программирования, отличных от T-SQL, например, C# и других. Это можно сделать за счет подключения расширений (CLR-сборок), которые как раз и можно написать, например, с использованием объектно-ориентированного языка программирования.
Принцип следующий: Вы подключаете к SQL серверу CLR-сборку, в которой, скажем, реализована функция на C#, а Вы можете использовать эту функцию как обычную функцию, реализованную на T-SQL, с передачей параметров и так далее. Что значительно расширяет возможности программирования в Microsoft SQL Server.
Но не нужно подключать CLR-сборку, которая решает задачу, с которой может справиться и сам T-SQL. Помните, что язык T-SQL ориентирован на работу с данными, и он эффективней справится с задачей, в которой предполагается оперирование данными в БД, объектно-ориентированный язык для таких задач не предназначен, кроме того, функционал, подключенный с помощью CLR-сборок, тяжелей сопровождать, поэтому хорошенько подумайте перед тем, как реализовывать задачу в виде CLR-сборки.
Всегда пишите конструкцию BEGIN END
В языке T-SQL в условных конструкциях использование ключевых слов BEGIN и END необязательно, ведь они нужны только для группировки взаимосвязанных инструкций. Но я рекомендую всегда писать их, даже если это не требуется, ведь этим Вы повышаете читабельность и понятность кода, и исключаете возможность появления ошибок в будущем, когда потребуется вносить изменения в этот участок кода. Допустим, добавление дополнительного действия при выполнении условия, при котором Вы можете просто забыть объединить их в группу с помощью BEGIN END.
Пример без использования BEGIN END, что вполне допустимо, но не рекомендуется
DECLARE @ProductId INT = 1;
IF @ProductId = 1
PRINT 'Действие';Пример с использованием BEGIN END
DECLARE @ProductId INT = 1;
IF @ProductId = 1
BEGIN
PRINT 'Действие';
ENDДа, вторая инструкция длинней, но ее логика визуально более понятна.
Пишите скобки и другие парные операторы одновременно
Когда Вы будете писать код с использованием скобок или, например, блоков BEGIN END, то записывайте сначала одновременно открывающую и закрывающую скобку или часть блока, а уже потом заполняйте внутреннее содержимое. Тем самым Вы минимизируете вероятность возникновения ошибок в процессе написания кода, связанных, например, с отсутствием закрывающейся скобки или наличия лишних скобок. Данный прием относится к очень хорошей практике написания кода!
Допустим, Вам потребовалось в запросе использовать вложенные функции, не нужно в процессе написания кода использовать следующую последовательность
--Парные скобки записываются в разное время
DECLARE @ProductDescription AS VARCHAR(100);
SET @ProductDescription = 'Персональный_компьютер';
--1
SELECT REPLACE(@ProductDescription
--2
SELECT RTRIM(REPLACE(@ProductDescription
--3
SELECT RTRIM(REPLACE(@ProductDescription, '_', ' ')) AS ProductDescription;Лучше записывайте их одновременно, а после заполняйте внутреннее содержимое.
--Парные скобки записываются одновременно
DECLARE @ProductDescription AS VARCHAR(100);
SET @ProductDescription = 'Персональный_компьютер';
--1
SELECT REPLACE()
--2
SELECT REPLACE(@ProductDescription '_', ' ')
--3
SELECT RTRIM(REPLACE(@ProductDescription, '_', ' ')) AS ProductDescription;
Наиболее вероятный вариант в CASE располагайте самым первым
Конструкция CASE позволяет выбрать один подходящий вариант из множества, с учетом различных условий. Но чтобы сделать эту конструкцию более читабельной и повысить ее быстродействие, необходимо самые подходящие и часто встречающиеся варианты, в случае если CASE применяется для столбца, размещать раньше остальных. Тем самым Вы минимизируете количество кода, который будет обрабатывать CASE, так как CASE последовательно оценивает условия и его выполнение останавливается, когда будет найдено первое подходящее условие. Это также снижает вероятность ошибок (то же самое деление на ноль), так как CASE проверяет ошибки в каждом выражении WHEN тогда, когда непосредственно оценивает и выполняет его.
К тому же сопровождать код станет немного легче, если в самом начале будет указано то выражение, которое чаще всего выполняется, и в которое чаще всего вносятся изменения. Иными словами, если Вы захотите внести изменения, Вам достаточно взглянуть на CASE, и сразу в самом верху внести эти изменения. В противном случае Вам нужно будет анализировать абсолютно все выражения WHEN в CASE, что может затянуться, если они будут сложными.
Например, в следующей простой инструкции можно предположить, что общая сумма заказов достаточно часто будет меньше 100, а такие суммы, как 0 или больше 200, будут встречаться редко. Но если это не так, допустим, наоборот, практически всегда сумма заказов больше 200, то порядок выражений не совсем логичен, так как первые две проверки будут выполняться в большинстве случаев, спрашивается -зачем?
DECLARE @TotalAmount MONEY = 0;
SELECT @TotalAmount = SUM(OrderAmount)
FROM Orders;
SELECT CASE WHEN @TotalAmount > 0 AND @TotalAmount < 100 THEN 1
WHEN @TotalAmount BETWEEN 100 AND 200 THEN 2
WHEN @TotalAmount > 200 THEN 3
ELSE 0
END AS OrderGroup;
Исключение.
Если CASE содержит простые выражения, и их не более 3-4, Вы можете расположить их в том порядке, в котором Вам удобней их читать, например, в случае с целыми числами - по возрастанию.
Проверяйте условие с использованием операторов «больше» и «меньше»
Когда Вы будете сравнивать значения с использованием оператора больше (>) или меньше (<), обязательно учитывайте возможность равенства, тем самым Вы избежите потерю некоторых значений.
Допустим, Вам нужно вывести в запросе все заказы с суммой больше 100, большинство программистов так и напишут Summa > 100. А что будет с заказами, в которых сумма равна 100? Нужны ли они Вам? Или нет? Большинство программистов допускают ровно такую ошибку, т.е. указывают некую цифру, и не учитывают равенство, в тех случаях, когда оно требуется. Поэтому я рекомендую всегда помнить о том, что значения могут еще и равняться, и какой оператор писать > или >= решать нужно в каждой конкретной ситуации.
Также напоминаю, что лучше использовать BETWEEN вместо двух предикатов с операторами «больше или равно» и «меньше или равно».
Размещайте нормальный ход событий после IF, а не после ELSE
Данное правило относится к классическому программированию, но оно также действует и в случае с языком T-SQL.
Весь смысл заключается в том, что после IF должен идти нормальный ход выполнения инструкции, а не после ELSE. Иными словами, не нужно делать проверку, в которой в случае TRUE будет выполняться какое-то исключение. Читать такую инструкцию очень неудобно, так как сначала Вы просматриваете варианты действий, которые не должны выполняться при нормальном ходе событий.
Например, нам нужно выполнить определённую инструкцию только в том случае, если сумма заказа превышает 100, а если она равна или меньше 100, то инструкцию, которая будет выполнять что-то исключительное.
Следующая инструкция показывает, как не надо делать.
IF @TotalAmount <= 100
BEGIN
PRINT 'Исключительная ситуация';
END
ELSE
BEGIN
PRINT 'Нужная нам инструкция';
ENDЗдесь Вы проверяете значение переменной так, что в случае успеха будут выполнены какие-то исключительные действия, а во всех остальных случаях будет выполняться код из блока ELSE, т.е. нужная нам инструкция, которая подразумевает нормальный ход событий.
В следующей инструкции мы уже делаем правильную проверку, т.е. если значение больше 100, то выполняем нужную нам инструкцию, а если нет, то выполняем какое-то исключительное действие
IF @TotalAmount > 100
BEGIN
PRINT 'Нужная нам инструкция';
END
ELSE
BEGIN
PRINT 'Исключительная ситуация';
END
При создании таблиц учитывайте логическую последовательность столбцов
Располагайте столбцы таблицы при ее создании в логическом порядке, пусть это никак не влияет на производительность или на какие-то другие моменты, но тем самым Вы облегчите себе работу в процессе разработки запросов и инструкций, а также повысите читабельность исходной инструкции создания таблицы. Ведь когда взаимосвязанные столбцы располагаются рядом, не нужно бегать глазами в поиске таких столбцов, например, в тех случаях, когда мы разрабатываем запрос, мы частенько используем звездочку (*) в качестве определения столбцов для того, чтобы посмотреть на фактические данные. При этом SQL Server будет выводить столбцы в том порядке, в котором они физически указаны, и, если они не упорядочены, нам будет очень неудобно конструировать запрос, так как столбцы просто хаотично разбросаны по таблице.
Например, логично же расположить рядом столбцы с номером и датой документа, вместо того чтобы один столбец был в начале, а второй в конце, сначала мы можем и не обратить внимания, что есть такая важная, логически связанная характеристика в таблице.
Для столбцов с числовыми значениями используйте ограничения диапазона
В большинстве случаев на числовые данные накладываются какие-то бизнес-ограничения, например, число должно быть больше 0, или не превышать 1000, и так далее, поэтому во всех подобных случаях не поленитесь создать соответствующее ограничение, в случае необходимости скорректировать его легче, чем исправлять возможные ошибки в расчетах.
Здесь действует одно очень важное правило в разработке всего приложения, как в части безопасности, так и в части надежности, я его сформулировал следующим образом
«Запрещаем все, а разрешаем только то, что необходимо».
Отсюда следует: если столбец не должен принимать значение меньше нуля, значит, мы сразу запрещаем хранение таких значений, а все исключительные случаи, которые будут возникать в процессе эксплуатации, рассматриваются индивидуально и согласовываются с руководством.
Для текстовых столбцов используйте ограничения LIKE
Здесь действуют те же самые правила, что и в случае с числовыми данными, только с текстовыми данными немного сложней, ведь на них не так легко наложить четкое ограничение, приходится использовать регулярные выражения.
Исключение.
Если столбец действительно может принимать любые текстовые данные, только в этом случае не используйте ограничения.
Создавайте отдельное ограничение СНЕСК для каждой проверки
Не нужно создавать одно «суперограничение», которое будет включать несколько проверок, лучше создайте для каждой проверки отдельное ограничение, тем самым в случаях, когда сработает ограничение, и SQL сервер выдаст ошибку, Вам или пользователю будет легче понять ее. А также такие ограничения легче сопровождать, ведь вынося каждую проверку в отдельное ограничение, мы тем самым снижаем сложность отдельно взятого ограничения.
Не разделяйте характеристику на несколько частей
Не стоить одну характеристику делить на несколько частей. Это означает, что, если по модели данных характеристика одна, она так и должна оставаться одним целым.
Что здесь я подразумеваю под разделением? Например, разделение по полу (мужской, женский). Допустим, в одну таблицу Вы записываете все сущности женского пола, а в другую мужского пола; или разделение по столбцам, т.е. в один столбец - мужской признак, а в другой - женский. Так не стоит делать, во-первых, это ошибка проектирования в части нормализации данных, т.е. Вы один и тот же атрибут отражаете в нескольких местах, во-вторых, Вы значительно усложняете процесс программирования в БД, так как Вам в любом случае нужно будет предоставлять общие объединённые данные, а как Вы знаете, если процесс мы усложняем, мы тем самым повышаем вероятность появления ошибок, что не очень хорошо. Поэтому не нужно разделять в базе данных одну характеристику по модели данных на несколько.
Избегайте хранения значений NULL в столбцах
На мой взгляд, это уже общепризнанный факт, что значения NULL в базе данных - это плохо, по крайней мере, в большинстве случаев. Если Вы этого не знали, то знайте - это так! Так как такие значения значительно усложняют процесс работы с данными, при их наличии мы нередко допускаем ошибки в своих запросах, сначала даже не зная, что мы их допустили !
В связи с этим нужно стараться избегать появления таких значений. Как это сделать? Для этого необходимо все столбцы в таблице по возможности делать обязательными, т.е. они должны иметь свойство «NOT NULL». Если данные в столбце
по модели не являются обязательными, то для таких столбцов, кроме свойства NOT NULL, необходимо создавать еще и ограничение «Значение по умолчанию», тем самым пользователю необязательно заполнять самостоятельно эти данные, SQL сервер при наличии такого ограничения присвоит значение по умолчанию, которое Вы укажете при создании ограничения.
Такие простые действия позволят минимизировать NULL в базе данных.
Исключение.
К сожалению, полностью избавиться от NULL и задать абсолютно всем столбцам вышеуказанные свойства в реальности будет сложно, но к этому нужно стремиться, поэтому разрешайте хранение значения NULL только в исключительных случаях, когда другого выхода у Вас нет. Иными словами, примените тот же принцип: запретите все, а разрешайте только по очень важной необходимости. Тем более, что разрешить хранение значений NULL легче, чем сделать наоборот - задать свойство NOT NULL, так как в этом случае Вам уже нужно будет работать с данными, чтобы исключить существующие NULL в столбце.
Сохраняйте SQL запросы и инструкции
При программировании на T-SQL сохраняйте все свои скрипты и SQL запросы в любом удобном для Вас виде, например, в текстовых файлах с расширением «.sql», и давайте таким файлам название, соответствующее названию задачи, для которой и создавался это запрос или инструкция. Делайте это, даже если запрос или инструкция является одноразовой, т.е. на момент ее разработки предполагается, что она больше не потребуется.
Если у Вас будут сохранены все эти наработки, то в случае появления схожей задачи, у Вас всегда будет, от чего оттолкнуться, а также нередко вроде бы одноразовые задачи повторяются, и в случае наличия наработок, Вам уже не нужно будет снова тратить время на разработку SQL запросов или инструкций.
Заключение
Всё, что я хотел рассказать Вам про стиль программирования на T-SQL, я рассказал. Применяя все эти правила, Вы обязательно повысите читабельность своих SQL инструкций, и снизите сложность их сопровождения. Но снова повторюсь, что эти правила не являются каким-то официальным стандартом, они основаны только на моих знаниях, которые я получил в процессе изучения и работы с языком T-SQL.
Также хочу дать Вам еще одну рекомендацию, нам мой взгляд, самую важную, если Вы хотите стать профессионалом по языку T-SQL, то никогда не останавливайтесь в его изучении, так как, поверьте мне, всегда найдётся что-то, чего Вы еще не знаете. Это относится и ко всему тому, что Вы хотите изучить, т.е. не нужно думать, что, прочитав одну или две книжки, Вы стали экспертом, это далеко не так, всегда есть что-то, с чем Вы не сталкивались, и что Вы еще не знаете.
В процессе изучения IT технологий я пришел к выражению, которое стал очень хорошо понимать, так как оно отражает действительность:
«Чем больше я узнаю, тем больше я понимаю, что ничего не знаю».
И уже после того, как я сформулировал это выражение, узнал, что всемирно известный философ Сократ, когда-то говорил что-то подобное:
«Я знаю только то, что я ничего не знаю, но многие не знают Даже этого!»
Поэтому рекомендую Вам постоянно совершенствоваться, стремиться узнавать что-то новое, развиваться и профессионально, и личностно! Только получая новую информацию, и применяя ее на практике, Вы постепенно будете становиться профессионалом.
Вы не буДете расти, если не буДете пытаться совершить что-то за пределами того, что Вы уже знаете в совершенстве.
Ральф УолДо Эмерсон
