Качки, Таїланд і T-SQL... або що може підстерігати програмістів при роботі з SQL Server?



Все починалося досить буденно… Зачитувався Ріхтером і посилено вивчав Шилдта. Думав, що буду займатися розробкою під .NET, але доля на першому місяці роботи розпорядилася інакше. Один із співробітників несподівано покинув проект і у знову утворену дірку докинули свіжого людського матеріалу. Саме тоді й почалося моє знайомство з SQL Server.

З тих пір пройшло трохи менше 6 років і згадати можна багато чого…

Про колишнього клієнта Джозефа з Англії, який переосмислив життя, за час відпустки в Таїланді, і в моєму скайпі став підписуватися Жозефіною. Про веселих сусідів по офісу, з якими доводилося сидіти в одній кімнаті: один страждав від алергії на свіже повітря, а інший маявся від нерозділеного кохання до С++ доповнюючи це алергією на сонячне світло. Один раз по команді понад довелося на деякий час стати Олександром батьком двох дітей, щоб зображати з себе котрий обріс скилами сениора JS.

Але найлютіший треш, напевно, пов'язаний з історією про гумову качку-пищалку. Один колега знімав нею стрес і, одного разу, в пориві емоцій, відгриз їй голову. З тих пір качечка втратила колишній лиск і незабаром була замінена на м'ячик, який він намагався іноді гризти… на жаль, вже безуспішно.

До чого це було сказано? Якщо хочете присвятити своє життя роботі з базами даних, то перше, чому потрібно навчитися… так це стресостійкості. Друге – це взяти на озброєння кілька правил при написанні запитів на T-SQL, які багато хто з початківців розробників не знають або просто ігнорують, а потім сидять і ламають голову… чому щось не працює?

1. Data Types
Саме основне, з чого починається більшість проблем при роботі з SQL Server — це неправильний вибір типів даних. Візьмемо гіпотетичний приклад з двома ідентичними за своєю суттю таблицями:

DECLARE @Employees1 TABLE (
EmployeeID BIGINT PRIMARY KEY
, IsMale VARCHAR(3)
, BirthDate VARCHAR(20)
)
INSERT INTO @Employees1
VALUES (123, 'YES', '2012-09-01')

DECLARE @Employees2 TABLE (
EmployeeID INT PRIMARY KEY
, IsMale BIT
, BirthDate DATE
)
INSERT INTO @Employees2
VALUES (123, 1, '2012-09-01')

Виконаємо запит і подивимося в чому різниця:

DECLARE @BirthDate DATE = '2012-09-01'

SELECT * FROM @Employees1 WHERE BirthDate = @BirthDate
SELECT * FROM @Employees2 WHERE BirthDate = @BirthDate



У першому випадку, типи даних надлишкові, ніж могли б бути. Навіщо зберігати бітовий ознака як рядок YES/NO? Навіщо зберігати дату як рядок? Навіщо BIGINT по таблиці з співробітниками? Ніж простий INT не підійшов?

Це погано з кількох причин: таблиці будуть займати більше місця на диску, потрібно більше сторінок прочитати з диска і більше сторінок розмістити в BufferPool щоб оперувати цими даними. Крім того, можуть бути і ще серйозні проблеми з продуктивністю — знак питання про це легко натякає, але про це поговоримо пізніше.

2. *
Часто доводилося зустрічати «картину маслом»: з таблиці беруться всі дані, а потім на клієнта через DataReader вибираються тільки ті стовпці, які реально потрібні. Це вкрай не ефективно, тому краще не використовувати подібної практики:

USE AdventureWorks2014
GO

SET STATISTICS TIME, IO ON

SELECT *
FROM Person.Person

SELECT BusinessEntityID
, FirstName
, MiddleName
, LastName
FROM Person.Person

SET STATISTICS TIME, IO OFF

Різниця буде і під час виконання запиту і в тому, що буде можливість зробити менше логічних читань за рахунок покриває індексу:

Table 'Person'. Scan count 1, logical reads 3819, physical reads 3, ...
SQL Server Execution Times:
CPU time = 31 ms, elapsed time = 1235 ms.

Table 'Person'. Scan count 1, logical reads 109, physical reads 1, ...
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 227 ms.

3. Alias
Створимо таблицю:

USE AdventureWorks2014
GO

IF OBJECT_ID('Sales.UserCurrency') IS NOT NULL
DROP TABLE Sales.UserCurrency
GO

CREATE TABLE Sales.UserCurrency (
CurrencyCode NCHAR(3) PRIMARY KEY
)
INSERT INTO Sales.UserCurrency
VALUES ('USD')

Припустимо, у нас є запит, який повертає кількість ідентичних рядків в обох таблицях:

SELECT COUNT_BIG(*)
FROM Sales.Currency
WHERE CurrencyCode IN (
SELECT CurrencyCode
FROM Sales.UserCurrency
)

І все буде працювати, як ми очікуємо, до тих пір, поки хтось не захоче перейменувати стовпець у таблиці Sales.UserCurrency:

EXEC sys.sp_rename 'Sales.UserCurrency.CurrencyCode', 'Code', 'COLUMN'

Виконаємо запит і побачимо, що повертається не 1 рядок, а все що є в Sales.Currency. При побудові плану виконання SQL Server на етапі биндинга подивиться на стовпці Sales.UserCurrency не знайде там CurrencyCode і подумає що цей стовпець відноситься до таблиці Sales.Currency після чого оптимізатор умова CurrencyCode = CurrencyCode відкине.

Мораль — використовуйте аліаси:

SELECT COUNT_BIG(*)
FROM Sales.Currency c
WHERE c.CurrencyCode IN (
SELECT u.CurrencyCode
FROM Sales.UserCurrency u
)

4. Column order
Припустимо, у нас є якась таблиця:

IF OBJECT_ID('dbo.DatePeriod') IS NOT NULL
DROP TABLE dbo.DatePeriod
GO

CREATE TABLE dbo.DatePeriod (
StartDate DATE
, A list DATE
)

І дані в неї ми завжди вставляємо з того припущення, що ми знаємо як по порядку розташовуються стовпці:

INSERT INTO dbo.DatePeriod
SELECT '2015-01-01', '2015-01-31'

Потім в один прекрасний момент, хтось змінить порядок стовпців:

CREATE TABLE dbo.DatePeriod (
A list DATE
, StartDate DATE
)

І дані будуть вже вставлятися не в ті стовпці які очікує розробник. Тому рекомендується завжди явно вказувати стовпці в конструкції INSERT:

INSERT INTO dbo.DatePeriod (StartDate, A List)
SELECT '2015-01-01', '2015-01-31'

Є ще один цікавий приклад:

SELECT TOP(1) *
FROM dbo.DatePeriod
ORDER BY 2 DESC

За яким стовпцем буде йти сортування? А все залежить від поточного порядку в таблиці. Якщо хто-то його змінить, то запит буде виводити не те, що ми очікуємо.

5. NOT IN vs NULL
Безперечний лідер серед запитань на співбесіді Junior DB Developer — конструкція NOT IN.

Наприклад, потрібно написати пару запитів: повернути всі записи з першої таблиці, яких немає у другій, і навпаки. Дуже часто початківці розробники не заморочуються і використовують IN NOT IN:

