Что может подстерегать новичков при работе с SQL Server. Часть 2

В отличие от первой статьи, никаких Жозефин, уточек, пьяных альпинистов с комплексом «трёхлитровки» и прочих веселостей тут уже не будет. И не потому, что количество историй вдруг решило уменьшить свое поголовье. Причина кроется в другом: работа с базой данных — это не всегда сырок моцарелла и смузи после 8-часового дня. Есть много аспектов, на которые нужно обращать внимание при написании запросов на T-SQL.

Views

Кто-то любит представления, кто-то нет. Навязывать мнение не использовать view — себе дороже, но знать про несколько особенностей при работе с ними нужно обязательно.

Создаем тестовую таблицу и view на основе нее:

USE tempdb
GO

IF OBJECT_ID('dbo.tbl', 'U') IS NOT NULL
    DROP TABLE dbo.tbl
GO

CREATE TABLE dbo.tbl (a INT, b INT)
GO
INSERT INTO dbo.tbl VALUES (0, 1)
GO

IF OBJECT_ID('dbo.vw_tbl', 'V') IS NOT NULL
    DROP VIEW dbo.vw_tbl
GO

CREATE VIEW dbo.vw_tbl
AS
    SELECT * FROM dbo.tbl
GO

SELECT * FROM dbo.vw_tbl
GO

Значения возвращаются правильно:

a           b
----------- -----------
0           1

Теперь добавим новый столбец в таблицу и пробуем опять вычитать данные из view:

ALTER TABLE dbo.tbl
    ADD c INT NOT NULL DEFAULT 2
GO

SELECT * FROM dbo.vw_tbl
GO

Получим тот же результат:

a           b
----------- -----------
0           1

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

EXEC sys.sp_refreshview @viewname = N'dbo.vw_tbl'
GO

SELECT * FROM dbo.vw_tbl
GO

Чтобы получить правильный результат:

a           b           c
----------- ----------- -----------
0           1           2

При прямом обращении к таблице подобного прикола не будет. Что ж, идем дальше.

Есть любители в одном запросе соединить все данные и обернуть это все в одном view. За примером далеко ходить не будем и посмотрим на «хороший паттерн» из AdventureWorks:

ALTER VIEW HumanResources.vEmployee
AS
    SELECT e.BusinessEntityID
         , p.Title
         , p.FirstName
         , p.MiddleName
         , p.LastName
         , p.Suffix
         , e.JobTitle
         , pp.PhoneNumber
         , pnt.[Name] AS PhoneNumberType
         , ea.EmailAddress
         , p.EmailPromotion
         , a.AddressLine1
         , a.AddressLine2
         , a.City
         , sp.[Name] AS StateProvinceName
         , a.PostalCode
         , cr.[Name] AS CountryRegionName
         , p.AdditionalContactInfo
    FROM HumanResources.Employee e
    JOIN Person.Person p ON p.BusinessEntityID = e.BusinessEntityID
    JOIN Person.BusinessEntityAddress bea ON bea.BusinessEntityID = e.BusinessEntityID
    JOIN Person.[Address] a ON a.AddressID = bea.AddressID
    JOIN Person.StateProvince sp ON sp.StateProvinceID = a.StateProvinceID
    JOIN Person.CountryRegion cr ON cr.CountryRegionCode = sp.CountryRegionCode
    LEFT JOIN Person.PersonPhone pp ON pp.BusinessEntityID = p.BusinessEntityID
    LEFT JOIN Person.PhoneNumberType pnt ON pp.PhoneNumberTypeID = pnt.PhoneNumberTypeID
    LEFT JOIN Person.EmailAddress ea ON p.BusinessEntityID = ea.BusinessEntityID

А теперь вопрос: что, если мне нужно получить не всю информацию, а только ее часть? Например, вернуть имя и фамилию работников:

SELECT BusinessEntityID
     , FirstName
     , LastName
FROM HumanResources.vEmployee

