Основные принципы программирования на T-SQL

0 ∞

У всех есть мнение относительно того, как писать код TSQL: по форматированию, присвоению переменных, системным функциям, стандартам ANSI или любым другим вопросам. И эти мнения могут существенно отличаться друг от друга, о чем свидетельствует множество сообщений на форумах и горячих дискуссий в комментариях к статьям. Но большинство согласится с тем, что хорошо читаемый и высокопроизводительный код является лучшим решением для всех. Конечно, вся загвоздка в том, как достичь этого.

Лучше всего начать с создания набора стандартов, которые помогут определиться разработчикам T-SQL, а затем ссылаться на них при построении решений. Таким образом, стандарты помогают устранить различия и работать в направлении общей цели.

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

Содержание

Читаемость и другие аспекты

О форматировании TSQL написано немало статей. Иногда в определенный момент нужно просмотреть большую часть кода, понять, что делает код, не говоря уже о выявлении потенциальных проблем.

В качестве примера возьмем операторы USE и SELECT, которые написаны строчными буквами, без разделителей строк или пробелов, кроме тех случаев, когда это необходимо:

use adventureworks2014
  go
  select emp.businessentityid empid,psn.firstname,psn.lastname,emp.sickleavehours sickleave,emp.nationalidnumber natid,emp.JobTitle from humanresources.employee emp inner join person.person psn on emp.businessentityid=psn.businessentityid where emp.jobtitle='production technician - wc60' or emp.jobtitle='production technician - wc50' order by emp.JobTitle desc,EmpID asc

По умолчанию SQL Server настроен так, что регистр букв не имеет значения. Поэтому нет причин, по которым нельзя было бы использовать этот подход. Или подход, который иллюстрирует следующий пример:

USE ADVENTUREWORKS2014
  GO
  SELECT EMP.BUSINESSENTITYID EMPID,PSN.FIRSTNAME,PSN.LASTNAME,EMP.SICKLEAVEHOURS SICKLEAVE,EMP.NATIONALIDNUMBER NATID,EMP.JOBTITLE FROM HUMANRESOURCES.EMPLOYEE EMP INNER JOIN PERSON.PERSON PSN ON EMP.BUSINESSENTITYID=PSN.BUSINESSENTITYID WHERE EMP.JOBTITLE='PRODUCTION TECHNICIAN - WC60' OR EMP.JOBTITLE='PRODUCTION TECHNICIAN - WC50' ORDER BY EMP.JOBTITLE DESC,EMPID ASC

Это вполне допустимо. Но невозможно прочесть. Представьте, что вам нужно просмотреть скрипт, содержащий сотни строк такого кода.

Даже код TSQL convert, который намного лучше этого, может быть сложен для понимания, если он непоследователен или организован случайным образом. Но посмотрите, что происходит, когда мы разбиваем оператор TSQL SELECT на несколько строк, вставляем отдельные элементы, ключевые слова пишем заглавными буквами и добавляем комментарии для пояснения оператора:

/*
  Извлекаем данные технических сотрудников WC60 и WC50.
  */
  USE AdventureWorks2014;
  GO
  SELECT emp.BusinessEntityID AS EmpID,
    psn.FirstName,
    psn.LastName,
    emp.SickLeaveHours AS SickLeave,
    emp.NationalIDNumber AS NatID, 
    emp.JobTitle
  FROM HumanResources.Employee AS emp
    INNER JOIN Person.Person AS psn
    ON emp.BusinessEntityID = psn.BusinessEntityID
  WHERE (emp.JobTitle = 'Production Technician - WC60')
    OR (emp.JobTitle = 'Production Technician - WC50')
  ORDER BY emp.JobTitle DESC, EmpID ASC;

Этот код намного легче рассматривать, чем тот, который был приведен в примерах выше. Продуманное использование разрывов строк, пробелов, заглавных букв и отступов облегчает понимание кода T-SQL, особенно когда все следуют тем же стандартам.

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

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