DECLARE @t1 TABLE (t1 INT, UNIQUE CLUSTERED(t1))
INSERT INTO @t1 VALUES (1), (2)

DECLARE @t2 TABLE (t2 INT, UNIQUE CLUSTERED(t2))
INSERT INTO @t2 VALUES (1)

SELECT *
FROM @t1
WHERE t1 NOT IN (SELECT t2 FROM @t2)

SELECT *
FROM @t1
WHERE t1 IN (SELECT t2 FROM @t2)

Перший запит повернув нам двійку, другий — одиницю. Давайте тепер у другу таблицю додамо ще одне значення — NULL:

INSERT INTO @t2 VALUES (1), (NULL)

При виконанні запиту NOT IN ми не отримаємо ніяких результатів. Невже якась магія втрутилася IN працює, а NOT IN відмовляється. Це перше, що слід «зрозуміти і пробачити» при роботі з SQL Server, який при операції порівняння керується третинної логікою: TRUE, FALSE, UNKNOWN.

При виконанні SQL Server інтерпретує умова IN:

a IN (1, NULL) == a=1 OR a=NULL

NOT IN:

a NOT IN (1, NULL) == a<>1 AND a<>NULL

При порівнянні будь-якого значення NULL повертається UNKNOWN. 1=NULL, NULL=NULL. Результат буде один — UNKNOWN. А оскільки у нас в умови використовується оператор AND, то весь вираз поверне невизначене значення і в результаті буде порожньо.

Написано трохи нудно. Але важливо розуміти, що така ситуація зустрічається досить часто. Наприклад, раніше стовпець був оголошений NOT NULL, потім якийсь добрий чоловік дозволив записувати в неї NULL значення. Підсумок: у клієнта перестає працювати звіт після того, як в таблицю потрапить хоча б одне NULL значення.

Що робити? Можна явно відкидати NULL значення:

SELECT *
FROM @t1
WHERE t1 NOT IN (
SELECT t2
FROM @t2
WHERE t2 IS NOT NULL
)

Можна використовувати EXCEPT:

SELECT * FROM @t1
EXCEPT
SELECT * FROM @t2

Якщо немає бажання багато думати, то простіше використовувати NOT EXISTS:

SELECT *
FROM @t1
WHERE NOT EXISTS(
SELECT 1
FROM @t2
WHERE t1 = t2
)

Який варіант запиту більш оптимальний? Переважніше виглядає останній варіант NOT EXISTS, який генерує більш оптимальний predicate pushdown оператор при доступі до даних з другої таблиці.

Взагалі NULL значеннями багато приколів. Можна погратися з такими запитами:

USE AdventureWorks2014
GO

SELECT COUNT_BIG(*)
FROM Production.Product

SELECT COUNT_BIG(*)
FROM Production.Product
WHERE Color = 'Grey'

SELECT COUNT_BIG(*)
FROM Production.Product
WHERE Color <> 'Grey'

і не отримати очікуваного результату тільки тому, що для NULL значень передбачені окремі оператори порівняння:

SELECT COUNT_BIG(*)
FROM Production.Product
WHERE Color IS NULL

SELECT COUNT_BIG(*)
FROM Production.Product
WHERE Color IS NOT NULL

Ще курйозні виглядає ситуація з CHECK констрейнтами:

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

CREATE TABLE #temp (
Color VARCHAR(15) --NULL
, CONSTRAINT CK CHECK (Color IN ('Black', 'White'))
)

Ми створюємо таблицю в яку дозволяємо записувати тільки білі і чорні кольори:

INSERT INTO #temp VALUES ('Black')

(1 row(s) affected)

Все працює як ми очікуємо:

INSERT INTO #temp VALUES ('Red')

INSERT The statement conflicted with the CHECK constraint...
The statement has been terminated.

Але давайте вставимо NULL:

INSERT INTO #temp VALUES (NULL)

(1 row(s) affected)

Наш CHECK констрейнт не спрацював, тому що для запису достатньо умови NOT FALSE, тобто TRUE UNKNOWN підходять за милу душу. Є кілька варіантів обійти цю особливість поведінки: явно оголошувати стовпець NOT NULL або враховувати NULL в обмеженні.

6. Date format
Ще часто спотикаються на різних нюансах з типами даних. Наприклад, потрібно отримати поточний час. Виконали функцію GETDATE:

SELECT GETDATE()

Скопіювали результат, вставили його в запит як є і прибрали час:

SELECT *
FROM sys.objects
WHERE create_date < '2016-11-14'

Чи правильно так робити?

Дата задається строкової константою, і в деякій мірі SQL Server дозволяє вільності при її написанні:

SET LANGUAGE English
SET DATEFORMAT DMY

DECLARE @d1 DATETIME = '05/12/2016'
, @d2 DATETIME = '2016/12/05'
, @d3 DATETIME = '2016-12-05'
, @d4 DATETIME = '05-dec-2016'

SELECT @d1, @d2, @d3, @d4

Всі значення практично скрізь однозначно інтерпретуються:

----------- ----------- ----------- -----------
2016-12-05 2016-05-12 2016-05-12 2016-12-05 

І це не буде призводити до проблем до тих пір, поки запит з такою бізнес-логікою не почнуть виконувати на іншому сервері, на якому налаштування можуть відрізнятися:

SET DATEFORMAT MDY

DECLARE @d1 DATETIME = '05/12/2016'
, @d2 DATETIME = '2016/12/05'
, @d3 DATETIME = '2016-12-05'
, @d4 DATETIME = '05-dec-2016'

SELECT @d1, @d2, @d3, @d4

Всі ці варіанти можуть призвести до невірного тлумачення дати:

----------- ----------- ----------- -----------
2016-05-12 2016-12-05 2016-12-05 2016-12-05 

Більше того, такий код може призвести до помилки як явної, так і прихованої. Наприклад, нам потрібно вставити дані в таблицю. На тестовому сервері все прекрасно працює:

DECLARE @t TABLE (a DATETIME)
INSERT INTO @t VALUES ('05/13/2016')

А у клієнта, з-за різниці в налаштуваннях сервера, ось такий запит буде призводити до проблем:

DECLARE @t TABLE (a DATETIME)
SET DATEFORMAT DMY
INSERT INTO @t VALUES ('05/13/2016')

Msg 242, 16 Level, State 3, Line 28
The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.

Так в якому ж форматі задавати константи для дат? Давайте подивимося на ще один приклад:

SET DATEFORMAT YMD

SET LANGUAGE English

DECLARE @d1 DATETIME = '2016/01/12'
, @d2 DATETIME = '2016-01-12'
, @d3 DATETIME = '12-jan-2016'
, @d4 DATETIME = '20160112'

SELECT @d1, @d2, @d3, @d4
GO

SET LANGUAGE Deutsch

DECLARE @d1 DATETIME = '2016/01/12'
, @d2 DATETIME = '2016-01-12'
, @d3 DATETIME = '12-jan-2016'
, @d4 DATETIME = '20160112'

SELECT @d1, @d2, @d3, @d4

