Comments 48
Насчет CURSOR не совсем согласен. Не все операции можно сделать при помощи T-SQL. Иногда жизнь требует более сложных алгоритмов обработки — напрмер сделать процедуру.
Поддерживаю Ваше мнение. Но все же старался донести мысль, что чаще всего курсоры применяют там где они не нужны. Если не брать во внимание административных задач и совсем древних версий (вроде SQL Server 2005), то я раза два-три вынужденно использовал курсоры и они задачу решали намного быстрее.
Да, особенно с таким ужасным примером. Честно, я не видел чтоб так делали и сам так не делал. Хотя и сам был новичком, который sql ни разу не видел и сразу макнули в него, и вокруг всегда было много новичков. Хотя вот динамический sql там, где обычным можно — видел.
Вот если б там ещё б добавили к курсору fast_forward, static, read_only, forward_only в комбинациях и всё это сравнили с циклом по временным таблицам (время исполнения, чтение/запись), то да, было б интересно. Есть ещё задачи, когда одним запросом дорого по времени (сервера или разработчика), а с циклом быстрее/понятнее.
Вот если б там ещё б добавили к курсору fast_forward, static, read_only, forward_only в комбинациях и всё это сравнили с циклом по временным таблицам (время исполнения, чтение/запись), то да, было б интересно. Есть ещё задачи, когда одним запросом дорого по времени (сервера или разработчика), а с циклом быстрее/понятнее.
Отличная статья-завтра проголосую, сегодня заряда нет(
Еще добавлю, что не нужно делать фиктивных обновлений.
Часто вижу приложение, которое делает фиктивное обновление каких-либо строк. В результате чего напрягает стандартную репликацию. И еще хуже-когда при сохранении удаляются строки и добавляются новые (при этом таблица реплецируема). Видел такие разработки на C++ и C#. И еще неоптимально, когда в качестве первичного ключа берут ГУИД, которым никто пользоваться не будет. Нет чтобы взять ту же дату вставки например с каким-нибудь полем-уж явно чаще использоваться будет и в сортировках в том числе.
Еще добавлю, что не нужно делать фиктивных обновлений.
Часто вижу приложение, которое делает фиктивное обновление каких-либо строк. В результате чего напрягает стандартную репликацию. И еще хуже-когда при сохранении удаляются строки и добавляются новые (при этом таблица реплецируема). Видел такие разработки на 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 (за мелкими исключениями, потому что кое где планы выполнения будут другими).
Все что я описал сохраняет актуальность с 2005 версии и по 2016 (за мелкими исключениями, потому что кое где планы выполнения будут другими).
Функция 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 репликацию а то и вставлять гуиды из серверов приложения — все эти инты в дизайне встанут боком.
Я бы больше сказал — только guid. Иначе когда приложение выростет до уровня Enterprise и вам придется настраивать Peer-To-Peer репликацию а то и вставлять гуиды из серверов приложения — все эти инты в дизайне встанут боком.
Ситуации бывают разные. Например, у меня пару проектов уровня Enterprise крутились на экспресс версии SQL Server 2014. Ни секционирования, ни колумсторов… в дополнении ограничен гигом оперативы и медленным диском. Так чтобы все «помещялось» в BufferPool такие заморочки с типами данных как раз были бы не лишними.
Да здравствует распухание кластерного индекса из-за вставок в рандомных местах? :)
Существуют приложения, которые никогда не вырастут до того уровня, где требуется merge-репликация.
Некоторые советы тут бесполезные.
Про даты — надо не искать "универсальный" формат даты, а указать нужные настройки для соединения. А еще лучше — использовать параметризованные запросы и вообще не указывать даты в строковых литералах.
Про NULL в (NOT) IN. Необходимость выполнить такой запрос по атрибуту, не являющемуся первичным ключом — говорит о том, что схему БД забыли перевести в третью нормальную форму. Не надо так делать.
- Кстати, где совет использовать третью нормальную форму?
… а лучше всего использовать ORM и не мучаться с "сырыми" запросами.
Долго ждал, но так и не увидел случаев, когда действительно стоит слезть с ORM на уровень ниже. А их два:
передача на сервер действительно больших массивов данных. Решение — делаем хранимую процедуру, которая принимает табличный параметр. На стороне ADO.NET передаем в качестве параметра в запрос DataTable.
- быстрый подсчет агрегатов через индексированные (они же материализованные) представления. Классический пример — баланс аккаунта можно считать как сумму изменений баланса по журналу операций.
Для "старших" редакций само наличие индексированного представления ускорит запросы, в которую используются подсчитанные агрегаты. Для "младших" (Express/Developer Edition) — надо делать выборку именно из индексированного представления, указав with(noexpand). Удобно создавать второе представление, которое делает select * from ... with (noexpand) — его можно завести в ORM как read-only таблицу.
Если про даты еще ладно… у каждого проекта свои особенности. Но вот с NOT NULL приколом сталкивался регулярно, когда старые базы саппортил. Знать об этом надо!
Вот тут не согласен. Зачем так категорично? :)
Относительно индексированных представлений… OLTP или DW? Их не всегда выгодно применять. Там много приколов, особенно когда присутствует неслабая OLTP нагрузка.
… а лучше всего использовать 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
Invoking CLR User-Defined Aggregate Functions
Прямо в документации — реализация конкатенирующей агрегатной функции для примера...
Уже не актуально… в 2016 они таки это побороли :)
https://msdn.microsoft.com/en-us/library/mt790580.aspx
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 индексы. С небольшими оговорками конечно, но все равно круто :)
Msg 195, Level 15, State 10, Line 4
'STRING_AGG' is not a recognized built-in function name.
Хотя к слову, крайне советую ознакомиться с изменениями в новом SP1 для 2016-го.
В Express, Standart редакциях можно наконец-то использовать секционирование и columnstore индексы. С небольшими оговорками конечно, но все равно круто :)
Касательно 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
Спасибо за статью!
TOP 24.69s
self join 13.33s
ROW_NUMBER 7.05s
Спасибо за статью!
Спасибо за статью! Сначала читал и никак не мог вспомнить, где же это было. Потом вспомнил, что было все в докладе, подумал: «Ай, как нехорошо, все из доклада взял, а автора не упомянул», — и только потом догадался проверить, что автор тот же.
Кстати, ссылка на доклад Сергея: https://www.youtube.com/watch?v=C1I5v1xxJv4. В том же канале есть видео со всех прошлых встреч Russian Virtual Chapter.
Кстати, ссылка на доклад Сергея: https://www.youtube.com/watch?v=C1I5v1xxJv4. В том же канале есть видео со всех прошлых встреч Russian Virtual Chapter.
Упс, посыпаю голову пеплом. Удивительно, но работает и в вашем варианте. Причем, работает одинаково хорошо с 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)
Спасибо. Очень интересно. С некоторыми проблемами я сам долго просидел пока допёр в чём дело :-)
Жаль, что автор удалил эти сообщения. Мне кажется он очень эмоционально реагирует на ту непростую ситуацию в которую мы все сейчас разворачивается вокруг нас. Тем не мение мне кажется не правильно переносить политику в программирование, и подобные статьи должны жить дальше, тем более, что рукописи не горят...
https://web.archive.org/web/20201004042530/https://habr.com/ru/post/315142/
Ой знаете реально хотел все затереть, но Хабр не дал :) пусть все остается как есть.
Эмоционально или нет... скажем так :) у меня свое мнение. Я гамна уже насмотрелся в своей жизни оттого что делает братский народ в моей стране и городе.
Если хочется конструктива велкомс: https://www.youtube.com/watch?v=wXH3fUN0PsM
Сегодня будем с другом стримить и паралельно шутить о русском мире а Харькове, колумсторах и немного поговорим о жизни :)
Sign up to leave a comment.
Утки, Таиланд и T-SQL… или что может подстерегать программистов при работе с SQL Server?