Comments 42
Насчет CURSOR не совсем согласен. Не все операции можно сделать при помощи T-SQL. Иногда жизнь требует более сложных алгоритмов обработки — напрмер сделать процедуру.
Поддерживаю Ваше мнение. Но все же старался донести мысль, что чаще всего курсоры применяют там где они не нужны. Если не брать во внимание административных задач и совсем древних версий (вроде SQL Server 2005), то я раза два-три вынужденно использовал курсоры и они задачу решали намного быстрее.
2005 не такой уж древний. Вот если SQL Server 6.
Раньше был продукт у QuestSoftware — FrogLight, рисовал такие зеленые Dashboard и мерял жизнь SQL в реальном времени. В том числе и запросы. Очень удобная была программа. Потом когда Dell купил их — и TOAD и остальные полезняшки как то пропали.
Да, особенно с таким ужасным примером. Честно, я не видел чтоб так делали и сам так не делал. Хотя и сам был новичком, который sql ни разу не видел и сразу макнули в него, и вокруг всегда было много новичков. Хотя вот динамический sql там, где обычным можно — видел.
Вот если б там ещё б добавили к курсору fast_forward, static, read_only, forward_only в комбинациях и всё это сравнили с циклом по временным таблицам (время исполнения, чтение/запись), то да, было б интересно. Есть ещё задачи, когда одним запросом дорого по времени (сервера или разработчика), а с циклом быстрее/понятнее.
Отличная статья-завтра проголосую, сегодня заряда нет(
Еще добавлю, что не нужно делать фиктивных обновлений.
Часто вижу приложение, которое делает фиктивное обновление каких-либо строк. В результате чего напрягает стандартную репликацию. И еще хуже-когда при сохранении удаляются строки и добавляются новые (при этом таблица реплецируема). Видел такие разработки на C++ и C#. И еще неоптимально, когда в качестве первичного ключа берут ГУИД, которым никто пользоваться не будет. Нет чтобы взять ту же дату вставки например с каким-нибудь полем-уж явно чаще использоваться будет и в сортировках в том числе.
Я правильно понимаю, что такой вариант конкатенации строк так же может не работать:

DECLARE @a VARCHAR(max) = NULL;

WITH a AS 
(
	SELECT 1 AS a
	UNION ALL SELECT 2 AS a
	UNION ALL SELECT 3 AS a
)
SELECT @a = COALESCE(@a + ',', '') + CAST(a AS VARCHAR)
FROM a

SELECT @a

?
И да… и нет… это все зависит от оптимизатора. Например, наши примеры работать будут, но MS не гарантирует этого поведения всегда (именно поэтому не рекомендуется так делать):

DECLARE @a VARCHAR(MAX)

;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)
SELECT @a = COALESCE(@a + ',', '') + N
FROM E4
ORDER BY LEN(N)

SELECT @a

Увы хорошее репро у меня было только одно, что я привел в статье.

Большая часть статьи из разряда "а как иначе?".


Но обычно так думаешь уже постфактум :)


Так что плюсую.

MS SQL Server 2005 не поддерживает формат Date, поэтому, к сожалению, параграф 6 не применить на практике.
То что на 2005 нету типа DATE еще не говорит о том, что эта версия себя как-то по особенному ведет. Там тоже нужно следить за форматом строковой константы для даты. Или Вы имели ввиду какой-то отдельный случай?

Все что я описал сохраняет актуальность с 2005 версии и по 2016 (за мелкими исключениями, потому что кое где планы выполнения будут другими).
Не совсем корректно, конечно, прокомментировал, параграф 6 можно и нужно применять на практике, с оговоркой про Date. Совет про YYYYMMDD работает и в версии 2005.
Функция ISNULL преобразует к наименьшему типу из двух операндов. COALESCE преобразует к наибольшему типу.

ISNULL преобразует к типу первого операнда.
ISNULL позволяет сбросить признак nullable с колонки (бывает нужно при создании видов или временных таблиц).
COALESCE позволяет указывать много аргументов.