В залежності від встановленого мови, константи також можуть по-різному інтерпретуватися:

----------- ----------- ----------- -----------
2016-01-12 2016-01-12 2016-01-12 2016-01-12 

----------- ----------- ----------- -----------
2016-12-01 2016-12-01 2016-01-12 2016-01-12 

І напрошується висновок використовувати останні два варіанти. Відразу скажу, що явно задавати місяць — це хороша можливість натрапити на «же не манж па сис жур» помилку:

SET LANGUAGE French
DECLARE @d DATETIME = '12-jan-2016'

Msg 241, 16 Level, State 1, Line 29
Échec de la conversion de la date et/ou de l'heure à partir d'une chaîne de caractères.

Отже, залишається останній варіант. Якщо хочете, щоб константи з датами однозначно тлумачилися в системі незалежно від налаштувань і фази Місяця, то вказуйте їх у форматі YYYYMMDD без всяких тильд, лапок і слешів.

Ще варто звернути увагу на відмінність в поведінці деяких типів даних:

SET LANGUAGE English
SET DATEFORMAT YMD

DECLARE @d1 DATE = '2016-01-12'
, @d2 DATETIME = '2016-01-12'

SELECT @d1, @d2
GO

SET LANGUAGE Deutsch
SET DATEFORMAT DMY

DECLARE @d1 DATE = '2016-01-12'
, @d2 DATETIME = '2016-01-12'

SELECT @d1, @d2

На відміну від DATETIME тип DATE коректно інтерпретується при різних настройках на сервері:

---------- ----------
2016-01-12 2016-01-12

---------- ----------
2016-01-12 2016-12-01

Але потрібно тримати цей нюанс в голові? Навряд чи. Головне пам'ятайте, що задавати потрібно дати в форматі YYYYMMDD і не буде ніяких проблем.

7. Date filter
Далі розглянемо, як ефективно фільтрувати дані. Чомусь на DATETIME/DATE стовпці припадає найбільша кількість милиць, так що з цього типу даних ми і почнемо:

USE AdventureWorks2014
GO

UPDATE TOP(1) dbo.DatabaseLog
SET PostTime = '20140716 12:12:12'

Тепер спробуємо дізнатися, скільки рядків поверне запит за певний день:

SELECT COUNT_BIG(*)
FROM dbo.DatabaseLog
WHERE PostTime = '20140716'

Запит поверне 0. Чому? При побудові плану SQL Server намагається перетворити строкову константу до типу даних стовпця, по якому йде фільтрація:



Створимо індекс:

CREATE NONCLUSTERED INDEX IX_PostTime ON dbo.DatabaseLog (PostTime)

Є правильні і неправильні варіанти вивести необхідні дані. Наприклад, обрізати час:

SELECT COUNT_BIG(*)
FROM dbo.DatabaseLog
WHERE CONVERT(CHAR(8), PostTime, 112) = '20140716'

SELECT COUNT_BIG(*)
FROM dbo.DatabaseLog
WHERE CAST(PostTime AS DATE) = '20140716'

Або задати діапазон:

SELECT COUNT_BIG(*)
FROM dbo.DatabaseLog
WHERE PostTime BETWEEN '20140716' AND '20140716 23:59:59.997'

SELECT COUNT_BIG(*)
FROM dbo.DatabaseLog
WHERE PostTime >= '20140716' AND PostTime < '20140717'

Саме останні два запиту більш правильні з точки зору оптимізації. І справа в тому, що всі перетворення і обчислення на індексних стовпцях, за яким йде пошук, може різко знижувати продуктивність і збільшувати логічні читання (перший і останні три варіанта запиту):

Table 'DatabaseLog'. Scan count 1, logical reads 7, ...
Table 'DatabaseLog'. Scan count 1, logical reads 2, ...

Поле PostTime раніше не входило в індекс, і особливого ефекту від використання «правильного» підходу при фільтрації ми б не змогли побачити. Інша справа, коли нам потрібно вивести дані за місяць. Чого тільки не доводилося бачити:

SELECT COUNT_BIG(*)
FROM dbo.DatabaseLog
WHERE CONVERT(CHAR(8), PostTime, 112) LIKE '201407%'

SELECT COUNT_BIG(*)
FROM dbo.DatabaseLog
WHERE DATEPART(YEAR, PostTime) = 2014
AND DATEPART(MONTH, PostTime) = 7

SELECT COUNT_BIG(*)
FROM dbo.DatabaseLog
WHERE YEAR(PostTime) = 2014
AND MONTH(PostTime) = 7

SELECT COUNT_BIG(*)
FROM dbo.DatabaseLog
WHERE EOMONTH(PostTime) = '20140731'

SELECT COUNT_BIG(*)
FROM dbo.DatabaseLog
WHERE PostTime >= '20140701' AND PostTime < '20140801'

І знову ж, останній варіант більш прийнятний, ніж всі інші:



Крім того, завжди можна зробити обчислюване поле і створити на його основі індекс:

IF COL_LENGTH('dbo.DatabaseLog', 'MonthLastDay') IS NOT NULL
ALTER TABLE dbo.DatabaseLog DROP COLUMN MonthLastDay
GO

ALTER TABLE dbo.DatabaseLog
ADD MonthLastDay AS EOMONTH(PostTime) --PERSISTED
GO

CREATE INDEX IX_MonthLastDay ON dbo.DatabaseLog (MonthLastDay)

У порівнянні з минулим запитом різниця в логічних читаннях буде суттєва (якщо ми говоримо про великі таблиці):

SET STATISTICS ON IO

SELECT COUNT_BIG(*)
FROM dbo.DatabaseLog
WHERE PostTime >= '20140701' AND PostTime < '20140801'

SELECT COUNT_BIG(*)
FROM dbo.DatabaseLog
WHERE MonthLastDay = '20140731'

SET STATISTICS IO OFF

Table 'DatabaseLog'. Scan count 1, logical reads 7, ...
Table 'DatabaseLog'. Scan count 1, logical reads 3, ...

8. Сalculation
Як я вже говорив, будь-які обчислення на індексних полях знижують продуктивність і призводять до збільшення логічних читань:

USE AdventureWorks2014
GO

SET STATISTICS ON IO

SELECT BusinessEntityID
FROM Person.Person
WHERE BusinessEntityID * 2 = 10000

SELECT BusinessEntityID
FROM Person.Person
WHERE BusinessEntityID = 2500 * 2

SELECT BusinessEntityID
FROM Person.Person
WHERE BusinessEntityID = 5000

Table 'Person'. Scan count 1, logical reads 67, ...
Table 'Person'. Scan count 0, logical reads 3, ...

Якщо поглянути на виконання планів, то в першому випадку SQL Server доводиться виконати IndexScan:



У другому і третьому випадку, коли обчислення на індексному полі, ні ми побачимо IndexSeek:



9. Convert implicit
Для початку подивимося на ці два запити, які фільтрують по одному і тому ж значенню:

USE AdventureWorks2014
GO

SELECT BusinessEntityID, NationalIDNumber
FROM HumanResources.Employee
WHERE NationalIDNumber = 30845

SELECT BusinessEntityID, NationalIDNumber
FROM HumanResources.Employee
WHERE NationalIDNumber = '30845'