Перейдем к комментариям, которые представляют собой простой и эффективный механизм для описания того, что происходит в коде. В том числе и в TSQL case. Размещенные надлежащим образом комментарии могут сэкономить время и даже указать на несоответствия. Даже если вы просматриваете собственный код, то оцените комментарии, оформленные надлежащим образом, особенно если вы написали этот код несколько лет назад.

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

Еще один способ облегчить понимание кода - использовать круглые скобки, когда это необходимо. В предыдущем примере я добавил скобки в выражение WHERE, чтобы продемонстрировать эту концепцию. Хотя в этом случае они действительно не нужны, это служит напоминанием, как и комментарий. Круглые скобки помогают прояснить логику выражения, чтобы сделать инспектирование кода более быстрым и простым.

Лучшей практикой считается добавление точки с запятой в конце операторов. Хотя в большинстве случаев для SQL Server она не обязательна, в Microsoft предупреждают, что точка с запятой нужна. Это уже стало частью стандартов ANSI.

Старайтесь избегать использования команд GOTO. Это может затруднить проверку кода, особенно если много.

Наша задача состоит в том, чтобы разработать способ последовательного форматирования и представления кода T-SQL, сделать его читаемым, понятным и поддерживаемым.

Использование команды UNION

Написание правильного кода

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

Часто встречающийся пример устаревшего кода - это объединение, основанное на стандарте SQL-92, в котором условие объединения определено в выражении WHERE, как показано в следующем примере:

SELECT emp.BusinessEntityID AS EmpID,
    psn.FirstName, 
    psn.LastName,
    emp.NationalIDNumber AS NatID
  FROM HumanResources.Employee emp, 
    Person.Person psn
  WHERE emp.BusinessEntityID = psn.BusinessEntityID
    AND emp.JobTitle = 'Production Technician - WC60';

Хотя SQL Server по-прежнему поддерживает такой подход, но все равно необходимо придерживаться новой модели, которая должна включать условие объединения в выражении FROM:

SELECT emp.BusinessEntityID AS EmpID,
    psn.FirstName, 
    psn.LastName,
    emp.NationalIDNumber AS NatID
  FROM HumanResources.Employee emp
    INNER JOIN Person.Person psn
    ON emp.BusinessEntityID = psn.BusinessEntityID
  WHERE emp.JobTitle = 'Production Technician - WC60';

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

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

Другой пример устаревшего кода, когда оператор T-SQL включает выражение TOP. В прошлом мы указывали числовое выражение без круглых скобок, как в этом примере:

SELECT TOP 10 Title, FirstName, LastName
  FROM Person.Person;
Хотя это все еще работает в SQL Server, правильный синтаксис теперь включает в себя круглые скобки:
SELECT TOP(10) Title, FirstName, LastName
  FROM Person.Person;

Команда, занимающаяся базой данных, должна активно работать над заменой устаревшего кода. Многие T-SQL и ANSI SQL-элементы уже устарели или могут устареть в будущем. С учетом того, как быстро меняются стандарты, скорее всего, у вас есть устаревшие элементы в коде. Например, в SQL Server 2016 параметр SET ROWCOUNT устарел для операторов TSQL INSERT, UPDATE и DELETE. Так же есть типы данных text, ntext и image. Даже такие операторы, как CREATE DEFAULT и DROP DEFAULT когда-нибудь устареют.

Когда вы определяете стандарты кодирования, не забудьте указать, как поступать с устаревшими элементами. Легко сказать, что разработчики должны их избегать, но этого тяжело добиться. Поэтому нужно объяснить, когда и как их следует удалять. К счастью, Microsoft предоставляет сведения о том, что устаревает с выходом каждой новой версии SQL Server. Вы найдете список этих элементов в разделе Deprecated Database Engine Features in SQL Server 2016.

Теперь посмотрим на другой пример сомнительного кода. Следующий оператор выбора делает то, что не должен - оператор сравнения (не равно) использован для значения NULL:

SELECT Title, FirstName, LastName
  FROM Person.Person
  WHERE Title <> NULL;

Несмотря на то, что таблица содержит строки со значением Title NULL, этот оператор не возвращает строки и не возвращает ошибку. Если вы не будете внимательны, то можете получить неверные результаты. По этой причине следует избегать такого типа конструкции и вместо них использовать оператор IS NULL или IS NOT NULL для возвращения правильных строк:

SELECT Title, FirstName, LastName
  FROM Person.Person
  WHERE Title IS NOT NULL;
Сервис событий в SQL-сервере

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

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

SELECT EmpID = emp.BusinessEntityID,
    psn.FirstName, 
    psn.LastName,
    NatID = emp.NationalIDNumber
  FROM HumanResources.Employee emp
    INNER JOIN Person.Person psn
    ON emp.BusinessEntityID = psn.BusinessEntityID
  WHERE emp.JobTitle = 'Production Technician - WC60';

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

SELECT emp.BusinessEntityID AS EmpID,
    psn.FirstName, 
    psn.LastName,
    emp.NationalIDNumber AS NatID
  FROM HumanResources.Employee emp
    INNER JOIN Person.Person psn
    ON emp.BusinessEntityID = psn.BusinessEntityID
  WHERE emp.JobTitle = 'Production Technician - WC60';

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

Еще один вопрос, который стоит рассмотреть, использование недокументированных хранимых процедур. Например, следующий оператор SELECT используется хранимой процедурой sp_mstablespace для возврата количества строк и объема дискового пространства, используемого таблицей Person. Пример TSQL exec:

EXECUTE sp_mstablespace 'person.person';

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

Разумеется, при стандартизации и проверке кода существуют всевозможные проблемы. Одна из основных задач - решить, насколько важно, чтобы код соответствовал стандартам ANSI. Некоторые утверждают, что он должен всегда соответствовать ANSI, потому что это делает его переносимым и понятным для разработчиков. С другой стороны, вы можете потерять важные функции, отказавшись от собственных элементов. Необходимо решить, что лучше для вашей команды и организации.

Несоответствие функций

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

Например, функция ISNUMERIC печально известна тем, что возвращает непредсказуемые результаты, что продемонстрировано в приведенном ниже примере:

DECLARE @a TABLE(ColA VARCHAR(10));
  INSERT INTO @a VALUES
  ('abc'), ('123'), ('$456'), ('22:35:27');

  SELECT ColA, CASE
    WHEN ISNUMERIC(ColA) = 1 
      THEN CAST(ColA AS INT)
    END AS TestResults
  FROM @a;

Мы создаем переменную таблицы TSQL и заполняем ее разными типами значений, которые передаются в виде строк. Затем используем функцию ISNUMERIC для проверки, является ли значение числовым. Если это так (функция возвращает 1), пытаемся преобразовать значение в тип данных INT. Но в данном случае, когда ядро базы данных достигает значения $456, оно сбрасывается и возвращается сообщение об ошибке:

Conversion failed when converting the varchar value '$456' to data type int.

Проблема заключается в том, что функция ISNUMERIC иногда вызывает числовое значение, которое не может быть преобразовано в числовой тип данных, как для $456. Она даже интерпретирует такие значения, как 7e9 и $., как числовые. Лучшим решением данной проблемы является использование функции TRY_CONVERT:

DECLARE @a TABLE(ColA VARCHAR(10));
  INSERT INTO @a VALUES
  ('abc'), ('123'), ('$456'), ('22:35:27');
  SELECT ColA, CASE
    WHEN TRY_CONVERT(int, ColA) IS NOT NULL 
      THEN CAST(colA AS INT)
  END AS TestResults
  FROM @a;

Преобразование данных является довольно сложным разделом в SQL Server, поэтому вы должны быть внимательны.

При работе с системными функциями вам придется иметь дело с различными функциями, которые могут использоваться для выполнения аналогичных операций или могут возвращать похожие типы информации. Например, можно использовать функцию CAST или CONVERT для преобразования типа данных значения. Функция CAST является частью стандартов ANSI, но имеет ограниченные возможности.

Функция TSQL CONVERT не предусмотрена стандартами, но она предоставляет ряд параметров для форматирования значений даты и времени. Хотя во многих случаях лучше оставить форматирование на уровне приложений. Независимо от этого нужно будет принять решение, какие из функций использовать, и насколько важно для вас соответствие кода стандартам ANSI.

