Что может подстерегать новичков при работе с SQL Server. Часть 2
В отличие от первой статьи, никаких Жозефин, уточек, пьяных альпинистов с комплексом «трёхлитровки» и прочих веселостей тут уже не будет. И не потому, что количество историй вдруг решило уменьшить свое поголовье. Причина кроется в другом: работа с базой данных — это не всегда сырок моцарелла и смузи после
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 19Invalid 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 6Subquery 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 4Violation 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.
На этом пока все. Знаю, конечно, что за бортом осталось еще много чего интересного, но все же надеюсь, что этот поток мыслей кому-то будет полезным.
