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

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

10К открытий11К показов

За 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%'

Отладка

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

Обнаружена первая зловредная программа для Android на языке Kotlin

tproger.ru

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

			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

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

10К открытий11К показов

Также рекомендуем

Большой гайд по инструментам для разработчиков от Tproger: фреймворки, базы, AI и DevOps в одной подборке

Подборка топовых инструментов и технологий для разработчиков: от Elixir и DevOps-платформ до no-code, AI-инструментов и новых фреймворков.

Векторные базы данных: простым языком про устройство и принцип работы

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

Как перевести проект на Laravel: пошаговый план перехода

Как перевести проект на Laravel. Показываем основные преимущества использования Ларавел. Рассматриваем пошаговую инструкцию нюансы переноса ✔ Tproger

Как работает Sharding в базах данных?

Что такое Sharding. Показываем, как работает шардинг в базах данных. Рассматриваем пошаговую инструкцию и основные нюансы ✔ Tproger