Другим примером взаимозаменяемых функций являются COUNT и EXISTS, когда они используются для подтверждения существования определенных данных. Например, следующий оператор IF проверяет, содержит ли таблица Person строки, имеющие значение EM в столбце PersonType:

IF(SELECT COUNT(*) FROM Person.Person 
    WHERE PersonType = 'EM') > 0
  SELECT FirstName, LastName
  FROM Person.Person
  WHERE PersonType = 'EM';
Что такое SQL инъекция: изучаем на примерах

Хотя этот оператор работает отлично, можно увеличить производительность, использовав функцию EXISTS, особенно для больших наборов данных:

IF EXISTS(SELECT * FROM Person.Person 
    WHERE PersonType = 'EM')
  SELECT FirstName, LastName
  FROM Person.Person
  WHERE PersonType = 'EM';

Неправильное использование функции - это не всегда проблема функции. Например, в зависимости от ситуации использование функции SCOPE_IDENTITY() выдает более точную информацию, чем системная переменная @@IDENTITY. В обоих случаях возвращается последнее значение идентификатора, сгенерированное для таблицы в текущей сессии. Но функция SCOPE_IDENTITY() применяется только к определенной области, а переменная @@ IDENTITY этого не делает, что может влиять на правильность возвращаемого значения. Дополнительные сведения об этой проблеме вы найдете в разделе документации SQL Server SCOPE_IDENTITY (Transact-SQL).

Использование системных функций TSQL не должно быть произвольным. Необходимо тщательно взвесить, какие из них задействовать.

Переменные и параметры

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

DECLARE @a INT,
    @b VARCHAR(25),
    @c VARCHAR(2),
    @d VARCHAR,
    @e MONEY;
  SET @a = 25;
  SET @b = 'twenty-five';
  SELECT @c = 'EM', @e = 25;
  SELECT @a AS '@a',
    @c AS '@c', 
    @d AS '@d',
    @e AS '@e',
    @f AS '@f';

В этом коротком наборе операторов T-SQL нам удалось зафиксировать ряд ошибок:

  • Мы объявляем переменную @b и присваиваем ей значение, но никогда не используем ее в операторе SELECT;
  • Мы объявляем @c с типом данных VARCHAR(2), а не с типом CHAR(2);
  • Мы объявляем @d как VARCHAR, без указания длины, и не присваиваем переменной значение. Затем мы используем переменную в операторе SELECT;
  • Мы используем @f в выражении SELECT, хотя не объявили ее и не присвоили ей значение.

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

Еще одна проблема, о которой стоит упомянуть - это использование SELECT для инициализации значений переменных. Такой подход позволяет присваивать значения нескольким переменным в одном выражении, чего нельзя сделать с помощью SET. С другой стороны, этот подход не является частью стандартов ANSI, и может ввести разработчика в заблуждение, когда он представлен рядом с другими операторами SELECT и TSQL UPDATE.

Замечательный мир сравнений

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

Предположим, что в вашем экземпляре SQL Server настроена сортировка SQL_Latin1_General_CP1_CI_AS, и затем вы создаете следующие базу данных и таблицу:

USE master;
  GO
  DROP DATABASE IF EXISTS Storehouse;
  GO
  CREATE DATABASE Storehouse
  COLLATE Latin1_General_100_CS_AI;
  GO
  USE Storehouse;
  GO
  DROP TABLE IF EXISTS Customers;
  GO
  CREATE TABLE Customers(
    NameID INT IDENTITY PRIMARY KEY,
    FirstName NVARCHAR(50) COLLATE Traditional_Spanish_CI_AS NOT NULL,
    LastName NVARCHAR(50) COLLATE Traditional_Spanish_CI_AS NOT NULL,
    Email NVARCHAR(50) NULL);

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

И это только начало

Существует гораздо больше эффективных методов создания кода, чем те, которые мы рассмотрели. В этой статье основное внимание уделено общим вопросам, связанным с форматированием кода, которые являются общими для широкого спектра операторов TSQL.

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