Якщо подивитися на плани виконання:



У першому випадку — попередження і IndexScan, у другому — IndexSeek:

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

Що сталося? Стовпець NationalIDNumber має тип даних NVARCHAR(15). Константу, за значенням якої необхідно відфільтрувати дані, ми передаємо INT і в результаті отримуємо неявне перетворення типів, яке може знижувати продуктивність. Таке дуже часто відбувається, коли хтось змінює тип даних на стовпці, але при цьому запити залишаються колишніми.

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

SELECT AddressID
FROM Person.[Address]
WHERE PostalCode = 92700

SELECT AddressID
FROM Person.[Address]
WHERE PostalCode = '92700'

Msg 245, 16 Level, State 1, Line 16
Conversion failed when converting the nvarchar value 'K4B 1S2' to data type int.

Ще цікавіше, коли на проекті використовується EntityFramework, який всі рядкові поля за замовчуванням інтерпретує Unicode:

SELECT CustomerID, AccountNumber
FROM Sales.Customer
WHERE AccountNumber = 'AW00000009'

SELECT CustomerID, AccountNumber
FROM Sales.Customer
WHERE AccountNumber = 'AW00000009'

І в результаті у нас генеруються не зовсім оптимальні запити:



Рішення проблеми досить просте — потрібно контролювати, щоб типи даних при порівнянні збігалися.

10. LIKE & Suppressed index
Навіть коли у вас є покриває індекс, ще не факт що він буде ефективно використовуватися. Наприклад, нам потрібно вивести всі рядки, які починаються з…

USE AdventureWorks2014
GO

SET STATISTICS ON IO

SELECT AddressLine1
FROM Person.[Address]
WHERE SUBSTRING(AddressLine1, 1, 3) = '100'

SELECT AddressLine1
FROM Person.[Address]
WHERE LEFT(AddressLine1, 3) = '100'

SELECT AddressLine1
FROM Person.[Address]
WHERE CAST(AddressLine1 AS CHAR(3)) = '100'

SELECT AddressLine1
FROM Person.[Address]
WHERE AddressLine1 LIKE '100%'

Ми отримаємо такі логічні читання:

Table 'Address'. Scan count 1, logical reads 216, ...
Table 'Address'. Scan count 1, logical reads 216, ...
Table 'Address'. Scan count 1, logical reads 216, ...
Table 'Address'. Scan count 1, logical reads 4, ...

Плани виконання, за яким швидко можна знайти переможця:



Результат є підсумком, про що ми так довго говорили до цього. Якщо є індекс, то на ньому не повинно бути ніяких обчислень і перетворень типів, функцій та іншого. Тільки тоді він буде ефективно використовуватися SQL Server.

Але що якщо потрібно знайти всі входження підрядка в рядок? Це задачка вже явно цікавіше:

SELECT AddressLine1
FROM Person.[Address]
WHERE AddressLine1 LIKE '%100%'

Але спочатку нам потрібно дізнатися багато чого цікавого про рядки та їх властивості.

11. Unicode vs ANSI
Перше, що потрібно пам'ятати — рядки бувають UNICODE ANSI. Для перших передбачені типи даних NVARCHAR/NCHAR (по 2 байти на символ — на жаль UTF8 не завезли). Для зберігання ANSI рядків VARCHAR/CHAR (1 байт — 1 символ). Є ще TEXT/n-текст, але про них краще забути спочатку (тому що при їх використанні можна істотно знизити продуктивність).

І начебто на цьому можна було закінчити, але немає…

Якщо в запиті визначається юникодная константа, то перед нею треба обов'язково ставити символ N. Щоб показати різницю, достатньо простого запиту:

SELECT '文本 ANSI'
, '文本 UNICODE'

------- ------------
?? ANSI 文本 UNICODE

Якщо не вказувати N перед константою, то SQL Server буде намагатися шукати відповідний символ ANSI кодуванні. Якщо не знайде, то підставить знак питання.

12. COLLATE
Згадався один дуже цікавий приклад, який люблять запитувати при співбесіді на позицію Middle/Senior DB Developer. Чи поверне дані наступний запит?

DECLARE @a NCHAR(1) = 'Е'
, @b NCHAR(1) = 'Ф'

SELECT @a, @b
WHERE @a = @b

І так… та ні… Тут як пощастить. Зазвичай я так відповідаю.

Чому такий неоднозначний відповідь? По-перше, перед строковим константами не варто N, тому вони не будуть тлумачитись як ANSI. Друге — дуже багато залежить від поточного COLLATE, який є набором правил при сортування і порівняння строкових даних.

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_100_CI_AS
GO

USE test
GO

DECLARE @a NCHAR(1) = 'Е'
, @b NCHAR(1) = 'Ф'

SELECT @a, @b
WHERE @a = @b

При такому COLLATE замість кирилиці ми отримаємо знаки питань, тому що символи знаку питання рівні між собою:

---- ----
? ?

Варто нам поміняти COLLATE на який-небудь інший:

ALTER DATABASE test COLLATE Cyrillic_General_100_CI_AS

І запит вже не поверне нічого, тому що кирилиця буде правильно інтерпретуватися.

Тому мораль тут проста: якщо символьна константа повинна приймати UNICODE, то не треба лінуватися ставити N перед нею. Є ще й зворотній бік медалі, коли N ліпитися скрізь, де можна, і оптимізатору доводиться виконувати перетворення типів, які, як я вже говорив, призводять до неоптимальним планам виконання (це було показано вище).

Що ще я забув згадати про рядка? Ще один хороший питання з циклу «давайте проведемо співбесіду»:

DECLARE
@a VARCHAR(10) = 'TEXT' 
, @b VARCHAR(10) = 'text'

SELECT IIF(@a = @b, 'TRUE', 'FALSE')

Ці рядки рівні? І так… ні… Знову відповів я. Якщо ми хочемо однозначного порівняння, то потрібно явно вказувати COLLATE:

DECLARE
@a VARCHAR(10) = 'TEXT' 
, @b VARCHAR(10) = 'text'

SELECT IIF(@a COLLATE Latin1_General_CS_AS = @b COLLATE Latin1_General_CS_AS, 'TRUE', 'FALSE')

Тому що COLLATE можуть бути як регістрозалежними (CS), так і не враховувати регістр (CI) при порівнянні і сортування рядків. Різні COLLATE у клієнта і на тестовій базі — це потенційне джерело не тільки логічних помилок в бізнес-логікою.

Ще веселіше, коли COLLATE цільової між базою і tempdb не збігаються. Створимо базу COLLATE, відмінним від дефолтного:

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 Albanian_100_CS_AS
GO

USE test
GO

CREATE TABLE t (c CHAR(1))
INSERT INTO t VALUES ('a')
GO

IF OBJECT_ID('tempdb.dbo.#t1') IS NOT NULL
DROP TABLE #t1
IF OBJECT_ID('tempdb.dbo.#t2') IS NOT NULL
DROP TABLE #t2
IF OBJECT_ID('tempdb.dbo.#t3') IS NOT NULL
DROP TABLE #t3
GO