SELECT p.BusinessEntityID
     , p.FirstName
     , p.LastName
FROM Person.Person p
WHERE p.BusinessEntityID IN (
        SELECT e.BusinessEntityID
        FROM HumanResources.Employee e
    )

Посмотрим на план выполнения в случае использования view:

Table 'EmailAddress'. Scan count 290, logical reads 640, ...
Table 'PersonPhone'. Scan count 290, logical reads 636, ...
Table 'BusinessEntityAddress'. Scan count 290, logical reads 636, ...
Table 'Person'. Scan count 0, logical reads 897, ...
Table 'Employee'. Scan count 1, logical reads 2, ...

И сравним с запросом, который мы осмысленно написали ручками:

Table 'Person'. Scan count 0, logical reads 897, ...
Table 'Employee'. Scan count 1, logical reads 2, ...

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

Code style

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

Давайте создадим отдельную базу и таблицу в ней:

USE [master]
GO

IF DB_ID('test') IS NOT NULL BEGIN
    ALTER DATABASE test SET SINGLE_USER WITH ROLLBACK IMMEDIATE
    DROP DATABASE test
END
GO

CREATE DATABASE test COLLATE Latin1_General_CI_AS
GO

USE test
GO

CREATE TABLE dbo.Employee (EmployeeID INT PRIMARY KEY)
GO

На время наденем погоны Junior Developer и напишем такой запрос:

select employeeid from employee

Работает? Бесспорно, ведь все на уровне букваря. А теперь попробуйте поменять COLLATE на какой-нибудь регистрозависимый:

ALTER DATABASE test COLLATE Latin1_General_CS_AI

И попробуем повторно проверить нашу удачу:

Msg 208, Level 16, State 1, Line 19
Invalid object name 'employee'.

Оптимизатор использует правила текущего COLLATE при построении плана выполнения. Точнее, на этапе связывания, когда производится проверка на существование таблиц, колонок и других объектов и сопоставление каждого объекта синтаксического дерева с реальным объектом системного каталога.

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

Column order

На чем еще спотыкаются — задание в ORDER BY порядкового номера столбца. Эта штука весьма коварная, но не такая хитрая, как предыдущий пример:

USE tempdb
GO

IF OBJECT_ID('dbo.tbl', 'U') IS NOT NULL
    DROP TABLE dbo.tbl
GO

CREATE TABLE dbo.tbl (a INT, b INT)
GO
INSERT INTO dbo.tbl VALUES (1, 99), (3, 4)
GO

IF OBJECT_ID('dbo.GetLastRecord') IS NOT NULL
    DROP PROCEDURE dbo.GetLastRecord
GO

CREATE PROCEDURE dbo.GetLastRecord
AS
    SELECT TOP(1) *
    FROM dbo.tbl
    ORDER BY 1 DESC
GO

EXEC dbo.GetLastRecord
GO

RecordID    Value
----------- -----------
3           4

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

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

IF OBJECT_ID('dbo.tbl', 'U') IS NOT NULL
    DROP TABLE dbo.tbl
GO

CREATE TABLE dbo.tbl (b INT, a INT)
GO
INSERT INTO dbo.tbl VALUES (99, 1), (4, 3)
GO

EXEC dbo.GetLastRecord
GO

Value       RecordID
----------- -----------
99          1

Потому что сортировка будет идти уже по другому столбцу. Однако это не самое страшное.

Тяжелее всего отловить ошибку, если меняется порядок столбцов в таблице, при этом вставка в нее происходит без явного указания колонок:

USE AdventureWorks2014
GO

IF OBJECT_ID('dbo.AWBuildVersion2', 'U') IS NOT NULL
    DROP TABLE dbo.AWBuildVersion2
GO
CREATE TABLE dbo.AWBuildVersion2 (
      InfoID TINYINT PRIMARY KEY
    , VersionDate DATE NOT NULL
    , ModifiedDate DATE NOT NULL
)
GO