Добавление в копилку полезных приемов:
Создавать временные таблицы желательно без явного указания типов, путем копирования типов нужных полей из таблиц с которыми собираемся в дальнейшем работать. Например:
SELECT e.BusinessEntityID
, p.FirstName
, p.MiddleName
, p.LastName
INTO #TmpTable
FROM HumanResources.Employee e
JOIN Person.Person p ON p.BusinessEntityID = e.BusinessEntityID
WHERE 0=1
Получаем пустую временную таблицу с колонками нужных типов. И, если в будущем размерность какого-либо поля изменится, код переписывать не придется.
Хинт1. конструкт NULLIF(column,column) as column позволяет писать в колонку временной таблицы NULL, даже если в исходной NULL не допустим.
Хинт2. конструкт column+0 AS column позволяет снять признак IDENTITY с колонки

---Зачем BIGINT по таблице с сотрудниками?

Я бы больше сказал — только guid. Иначе когда приложение выростет до уровня Enterprise и вам придется настраивать Peer-To-Peer репликацию а то и вставлять гуиды из серверов приложения — все эти инты в дизайне встанут боком.
Ситуации бывают разные. Например, у меня пару проектов уровня Enterprise крутились на экспресс версии SQL Server 2014. Ни секционирования, ни колумсторов… в дополнении ограничен гигом оперативы и медленным диском. Так чтобы все «помещялось» в BufferPool такие заморочки с типами данных как раз были бы не лишними.

Да здравствует распухание кластерного индекса из-за вставок в рандомных местах? :)


Существуют приложения, которые никогда не вырастут до того уровня, где требуется merge-репликация.

Некоторые советы тут бесполезные.


  • Про даты — надо не искать "универсальный" формат даты, а указать нужные настройки для соединения. А еще лучше — использовать параметризованные запросы и вообще не указывать даты в строковых литералах.


  • Про NULL в (NOT) IN. Необходимость выполнить такой запрос по атрибуту, не являющемуся первичным ключом — говорит о том, что схему БД забыли перевести в третью нормальную форму. Не надо так делать.


  • Кстати, где совет использовать третью нормальную форму?

… а лучше всего использовать ORM и не мучаться с "сырыми" запросами.


Долго ждал, но так и не увидел случаев, когда действительно стоит слезть с ORM на уровень ниже. А их два:


  1. передача на сервер действительно больших массивов данных. Решение — делаем хранимую процедуру, которая принимает табличный параметр. На стороне ADO.NET передаем в качестве параметра в запрос DataTable.


  2. быстрый подсчет агрегатов через индексированные (они же материализованные) представления. Классический пример — баланс аккаунта можно считать как сумму изменений баланса по журналу операций.

Для "старших" редакций само наличие индексированного представления ускорит запросы, в которую используются подсчитанные агрегаты. Для "младших" (Express/Developer Edition) — надо делать выборку именно из индексированного представления, указав with(noexpand). Удобно создавать второе представление, которое делает select * from ... with (noexpand) — его можно завести в ORM как read-only таблицу.

Если про даты еще ладно… у каждого проекта свои особенности. Но вот с NOT NULL приколом сталкивался регулярно, когда старые базы саппортил. Знать об этом надо!

… а лучше всего использовать ORM и не мучаться с «сырыми» запросами.

Вот тут не согласен. Зачем так категорично? :)

Относительно индексированных представлений… OLTP или DW? Их не всегда выгодно применять. Там много приколов, особенно когда присутствует неслабая OLTP нагрузка.

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


В DW: нет никаких препятствий для создания стольких индексированных представлений, сколько хочется.

Так не только из твоего приложения запросы к БД будут, иногда ещё и сам запросы пишешь в SSMS и тут уж от строки в дате не отвертеться.
Добавьте в начале оглавление с ссылками на места в статье. Хотел показать человеку один пункт, потратил пару минут чтобы его найти.
Держите оглавление:

$('.post__title').after('<div class="directory"><h1>Содержание</h1></div>');$('h5').each(function(index) {$(this).attr('id', 'h5_' + index); $('.directory').append('<br/><a href="#h5_' + index + '">' + $('h5')[index].innerText + '</a>');})
Я бы конечно мог посоветовать использовать функцию STRING_CONCAT, если бы она была… На дворе 2016 год, а отдельной функции для конкатенации строк, в SQL Server так и не добавили


А как же функция CONCAT? Появилась в MS SQL Server 2012

Статейка хорошая! автору спасибо.
А что мешает строки ей конкатенировать? Из вашего же примера:
DECLARE @txt VARCHAR(50) = ''
SELECT @txt = CONCAT(@txt, i)
FROM #t