CREATE TABLE #t1 (c CHAR(1))
INSERT INTO #t1 VALUES ('a')

CREATE TABLE #t2 (c CHAR(1) COLLATE database_default)
INSERT INTO #t2 VALUES ('a')

SELECT c = CAST('a' AS CHAR(1))
INTO #t3

DECLARE @t TABLE (c VARCHAR(100))
INSERT INTO @t VALUES ('a')

SELECT 'tempdb', DATABASEPROPERTYEX('tempdb', 'collation')
UNION ALL
SELECT 'test', DATABASEPROPERTYEX(DB_NAME(), 'collation')
UNION ALL
SELECT 't', SQL_VARIANT_PROPERTY(c, 'collation') FROM t
UNION ALL
SELECT '#t1', SQL_VARIANT_PROPERTY(c, 'collation') FROM #t1
UNION ALL
SELECT '#t2', SQL_VARIANT_PROPERTY(c, 'collation') FROM #t2
UNION ALL
SELECT '#t3', SQL_VARIANT_PROPERTY(c, 'collation') FROM #t3
UNION ALL
SELECT '@t', SQL_VARIANT_PROPERTY(c, 'collation') FROM @t

При створенні таблиці COLLATE успадковується від бази даних. Єдина відмінність — для першої тимчасової таблиці, для якої ми визначаємо структуру без вказівки COLLATE. У цьому випадку вона успадковує COLLATE від бази tempdb.

------ --------------------------
tempdb Cyrillic_General_CI_AS
test Albanian_100_CS_AS
t Albanian_100_CS_AS
#t1 Cyrillic_General_CI_AS
#t2 Albanian_100_CS_AS
#t3 Albanian_100_CS_AS
@t Albanian_100_CS_AS

Зараз зупинимося на нашому прикладі з #t1, тому що якщо COLLATE не збігаються — це може призвести до потенційних проблем.

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

SELECT *
FROM #t1
WHERE, c = 'A'

Або SQL Server буде лаятися на неможливість з'єднання таблиць з-за розрізняються COLLATE:

SELECT *
FROM #t1
JOIN t ON [#t1].c = t.c

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

Msg 468, 16 Level, State 9, Line 93
Cannot resolve the collation conflict between "Albanian_100_CS_AS" and "Cyrillic_General_CI_AS" in the equal to operation.

Після чого доводиться робити скрізь милиці:

SELECT *
FROM #t1
JOIN t ON [#t1].c = t.c COLLATE database_default

13. BINARY COLLATE
Тепер, коли «ложка дьогтю» пройдена, подивимося, як можна використовувати COLLATE з користю для себе. Пам'ятаєте приклад про пошук підрядка в рядку?

SELECT AddressLine1
FROM Person.[Address]
WHERE AddressLine1 LIKE '%100%'

Даний запит можна істотно оптимізувати і скоротити час його виконання.

Але для того, щоб була видна різниця, нам потрібно згенерувати велику таблицю:

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_100_CS_AS
GO
ALTER DATABASE test MODIFY FILE NAME = 'test', SIZE = 64MB)
GO
ALTER DATABASE test MODIFY FILE NAME = 'test_log', SIZE = 64MB)
GO

USE test
GO

CREATE TABLE t (
ansi VARCHAR(100) NOT NULL
, unicod NVARCHAR(100) NOT NULL
)
GO

;WITH
E1(N) AS (
SELECT * FROM (
VALUES
(1),(1),(1),(1),(1),
(1),(1),(1),(1),(1)
) t(N)
),
E2(N) AS (SELECT 1 FROM E1 a, E1 b),
E4(N) AS (SELECT 1 FROM E2 a, E2 b),
E8(N) AS (SELECT 1 FROM E4 a, E4 b)
INSERT INTO t
SELECT v, v
FROM (
SELECT TOP(50000) v = REPLACE(CAST(NEWID() AS VARCHAR(36)) + CAST(NEWID() AS VARCHAR(36)), '-', ")
FROM E8
) t

Створимо обчислювані стовпці з бінарними COLLATE, не забувши при цьому створити індекси:

ALTER TABLE t
ADD ansi_bin AS UPPER(ansi) COLLATE Latin1_General_100_Bin2

ALTER TABLE t
ADD unicod_bin AS UPPER(unicod) COLLATE Latin1_General_100_BIN2

CREATE NONCLUSTERED INDEX ansi ON t (ansi)
CREATE NONCLUSTERED INDEX unicod ON t (unicod)

CREATE NONCLUSTERED INDEX ansi_bin ON t (ansi_bin)
CREATE NONCLUSTERED INDEX unicod_bin ON t (unicod_bin)

Виконуємо фільтрацію:

SET STATISTICS TIME, IO ON

SELECT COUNT_BIG(*)
FROM t
WHERE ansi LIKE '%AB%'

SELECT COUNT_BIG(*)
FROM t
WHERE unicod LIKE '%AB%'

SELECT COUNT_BIG(*)
FROM t
WHERE ansi_bin LIKE '%AB%' --COLLATE Latin1_General_100_BIN2

SELECT COUNT_BIG(*)
FROM t
WHERE unicod_bin LIKE '%AB%' --COLLATE Latin1_General_100_BIN2

SET STATISTICS TIME, IO OFF

І можемо побачити результати виконання, які приємно здивують:

SQL Server Execution Times:
CPU time = 350 ms, elapsed time = 354 ms.

SQL Server Execution Times:
CPU time = 335 ms, elapsed time = 355 ms.

SQL Server Execution Times:
CPU time = 16 ms, elapsed time = 18 ms.

SQL Server Execution Times:
CPU time = 17 ms, elapsed time = 18 ms.

Вся суть в тому, що пошук на основі бінарного порівняння відбувається набагато швидше, і якщо потрібно часто і швидко шукати входження рядків, то дані можна зберігати COLLATE, що закінчується на BIN. Єдине, що потрібно пам'ятати все бінарні COLLATE регистрозависимые при порівнянні.

14. 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)

і напишемо такий запит:

select employeeid from employee

Працює? Тепер спробуйте поміняти COLLATE на який-небудь регистрозависимый:

ALTER DATABASE test COLLATE Latin1_General_CS_AI

Та спробуємо виконати запит:

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

Оптимізатор використовує правила поточного COLLATE при побудові плану виконання. Точніше, на етапі биндинга, коли проводиться перевірка на існування таблиць, колонок та інших об'єктів і зіставлення кожного об'єкта синтаксичного дерева з реальним об'єктом системного каталогу.

Якщо хочеться генерувати ручками запити, які будуть працювати скрізь, то потрібно завжди дотримуватися правильного регістра в іменах об'єктів, які використовуються в запиті.

Ще цікавіше справи зі змінними…

Для них COLLATE успадковуються від бази master. Тому потрібно дотримуватися правильний регістр при роботі зі змінними:

SELECT DATABASEPROPERTYEX('master', 'collation')
DECLARE @EmpID INT = 1
SELECT @empid

Помилки швидше за все не буде:

-----------------------
Cyrillic_General_CI_AS

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

При цьому на іншому сервері помилка в регістрі може дати про себе знати:

--------------------------
Latin1_General_CS_AS