INSERT INTO dbo.AWBuildVersion2
SELECT SystemInformationID
     , VersionDate
     , ModifiedDate
FROM dbo.AWBuildVersion
GO

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

IF OBJECT_ID('dbo.AWBuildVersion2', 'U') IS NOT NULL
    DROP TABLE dbo.AWBuildVersion2
GO
CREATE TABLE dbo.AWBuildVersion2 (
      InfoID TINYINT PRIMARY KEY
    , ModifiedDate DATE NOT NULL
    , VersionDate DATE NOT NULL
)
GO

INSERT INTO dbo.AWBuildVersion2
SELECT SystemInformationID
     , VersionDate
     , ModifiedDate
FROM dbo.AWBuildVersion
GO

В этом случае у нас данные будут записываться уже некорректные:

InfoID VersionDate ModifiedDate
------ ----------- ------------
1      2014-02-20  2014-07-08

InfoID VersionDate ModifiedDate
------ ----------- ------------
1      2014-07-08  2014-02-20

Поэтому считается хорошим тоном не лениться и всегда явно указывать столбцы в конструкции INSERT:

INSERT INTO dbo.AWBuildVersion2 (InfoID, VersionDate, ModifiedDate)
SELECT SystemInformationID
     , VersionDate
     , ModifiedDate
FROM dbo.AWBuildVersion

Data length

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

DECLARE @a DECIMAL
      , @b VARCHAR(10) = '0.1'
      , @c SQL_VARIANT

SELECT @a = @b
     , @c = @a

SELECT @a
     , @c
     , SQL_VARIANT_PROPERTY(@c,'BaseType')
     , SQL_VARIANT_PROPERTY(@c,'Precision')
     , SQL_VARIANT_PROPERTY(@c,'Scale')

В чем суть данной проблемы? Явно не указали размерность типа и вместо дробного значения получаем «вроде целое»:

---- ---- ---------- ----- -----
0    0    decimal    18    0

Со строками все еще веселее:

DECLARE @t1 VARCHAR(MAX) = '123456789_123456789_123456789_123456789_'
DECLARE @t2 VARCHAR = @t1

SELECT LEN(@t1)
     , @t1
     , LEN(@t2)
     , @t2
     , LEN(CONVERT(VARCHAR, @t1))
     , LEN(CAST(@t1 AS VARCHAR))

Если явно не указывается размерность, то у строки длина будет 1 символ:

----- ------------------------------------------ ---- ---- ---- ----
40    123456789_123456789_123456789_123456789_   1    1    30   30

При этом поведение преобразовании типов имеет свою особенность: не указали размерность в CAST/CONVERT, то браться будут первые 30 символов.

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

STRING_CONCAT

Я бы мог посоветовать использовать функцию STRING_CONCAT, если бы она была... На дворе 2016 год, а отдельной функции, чтобы склеивать строки, в SQL Server так и не добавили. Нужно же как-то выходить из положения?

Рассмотрим пару наиболее популярных вариантов, но вначале создадим тестовую таблицу:

IF OBJECT_ID('tempdb.dbo.#t') IS NOT NULL
    DROP TABLE #t
GO

CREATE TABLE #t (i CHAR(1))
INSERT INTO #t
VALUES ('1'), ('2'), ('3')

И начнем с моего «любимца» — конкатенация строк через присваивание значений в переменную:

DECLARE @txt VARCHAR(50) = ''
SELECT @txt += i
FROM #t
--ORDER BY i

SELECT @txt

--------
123

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

DECLARE @txt VARCHAR(50) = ''
SELECT @txt += i
FROM #t
ORDER BY LEN(i)

SELECT @txt

--------
3

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

На практике, в 90% случаев достаточно использовать XML:

SELECT [text()] = i
FROM #t
FOR XML PATH('')

--------
123

Однако и тут нас может поджидать пара нюансов. Во-первых, очень часто необходимо склеить строки в разрезе каких-то данных, а не все в одно:

SELECT t.name
     , STUFF((
            SELECT ', ' + c.name
            FROM sys.columns c
            WHERE c.[object_id] = t.[object_id]
            FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 2, '')
FROM sys.objects t
WHERE t.[type] = 'U'

------------------------ ------------------------------------
ScrapReason              ScrapReasonID, Name, ModifiedDate
Shift                    ShiftID, Name, StartTime, EndTime

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

Теперь пробуем не использовать value:

SELECT t.name
     , STUFF((
            SELECT ', ' + c.name
            FROM sys.columns c
            WHERE c.[object_id] = t.[object_id]
            FOR XML PATH('')), 1, 2, '')
FROM sys.objects t
WHERE t.[type] = 'U'

И такой вариант будет работать хорошо и быстро, если не одно «но». Попробуйте выполнить вот такой запрос:

SELECT t.name
     , STUFF((
            SELECT ', ' + CHAR(13) + c.name
            FROM sys.columns c
            WHERE c.[object_id] = t.[object_id]
            FOR XML PATH('')), 1, 2, '')
FROM sys.objects t
WHERE t.[type] = 'U'

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

В итоге у нас два варианта: если спецсимволов нет, то использовать вариант запроса без метода value, в противном случае обратить внимание на более ресурсоемкий план.

Subquery

Без лишних прелюдий выполним запрос:

USE AdventureWorks2014
GO

SELECT p.BusinessEntityID
     , (
        SELECT s.SalesQuota
        FROM Sales.SalesPersonQuotaHistory s
        WHERE s.BusinessEntityID = p.BusinessEntityID
      )
FROM Person.Person p

и получим ошибку:

Msg 512, Level 16, State 1, Line 6
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

Как решаются подобные проблемы? Элементарно — добавляется TOP(1), и проблема ушла. Однако не все так просто, как может показаться. Использование операции TOP(...) заставляет оптимизатор форсировать использование IndexSeek. К таким же последствиям приводит использованием OUTER/CROSS APPLY вместе с TOP.

К примеру, есть запросы:

SELECT p.BusinessEntityID
     , (
        SELECT TOP(1) s.SalesQuota
        FROM Sales.SalesPersonQuotaHistory s
        WHERE s.BusinessEntityID = p.BusinessEntityID
        ORDER BY s.QuotaDate DESC
      )
FROM Person.Person p

SELECT p.BusinessEntityID
     , t.SalesQuota
FROM Person.Person p
OUTER APPLY (
    SELECT TOP(1) s.SalesQuota
    FROM Sales.SalesPersonQuotaHistory s
    WHERE s.BusinessEntityID = p.BusinessEntityID
    ORDER BY s.QuotaDate DESC
) t

У них одна и та же проблема на плане выполнения:

Table 'SalesPersonQuotaHistory'. Scan count 19972, logical reads 39944, ...
Table 'Person'. Scan count 1, logical reads 67, ...

Вооружившись оконной функцией, перепишем запрос :

SELECT p.BusinessEntityID
     , t.SalesQuota
FROM Person.Person p
LEFT JOIN (
    SELECT s.BusinessEntityID
         , s.SalesQuota
         , RowNum = ROW_NUMBER() OVER (PARTITION BY s.BusinessEntityID ORDER BY s.QuotaDate DESC)
    FROM Sales.SalesPersonQuotaHistory s
) t ON p.BusinessEntityID = t.BusinessEntityID AND t.RowNum = 1

И посмотрим что изменилось:

Table 'Person'. Scan count 1, logical reads 67, ...
Table 'SalesPersonQuotaHistory'. Scan count 1, logical reads 4, ...

Думаю, что комментарии излишни. Поэтому перейдем к десерту.

@TableVariable vs #TempTable

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

На самом деле табличные переменные и временные таблицы физически хранятся одинаково — как таблицы в базе tempdb. Исключение составляют InMemory табличные типы.

USE [master]
GO

IF OBJECT_ID('tempdb.dbo.#t') IS NOT NULL
    DROP TABLE #t
GO

SELECT COUNT(*) FROM tempdb.sys.tables WHERE [name] LIKE '#_%'
GO

DECLARE @t TABLE (id INT)

SELECT COUNT(*) FROM tempdb.sys.tables WHERE [name] LIKE '#_%'

CREATE TABLE #t (id INT)

SELECT COUNT(*) FROM tempdb.sys.tables WHERE [name] LIKE '#_%'

-----------
0

-----------
1

-----------
2

На этом основное сходство заканчивается, и начинаются грабли. Сперва мои самые любимые, которые часто попадаются на собеседованиях Middle/Senior DB Developer:

USE [master]
GO

IF OBJECT_ID('tempdb.dbo.#t') IS NOT NULL
    DROP TABLE #t
GO

DECLARE @t TABLE (id INT)
CREATE TABLE #t (id INT)

BEGIN TRANSACTION

INSERT INTO @t VALUES (2)
INSERT INTO #t VALUES (2)

ROLLBACK

SELECT COUNT(*) FROM @t
SELECT COUNT(*) FROM #t

-----------
1
-----------
0

Временные таблицы работают по аналогии с обычными таблицами и отражают все изменения в логе, которые мы и откатили с помощью ROLLBACK.

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

DECLARE @t TABLE (id INT PRIMARY KEY)
INSERT INTO @t VALUES (1)

INSERT INTO @t VALUES (1), (2)

SELECT * FROM @t

Msg 2627, Level 14, State 1, Line 4
Violation of PRIMARY KEY constraint 'PK__#AD25707__3213E83F1AADB62D'. Cannot insert duplicate key in object 'dbo.@t'. The duplicate key value is (1).

id
-----------
1

В чем еще разница? Перечислять много, но мы остановимся на самом главном. Во временных таблицах все по аналогии с обычными таблицами — используется статистика и показывается кардинальность, более или менее адекватная:

USE AdventureWorks2014
GO

IF OBJECT_ID('tempdb.dbo.#SalesOrderDetail') IS NOT NULL
    DROP TABLE #SalesOrderDetail

CREATE TABLE #SalesOrderDetail (ProductID INT PRIMARY KEY)

INSERT INTO #SalesOrderDetail (ProductID)
SELECT DISTINCT ProductID
FROM Sales.SalesOrderDetail

SET STATISTICS IO ON

SELECT *
FROM Production.Product p
WHERE p.ProductID IN (SELECT s.ProductID FROM #SalesOrderDetail s)

SET STATISTICS IO OFF

Table 'Product'. Scan count 1, logical reads 15, ...
Table '#SalesOrderDetail_000000000030'. Scan count 1, logical reads 2, ...

У табличных переменных нет статистики, и кардинальность всегда равна единице:

USE AdventureWorks2014
GO

DECLARE @SalesOrderDetail TABLE (ProductID INT PRIMARY KEY)

INSERT INTO @SalesOrderDetail (ProductID)
SELECT DISTINCT ProductID
FROM Sales.SalesOrderDetail

SET STATISTICS IO ON

SELECT *
FROM Production.Product p
WHERE p.ProductID IN (SELECT s.ProductID FROM @SalesOrderDetail s)

SET STATISTICS IO OFF
GO

Table 'Product'. Scan count 0, logical reads 532, ...
Table '#A28D5CEC'. Scan count 1, logical reads 2, ...

Именно по этой причине при использовании табличной переменной могут строиться не оптимальные планы выполнения:

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

А вот лечить данную проблему можно по-разному: использовать временные таблицы либо избирательно добавлять OPTION(RECOMPILE) к проблемным запросам. Хотя постойте, еще стоит упомянуть про trace flag 2453, который можно использовать, начиная с SQL Server 2012 SP2 и SQL Server 2014 CU3.

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