SELECT @txt
Уже не актуально… в 2016 они таки это побороли :)
https://msdn.microsoft.com/en-us/library/mt790580.aspx
По поводу актуальности можно поспорить… vNext не относится к 2016-му. На последней версии SQL Server 2016 SP1 (13.0.4001.0) вот такое мы получим:

Msg 195, Level 15, State 10, Line 4
'STRING_AGG' is not a recognized built-in function name.


Хотя к слову, крайне советую ознакомиться с изменениями в новом SP1 для 2016-го.

В Express, Standart редакциях можно наконец-то использовать секционирование и columnstore индексы. С небольшими оговорками конечно, но все равно круто :)
Имелся в виду год, а не версия :)
проверял, работает на этой:

Microsoft SQL Server vNext (CTP1) — 14.0.1.246 (X64)
Nov 1 2016 23:24:39
Copyright © Microsoft Corporation
on Linux (CentOS Linux 7 (Core))
Касательно 21 пункта, как насчёт варианта с сэлфджойном?
SELECT p.BusinessEntityID, s.SalesQuota
FROM Person.Person p
LEFT JOIN Sales.SalesPersonQuotaHistory s
  LEFT JOIN Sales.SalesPersonQuotaHistory s1 ON s1.BusinessEntityID = s.BusinessEntityID AND s1.QuotaDate > s.QuotaDate 
ON s.BusinessEntityID = p.BusinessEntityID AND s1.[Первичный ключ] IS NULL

Сейчас не могу сам проверить.
Сам и проверил. self join лучше чем вариант с TOP 1, но проигрывает оконной функции, базы AdventureWorks2014 у меня нет, проверил на своих данных, эксперимент не чистый так как там есть и другие соединения но тем не менее. Во всех случаях беру результаты второго подряд запроса.
TOP 24.69s
self join 13.33s
ROW_NUMBER 7.05s

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

Кстати, ссылка на доклад Сергея: https://www.youtube.com/watch?v=C1I5v1xxJv4. В том же канале есть видео со всех прошлых встреч Russian Virtual Chapter.
Спасибо за статью! Хорошая ревизия уже известного, но и нового узнал еще больше.
Небольшое замечание:
SUBSTRING(@Email, CHARINDEX('@', Email) + 1, LEN(@Email))

3й параметр должен быть LEN(@Email)-CHARINDEX('@', Email), моя «любимая» ошибка кстати при работе с substring.
Упс, посыпаю голову пеплом. Удивительно, но работает и в вашем варианте. Причем, работает одинаково хорошо с varchar, char, даже если выходит за границы строки. Почему такое возможно?
Потому что функция «глупая», но «умная». «Глупая» — потому что нет возможности опустить этот параметр, показав, что мы хотим получить все до конца строки. «Умная», потому что если оставшихся символов больше, чем есть в строке, функция не упадет, а вернет все оставшиеся символы. С таким же успехом туда можно поместить максимальное значение int — функция все равно не будет падать, а будет возвращать данные.

Это поведение описано в документации:
If the sum of start and length is greater than the number of characters in expression, the whole value expression beginning at start is returned.
А что если для пятого пункта написать отбор через JOIN (LEFT JOIN)?
Можно так сделать, конечно, но, думаю, в данном случае смысл был именно показать особенности конструкции IN/NOT IN применительно к NULL-овым значениям.
Я собственно почему спросил: что выгодней применить в таком случае?
Так можно посчитать.

Репро:

Заголовок спойлера
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), (NULL)

set statistics io on

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

SELECT * FROM @t1
EXCEPT
SELECT * FROM @t2

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

select t_1.*
from @t1 t_1
		left join @t2 t_2
				on t1 = t2
where t2 is null



Результат (все варианты одинаково хороши за исключением первого):

Table '#54AF0098'. Scan count 2, logical reads 4, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table '#50DE6FB4'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(1 row(s) affected)

(1 row(s) affected)
Table '#54AF0098'. Scan count 0, logical reads 4, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table '#50DE6FB4'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(1 row(s) affected)

(1 row(s) affected)
Table '#54AF0098'. Scan count 0, logical reads 4, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table '#50DE6FB4'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(1 row(s) affected)

(1 row(s) affected)
Table '#54AF0098'. Scan count 0, logical reads 4, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table '#50DE6FB4'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(1 row(s) affected)

Спасибо. Очень интересно. С некоторыми проблемами я сам долго просидел пока допёр в чём дело :-)
Only those users with full accounts are able to leave comments. Log in, please.