Msg 137, Level 15, State 2, Line 4
Must declare the scalar variable "@empid".

15. [var]char
Не секрет, що є рядкові типи даних з фіксованою (CHAR, NCHAR) і змінною довжиною (VARCHAR, NVARCHAR):

DECLARE @a CHAR(20) = 'text'
, @b VARCHAR(20) = 'text'

SELECT LEN(@a)
, LEN(@b)
, DATALENGTH(@a)
, DATALENGTH(@b)
, '"' + @a + '"'
, '"' + @b + '"'

SELECT [a = b] = IIF(@a = @b, 'TRUE', 'FALSE')
, [b = a] = IIF(@b = @a, 'TRUE', 'FALSE')
[a LIKE b] = IIF(@LIKE a @b, 'TRUE', 'FALSE')
, [b LIKE a] = IIF(@b LIKE @a, 'TRUE', 'FALSE')

Якщо рядок має фіксовану довжину скажімо в 20 символів, але в неї записали тільки 4, то в цьому випадку SQL Server автоматично додасть 16 прогалин праворуч (при цьому зверніть увагу функції LEN і DATALENGTH ведуть себе по-різному):

--- --- ---- ---- ---------------------- ----------------------
4 4 20 4 "text " "text"

Крім того, важливо розуміти — при порівнянні рядків через одно прогалини справа не враховуються:

a = b, b = a a LIKE LIKE a b b
----- ----- -------- --------
TRUE TRUE TRUE FALSE

Інша справа оператор LIKE:

SELECT 1
WHERE 'a' LIKE 'a'

SELECT 1
WHERE 'a' LIKE 'a' -- !!!

SELECT 1
WHERE 'a' LIKE 'a'

SELECT 1
WHERE 'a' LIKE 'a%'

Прогалини у правого операнда завжди враховуються при порівнянні.

16. 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 символів.

17. ISNULL vs COALESCE
Що ще потенційно цікавого можна показати? Є дві функції: ISNULL COALESCE. З одного боку все просто — якщо перший оператор NULL, то повернути другий оператор або наступний, якщо ми говоримо про COALESCE. З іншого боку, є підступне відмінність між ними.

Що повернуть ці функції?

DECLARE @a CHAR(1) = NULL
SELECT ISNULL(@a, 'NULL'), COALESCE(@a, 'NULL')

DECLARE @INT i = NULL
SELECT ISNULL(@i, 7.1), COALESCE(@i, 7.1)

Відповідь і справді не дуже очевидний:

---- ----
N NULL

---- ----
7 7.1

Чому? Функція ISNULL перетворює найменшого типу з двох операндів. COALESCE перетворить на найбільшому типу. Ось ми і отримуємо таку радість, над якою я в перший раз дуже довго просидів у спробах зрозуміти, «що не так».

З точки зору продуктивності, ISNULL буде трохи швидше відпрацьовувати в ряді випадку, COALESCE ж розкладається в CASE WHEN оператор про який поговоримо нижче.

18. Math
Ще цікавіше, коли стикаєшся з математикою на SQL Server. Начебто різниці не повинно бути:

SELECT 1 / 3
SELECT 1.0 / 3

Але за фактом виявляється, що різниця є — все залежить від того, які дані беруть участь у запиті. Якщо ціле число, то і результат буде цілочисельним:

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

-----------
0.333333

Ще цікавий приклад, який часто зустрічається на співбесідах в тому або іншому вигляді:

SELECT COUNT(*)
, COUNT(1)
, COUNT(val)
, COUNT(DISTINCT val)
, SUM(val)
, SUM(DISTINCT val)
FROM (
VALUES (1), (2), (2), (NULL, NULL)
) t (val)

SELECT AVG(val)
, SUM(val) / COUNT(val)
, AVG(val * 1.)
, AVG(CAST(val AS FLOAT))
FROM (
VALUES (1), (2), (2), (NULL, NULL)
) t (val)

Що поверне запит? COUNT(*)/COUNT(1) поверне загальне число рядків. COUNT по стовпцю поверне кількість не NULL рядків. Якщо додати DISTINCT, то кількість унікальних значень, які не NULL.

Цікавіше з підрахунком середнього. Операція AVG розкладається оптимізатором на SUM COUNT. І тут ми згадаємо про приклад вище — при підрахунку середнього не будуть враховуватися NULL. Крім того, якщо цілочисельні значення, то який буде результат? Цілочисельний. Про це часто забувають.

19. UNION vs UNION ALL
Тут все просто: якщо ми знаємо, що дані не перетинаються, і нас не хвилюють дублікати, то, з точки зору продуктивності, краще використовувати UNION ALL. Якщо потрібно прибрати дублювання, те сміло використовуємо UNION.

Наприклад, у випадку коли дублікатів точно не буде краще використовувати UNION ALL:

SELECT [object_id]
FROM sys.system_objects
UNION
SELECT [object_id]
FROM sys.objects

SELECT [object_id]
FROM sys.system_objects
UNION ALL
SELECT [object_id]
FROM sys.objects



Ще важливо знати про цікавий відмінності між цими двома конструкціями: оператор UNION виконується паралельно, а UNION ALL — послідовно. І це не відноситься до паралельних планів, просто це така особливість доступу до даних, яка може допомогти при оптимізації.

Припустимо, нам потрібно повернути 1 рядок, виходячи з різного набору умов:

DECLARE @AddressLine NVARCHAR(60)
SET @AddressLine = '4775 Kentucky Dr.'

SELECT TOP(1) AddressID
FROM Person.[Address]
WHERE AddressLine1 = @AddressLine
OR AddressLine2 = @AddressLine

Тоді за рахунок використання OR в умови у нас буде IndexScan:



Table 'Address'. Scan count 1, logical reads 90, ...

Перепишемо запит з використанням UNION ALL:

SELECT TOP(1) AddressID
FROM (
SELECT TOP(1) AddressID
FROM Person.[Address]
WHERE AddressLine1 = @AddressLine

UNION ALL

SELECT TOP(1)AddressID
FROM Person.[Address]
WHERE AddressLine2 = @AddressLine
) t

Після виконання першого подзапроса, SQL Server дивиться, що повернулася 1 рядок, якої досить, щоб повернути результат, і далі не продовжує шукати по другій умові:



Table 'Worktable'. Scan count 0, logical reads 0, ...
Table 'Address'. Scan count 1, logical reads 3, ...

20. Re-read
Дуже часто доводилося бачити ситуацію, коли дані можна витягти за допомогою одного JOIN при цьому в запиті пишалася купа підзапитів:

USE AdventureWorks2014
GO

SET STATISTICS ON IO

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

SELECT e.BusinessEntityID
, p.LastName
, p.FirstName
FROM HumanResources.Employee e
JOIN Person.Person p ON e.BusinessEntityID = p.BusinessEntityID

Адже чим менше йде зайвих звернень до таблиці — тим менше логічних читань:

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

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

21. SubQuery
Попередній приклад досить показовий, тому що буде працювати тільки якщо зв'язок між таблицями один-до-одного.

Давайте припустимо що раніше між таблицями Person.Person Sales.SalesPersonQuotaHistory була така зв'язок, виходило що для одного співробітника існувала максимум одна запис за розміром квоти.

