5 полезных запросов для MS SQL

За 2 года работы с MS SQL у меня накопился перечень из 5 запросов: для поиска, отладки, агрегации и обработки множеств и таблиц.

9040

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

Поиск

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

			SELECT OBJECT_NAME(object_id)
FROM sys.sql_modules AS sm
WHERE sm.definition LIKE '%filter%'

Или, если нам нужно найти код, который находится внутри джоба:

			SELECT j.name, step_name
FROM msdb..sysjobs AS j
  JOIN msdb..sysjobsteps AS js
    ON j.job_id = js.job_id
WHERE command LIKE '%filter%'

Отладка

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

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

			BEGIN TRAN
    DECLARE @id INT = 10;
    -- Вызов процедуры, которая изменяет реальные данные
    EXEC dbo.spDoSomething @param1 = @id;
    -- Проверяем, что реальные данные изменились так, как нам нужно
    SELECT *
    FROM dbo.tblData
    WHERE IdData = @id;
  -- Возврат состояния системы в исходное состояние
  ROLLBACK

Числовые множества и случайные числа

Для генерации множества «на лету», без использования дополнительных таблиц можно использовать рекурсивные CTE:

			-- Пример с генерацией чисел от 1 до 100
  WITH cte
  AS
  (
    SELECT Number = 1
    UNION ALL
    SELECT Number = Number + 1
    FROM cte
    WHERE Number < 100
  )
  SELECT *
  FROM cte

А для генерации случайных чисел или сортировки строк в случайном порядке можно использовать стандартную функцию NEWID():

			-- Генерация случайных чисел
SELECT CHECKSUM(NEWID())

-- Генерация случайного числа от 0 до 100
SELECT ABS(CHECKSUM(NEWID())) % 101

-- Сортировка данных в случайном порядке
SELECT *
FROM tblGoods
ORDER BY NEWID()

Передача табличных данных

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

			-- Создаем собственный тип, возвращающий табличное значение:
CREATE TYPE MyTableValueType
AS TABLE
(
  IdField INT NOT NULL UNIQUE,
  TextValue VARCHAR(100) DEFAULT('')
);
GO
CREATE PROC dbo.spProcessing
  @myTable MyTableValueType READONLY
AS
BEGIN
  -- Тут можно использовать нашу табличную переменную @myTable
  SELECT *
  FROM @myTable
END

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

			— Server 1
CREATE PROCEDURE api.spXMLParametrExample
  @XML VARCHAR(8000)
AS
BEGIN
  SET NOCOUNT ON;

  DECLARE @xmlTable XML = CAST(@XML AS XML);

  SELECT
    Id = h.l.value('(./@Id)[1]', 'INT'),
    TextValue = h.l.value('(./@TextValue)[1]', 'VARCHAR(50)')
  FROM @xmlTable.nodes('./Head/Line') AS h(l)
END

— Server 2
DECLARE @xml VARCHAR(8000);

SET @xml =
(
  SELECT
    [@Id] = Id,
    [@TextValue] = TextValue
  FROM tblMyTable
  FOR XML PATH('Line'), ROOT('Head')
);

/*
<Head>
<Line Id="1" TextValue="SomeText" />
<Line Id="2" TextValue="SomeText2" />
</Head>
*/

EXEC server1.DB1.api.spXMLParametrExample @XML = @xml;

Агрегация в разрезе строк

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

Допустим, у нас есть таблица tblGoods, хранящая информацию о размерах некоторой продукции Size1, Size2, Size3, и нам требуется для каждой строки определить, какое из значений является высотой, иначе говоря, найти наибольшее число среди Size1, Size2, Size3.

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

Этот способ заключается в использовании подзапроса и конструкции SELECT * FROM (VALUES…).

Для начала подготовим тестовые данные:

			-- Создаем тестовую таблицу, заполненную случайными числами
CREATE TABLE tblGoods
ADD
  CodeGoods VARCHAR(10),
  Size1 INT DEFAULT(ABS(CHECKSUM(NEWID())) % 50),
  Size2 INT DEFAULT(ABS(CHECKSUM(NEWID())) % 50),
  Size3 INT DEFAULT(ABS(CHECKSUM(NEWID())) % 50);

-- Добавляем 100 строк товаров
INSERT INTO tblGoods(CodeGoods)
VALUES(LEFT(NEWID(), 10))
GO 100

Пример 1, с использованием оператора UNPIVOT:

			-- Вариант с использованием оператора UNPIVOT
 SELECT
  CodeGoods,
  Height = MAX(ValueSize)
 FROM tblGoods AS g
 UNPIVOT
 (ValueSize FOR TypeSize IN(Size1, Size2, Size3)) AS unpvt
 GROUP BY CodeGoods;

Пример 2, с подзапросом:

			-- Вариант без UNPIVOT
SELECT
  CodeGoods,
  Height = (  SELECT MAX(Size)
        FROM (  VALUES(g.Size1), (g.Size2), (g.Size3) ) AS Sizes(Size))
FROM tblGoods AS g

На этом все. Надеюсь, среди читателей найдутся те, кто открыл для себя что-то новое в этой статье.

9040