USE AdventureWorks2014
GO

SET STATISTICS ON IO

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

На сервері клієнта може бути по-іншому і тоді цей запит призведе до такої помилку:

Msg 512, 16 Level, 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) ORDER BY, і проблема пішла. Однак не все так просто, як може здатися. Використання операції 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

При їх виконанні буде виникати одна і та ж проблема — множинні IndexSeek операції:



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, ...

22. CASE WHEN
Що можна сказати про дану конструкцію мови? Вона часто використовується і має декілька не дуже очевидних особливостей, про які потрібно знати. Незалежно від того, як ми написали оператор CASE WHEN:

USE AdventureWorks2014
GO

SELECT BusinessEntityID
, Gender
, Gender =
CASE Gender
WHEN 'M' THEN 'Male'
WHEN 'F' THEN 'Female'
ELSE 'Unknown'
END
FROM HumanResources.Employee

SQL Server буде розкладати вираз до такого виду:

SELECT BusinessEntityID
, Gender
, Gender =
CASE
WHEN Gender = 'M' THEN 'Male'
WHEN Gender = 'F' THEN 'Female'
ELSE 'Unknown'
END
FROM HumanResources.Employee

В цьому і полягає головна проблема — кожна умова буде послідовно виконуватися до тих пір, поки одна з них не поверне TRUE або не дійдемо до блоку ELSE.

Давайте покажу, проблему більш наочно. Для цього створимо скалярну функцію, яка буде повертати праву частина поштової адреси:

IF OBJECT_ID('dbo.GetMailUrl') IS NOT NULL
DROP FUNCTION dbo.GetMailUrl
GO

CREATE FUNCTION dbo.GetMailUrl
(
@Email NVARCHAR(50)
)
RETURNS NVARCHAR(50)
AS BEGIN

RETURN SUBSTRING(@Email, CHARINDEX('@', @Email) + 1, LEN(@Email))

END

Налаштуємо SQL Profiler на відображення подій SQL:StmtStarting / SP:StmtCompleted (якщо хочеться зробити цього з допомогою XEvents: sp_statement_starting / sp_statement_completed).

Виконаємо запит:

SELECT TOP(10) EmailAddressID
, EmailAddress
CASE dbo.GetMailUrl(EmailAddress)
--WHEN 'microsoft.com' THEN 'Microsoft'
WHEN 'adventure-works.com' THEN 'AdventureWorks'
END
FROM Person.EmailAddress

Функція виконується 10 разів. Тепер приберемо коментар з умови вище:

SELECT TOP(10) EmailAddressID
, EmailAddress
CASE dbo.GetMailUrl(EmailAddress)
WHEN 'microsoft.com' THEN 'Microsoft'
WHEN 'adventure-works.com' THEN 'AdventureWorks'
END
FROM Person.EmailAddress

Функція виконується вже 20 разів. Суть в тому, що вираз CASE не обов'язково бути функцією. Це може бути якийсь складний розрахунок. За рахунок того, що CASE розкладається — це може привести до багаторазового обчисленню одних і тих же операторів.

Боротися з цим можна з допомогою вкладених запитів:

SELECT EmailAddressID
, EmailAddress
CASE MailUrl
WHEN 'microsoft.com' THEN 'Microsoft'
WHEN 'adventure-works.com' THEN 'AdventureWorks'
END
FROM (
SELECT TOP(10) EmailAddressID
, EmailAddress
, MailUrl = dbo.GetMailUrl(EmailAddress)
FROM Person.EmailAddress
) t

Функція виконується 10 разів.

Крім того, потрібно намагатися не навантажувати CASE оператор дублікатами:

SELECT DISTINCT
CASE
WHEN Gender = 'M' THEN 'Male'
WHEN Gender = 'M' THEN '...'
WHEN Gender = 'M' THEN '......'
WHEN Gender = 'F' THEN 'Female'
WHEN Gender = 'F' THEN '...'
ELSE 'Unknown'
END
FROM HumanResources.Employee

Хоч вираження в CASE і обчислюється послідовно (саме в тому порядку, як ми написали). У деяких випадках цей оператор буде виконуватися SQL Server з агрегованих функцій:

DECLARE @INT i = 1
SELECT
CASE WHEN @i = 1
1 THEN
ELSE 1/0
END
GO

DECLARE @INT i = 1
SELECT
CASE WHEN @i = 1
1 THEN
ELSE MIN(1/0)
END

23. Scalar func
Спеціально для любителів ООП — не використовуйте скалярні функції в запитах на T-SQL, які оперують великою кількістю рядків.

Ось приклад з життя, яким я колись страждав, коли ще не знав про потенційних мінуси скалярних функцій:

USE AdventureWorks2014
GO

UPDATE TOP(1) Person.[Address]
SET AddressLine2 = AddressLine1
GO

IF OBJECT_ID('dbo.isEqual') IS NOT NULL
DROP FUNCTION dbo.isEqual
GO

CREATE FUNCTION dbo.isEqual
(
@val1 NVARCHAR(100),
@val2 NVARCHAR(100)
)
RETURNS BIT
AS BEGIN
RETURN
CASE WHEN (@val1 IS NULL AND @val2 IS NULL) OR @val1 = @val2
1 THEN
ELSE 0
END
END

Запити повертають ідентичні дані:

SET STATISTICS ON TIME

SELECT AddressID, AddressLine1, AddressLine2
FROM Person.[Address]
WHERE dbo.IsEqual(AddressLine1, AddressLine2) = 1

SELECT AddressID, AddressLine1, AddressLine2
FROM Person.[Address]
WHERE (AddressLine1 IS NULL AND AddressLine2 IS NULL)
OR AddressLine1 = AddressLine2

SELECT AddressID, AddressLine1, AddressLine2
FROM Person.[Address]
WHERE AddressLine1 = ISNULL(AddressLine2, ")

SET STATISTICS TIME OFF

Але за рахунок того, що кожний виклик скалярної функції ресурсномісткий, отримуємо ось таку різницю:

SQL Server Execution Times:
CPU time = 63 ms, elapsed time = 57 ms.

SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 1 ms.

SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 1 ms.

Крім того, використання скалярних функцій у запиті заважає SQL Server будувати паралельні плани виконання, що при великих обсягах даних може істотно підкосити продуктивність.

В усіх випадках скалярні функції — це зло? Немає. Можна створити функцію з опцією SCHEMABINDING не використовувати вхідних параметрів:

IF OBJECT_ID('dbo.GetPI') IS NOT NULL
DROP FUNCTION dbo.GetPI
GO

CREATE FUNCTION dbo.GetPI ()
RETURNS FLOAT
WITH SCHEMABINDING
AS BEGIN
RETURN PI()
END
GO

SELECT dbo.GetPI()
FROM Sales.Currency

У такому разі функція буде вважатися детермінованою і виконуватися рівно 1 раз.

24. VIEWs
Хтось любить подання… хто-то немає. Нав'язувати думку не використовувати в'ю – собі дорожче, але знати про кілька особливостей при роботі з ними потрібно обов'язково.

Створюємо тестову таблицю і в'ю на основі неї:

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

CREATE TABLE dbo.tbl (INT a, INT b)
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

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

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

Тепер додамо нового стовпця у таблицю і пробуємо знову віднімати дані з в'ю:

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

SELECT * FROM dbo.vw_tbl

Отримаємо той же результат:

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

А все тому, що потрібно або явно задавати стовпці, або рекомпилировать скриптова об'єкт:

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

SELECT * FROM dbo.vw_tbl

щоб отримати правильний результат:

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

При прямому зверненні до таблиці такого приколу не буде.

Є любителі в одному запиті з'єднати всі дані і обернути це все в одному в'ю. За прикладом далеко ходити не будемо, і подивимося на «хороший патерн» 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 ON sp 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
)

Подивимося на план виконання у разі використання в'ю:



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 зробили вельми розумним і на етапі спрощення дерева операторів, при побудові плану виконання, він вміє відкидати непотрібні сполуки.

Однак ефективно він може робити це не завжди. Іноді йому заважає відсутність валідного зовнішнього ключа між таблицями, коли немає можливості перевірити «а чи вплине з'єднання на результат вибірки». Або, наприклад, коли з'єднання йде по більш ніж одному полю… ну не вміє деяких речей оптимізатор, але це ж не привід навантажувати його зайвою роботою.

25. CURSORs
При роботі з SQL Server запам'ятайте одну істину — не використовуйте курсори для ітераційної модифікації даних. Це не Oracle!

Часто можна зустріти ось такий код:

DECLARE @INT BusinessEntityID

DECLARE cur CURSOR FOR
SELECT BusinessEntityID
FROM HumanResources.Employee

OPEN cur

FETCH NEXT FROM cur INTO @BusinessEntityID

WHILE @@FETCH_STATUS = 0 BEGIN

UPDATE HumanResources.Employee
SET VacationHours = 0
WHERE BusinessEntityID = @BusinessEntityID

FETCH NEXT FROM cur INTO @BusinessEntityID

END

CLOSE cur
DEALLOCATE cur

Цей код можна переписати так:

UPDATE HumanResources.Employee
SET VacationHours = 0
WHERE VacationHours <> 0

Приводити час виконання і кількість логічних читань не варто, але повірте, різниця дійсно є. Як варіант, просто розповім про недавній приклад з життя. Зустрів скрипт, в якому було два вкладених курсору. При виконанні даний код приводив до таймауту на клієнті, а всього він виконувався приблизно 38 секунд. Викинув з запиту перший курсор і запит став виконуватися 600мс. Викинув другий курсор — 200мс.

Курсори на SQL Server — зло!

26. 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

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 [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 методу для парсингу, оскільки він дуже вибагливий:



Його можна зробити менш витратним:

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

Але суті це кардинально не змінить. Тепер пробуємо не використовувати метод 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, в іншому випадку звернути увагу на більш вибагливий план value('(./text())[1]'....

27. SQL Injection
Найпростіше показати проблему з sql injection, ніж описати її на словах. Припустимо, у нас є такий код:

DECLARE @param VARCHAR(MAX)
SET @param = 1

DECLARE @SQL NVARCHAR(MAX)
SET @SQL = 'SELECT TOP(5) name FROM sys.objects WHERE schema_id = ' + @param

PRINT @SQL
EXEC (@SQL)

Сформується ось такий запит:

SELECT TOP(5) name FROM sys.objects WHERE schema_id = 1

Якщо перед виконанням дописати до параметру щось зайве:

SET @param = '1; select "hack"'

то запит буде вже такий:

SELECT TOP(5) name FROM sys.objects WHERE schema_id = 1; select 'hack'

Це і є атака, відома як sql injection, коли разом із запитом можна буде виконати щось «зайве». Тут як пощастить — лише б прав вистачило :)

Якщо в коді запит формується за допомогою String.Format (або вручну), то це потенційне місце, де може виникнути проблема з sql injection:

using (SqlConnection conn = new SqlConnection())
{
conn.ConnectionString = @"Server=.;Database=AdventureWorks2014;Trusted_Connection=true";
conn.Open();

SqlCommand command = new SqlCommand(
string.Format("SELECT TOP(5) name FROM sys.objects WHERE schema_id = {0}", value), conn);

using (SqlDataReader reader = command.ExecuteReader())
{
while (reader.Read()) {}
}
}

Інша справа, коли використовується sp_executesql і параметри:

DECLARE @param VARCHAR(MAX)
SET @param = '1; select "hack"'

DECLARE @SQL NVARCHAR(MAX)
SET @SQL = 'SELECT TOP(5) name FROM sys.objects WHERE schema_id = @schema_id'

PRINT @SQL
EXEC sys.sp_executesql @SQL
, N'@schema_id INT'
, @schema_id = @param

В такому випадку, дописати до параметру щось зайве вже не вийде.

У коді це буде виглядати так:

using (SqlConnection conn = new SqlConnection())
{
conn.ConnectionString = @"Server=.;Database=AdventureWorks2014;Trusted_Connection=true";
conn.Open();

SqlCommand command = new SqlCommand(
"SELECT TOP(5) name FROM sys.objects WHERE schema_id = @schema_id", conn);
command.Parameters.Add(new SqlParameter("schema_id", value));

...
}

Тепер точно треба зробити паузу, інакше матеріал ризикує стати нечитабельним…

Короткі підсумки по 38 сторінок тексту розташованого вище
Робота з базою даних — це не завжди сирок моцарелла і смузі після 8-годинного дня. Є багато аспектів, на які потрібно звертати увагу при написанні запитів на T-SQL. Тут я спробував зібрати частину граблів, на які сам в свій час наступав.

Безумовно, це не вичерпний список «підводних каменів» при роботі з SQL Server, але все ж я сподіваюся, що даний матеріал буде комусь корисним. Надалі я постараюся в міру сил поповнювати цей пост новими прикладами.

Якщо будуть питання, конструктивні пропозиції і розумна критика, то всі контакти в профілі.

Відео
Читати всю цю інформацію досить важко, тому для любителів «послухати» вже давно готове відео на каналі russianVC: Шкідливі поради для новачків. Відео відрізняється від поста відсутністю кількох прикладів і тим, що зараз я не перебуваю в штаті фірми про яку згадував на відео.

Що залишилося за бортом?
Спочатку планувати докладно написати про відмінності між тимчасовими таблицями і табличними змінними. В результаті вирішив оформити це в окремий пост, який очікує свого завершення в січні.

Крім того, хотів побіжно розповісти про parameter sniffing, але краще не винаходити велосипед і привести посилання на відмінний пост від Дмитра Пілюгіна: Повільно в додатку, швидко SSMS.

Найближчі заходи?
Тепер пару слів про найближчі заходи по SQL Server. У 26 листопада в Дніпрі буде проходити SQL Saturday 2016 Dnepr — безкоштовний одноденний тренінг для розробників і тих, хто хоче дізнатися щось нове SQL Server. Буде купа спікерів і цікавих доповідей. Попередній розклад можна подивитися тут.
Джерело: Хабрахабр

0 коментарів

Тільки зареєстровані та авторизовані користувачі можуть залишати коментарі.