Я Вам на пальцах объяснил бизнес-смысл, а Вы вдруг настаиваете на его изменении. Бизнесу надо знать, в какой момент времени эти показания были зафиксированы. Даже не когда они были получены концентратором. И, тем более, не когда они попали в БД.
Простите, вы не читаете
...если нужна бизнес дата - пожалуйста, храните в той же таблице отдельной колонкой.
Про Ledger таблицы - я просто рассказал, что есть такой функционал из "коробки". Если вам показалось, что мне интересен вопрос о сложностях с лицензированием - отнюдь.
Позвольте я закончу ваш дискус - я переводил термины на русский просто для того, чтобы мои ответы не были целиком написаны на руслише. Пользую я ofc только en-us версии всех сред разработки, на этом же языке знаком из терминологией.
Получилось так, что мы друг-друга не поняли (хотя говорили об одном и том же) - бывает. Не думал что это вызовет такой сильный резонанс, sry.
То, что время в темпоралках - всегда время транзакции это как раз таки офигительный бонус: Представим себе ситуацию, вы отдали аудированный отчёт по допустим балансу счёта, который ведёте в таблице вроде вашей:
на время A (до секунду) баланс счёта - 100 И прилетает вам событие, которое по каким-л. причинам вы обрабатываете позже о том, что в момент А-1 со счёта было снято 50 И бежите вы доблестно задним числом править аудированные данные Ну а потом, как водится, приходит аудиторская проверка.
В темпоралках вы храните именно состояние, с которого ваша система учла это событие, если нужна бизнес дата - пожалуйста, храните в той же таблице отдельной колонкой.
Чего вы достигаете: 1. Ваши запросы про баланс счёта на момент A всегда вернут один и тот же результат. 2. Не существует простого способа править данные в исторической части темпоралки - вы автоматически защищены от чьих-нибудь толстых пальцев. 3. Если уж совсем упороться - делаем не обычную темпоралку, а Ledger таблицу, с контрольными суммами, порядками операций аудитом из коробки и прочими прелестями.
Тяжело иногда с теми, кто не хочет слышать. Идём и курим, что такое index density и как она связана с cardinality.
Я не предлагал IsDeleted вносить в индекс, читайте внимательнее. Я говорил, что строить индекс по колонке в которой всего 2 уникальных значения это дурь сама по себе, если уж у вас так сильно перекошенны данные, что одного значения 10%, а другого 90, то по 10% надо строить именно фильтрованный индекс и не страдать историями про частичное покрытие.
Не всегда index-seek быстрее scan'а: сильно ли быстрее будет seek, если плотность созданного вами индекса = 1/3 (3 уникальных занчения в колонке) при равномерном распределении значений по таблице и объеме ну, скажем 1M+ строк?
Не нужно "изобретать". Индексы это вполне конкретный инструмент, которым как и любым другим инструментом нужно уметь пользоваться. Их создание "на всякий случай" без обдумывания - жёсткий антипаттерн, принимать решение о создании индекса в таблице только на основании того, что у вас в запросе есть фильтр по этой колонке это такое себе.
Вы здесь говорите о фильтрованном индексе (a, b) с предикатом фильтра IsDeleted = 0? Индекс, начинающийся с IsDeleted у которого плотность = 0.5 это просто нонсенс, особенно учитывая, что в гистрограмме статистики будет только совершенно ненужный мусор. (leftmost column). При подобном "изобретении" нужно накладывать запрет на профессиию.
1. Вы не указали о каком типе функции идёт речь, будем считать, что это TVIF В общем и целом это называется проблемой невозможности выполнить push-down предиката, (например при использовании в функции оконок), и в таком случае да - безусловно нужно вносить параметр явно и смотреть очень пристально на то, как оно компилируется. НО в общем случае неприятности это может вам принести только в случае если вы используете функцию в качестве ограничивающего предиката с доп. условием в WHERE
2. Только ради этого не стоит заводит MOFG, к тому же: если вы создаетё HASH индекс, то зачем вам ещё и B-TREE? И почему HASH индекс не уникальный, если B-TREE уникальный? В общем и целом могу порекомендовать следующее: если основной шаблон использования ID в типе - singleton lookup и вы знаете предполагаемый объем данных в этом конкретном TVP - создавайте HASH, иначе просто B-Tree и лишь в очень (ОЧЕНЬ) редких случаях нужны оба индекса.
3. Я перефразирую: нельзя использовать VIEW в качестве таблиц при написания запросов: оптимизатор гарантированно не будет счастлив, если вы сделаете соединение VIEW, которая ссылается на другое VIEW, которое содержит в определении APPLY на функцию и т.д. Т.е. суть кроется в следующем: VIEW это не таблица, при написании запросов к ней НУЖНО это учитывать.
4. Не понимаю при чём здесь "рекомендуется", речь идёт о SARGability, и в таком случае простой ссылки будет достаточно. п. 5 - то же самое
6. Нет ничего плохого в OR, просто надо знать меру (как и с IN btw), оценивайте приводит ли использование OR к излишним чтениям или проблемам с компиляцией запроса, если всё в порядке - оставляйте и даже не забивайте себе голову. Мы занимаемся разработкой, а не следованием мантрам в стиле "туда не ходи"
7. Это не совсем (и не всегда) верно, поскольку может ухудшать план исполнения, поскольку после вынесения в TVV этих значений план будет скомпилирован скорее всего как multiple seek of unknown value, это не столь критично при поиске уникальных значений, но может иметь сильнейший отридцательный эффект при поиске чего либо с cardinality > 1 строки.
9. За вас это делает оптимизатор (занимается оценкой), похоже на очередную мантру.
12. Если в ваших запросах DISTINCT SORT получается всегда дороже чем STREAM AGGREGATE, то значит оптимизатор ошибается в cardinality и вам нужно либо посмотреть что со статистикой, либо упростить запрос. (вообще к DISTINCT отношусь как жёлтому флагу, но кажется теперь начну так же относиться и к GROUP BY вместо DISTINCT).
13. Если изменить формулировку на "При проектировании таблицы в OLTP БД с сильной нагрузкой на вставку...", то это одно и допустимых решений, в остальном не очень, и да ссылки как всегда достаточно.
14. В качестве оптимизации или при проектировании seek-heavy таблицы - да, как исходное решение - нет. (у вас часто пользователи ищут строки по 50+ символов?)
17. Нет, оптимизатору в общем случае виднее, если есть проблемы - да, пробовать хинтовать, но НИКОГДА не делать этого "просто потому что принято", то же самое относится и к хинту NOLOCK.
18. Это не верно для NC модулей, поскольку может приводить к падению производительности.
23. Это просто разные функции и у каждой из них есть своя область применения: COALESCE в T-SQL так вообще сахарок, она раскрывается:
SELECT COALESCE(a, b) FROM tbl;
SELECT CASE WHEN a IS NOT NULL THEN a WHEN b IS NOT NULL THEN b END FROM tbl;
соответственно вы не будете вычислять b, если у вас есть a (в случае если a и b подзапросы), НО вы вычислите a дважды (!!! это важно), один раз для выполнения assert, второй для непосредственно получения значения (таков уж T-SQL),
Проверьте то же самое для ISNULL() - все выражения внутри будут вычислены (но только и ВСЕГДА один раз)
25. И зачем вам это в таблице log'ов например?
26. Должно звучать как указывать длину при работе с любыми var* типами: [n]varchar(), varbinary(), дополню от себя: не каждая строка это "по-умолчанию" [n]varchar(255) (подставьте сюда своё любимое число).
29. А если я точно знаю, что выполнение будет только одно и мне не нужно кэшировать план?
30. Мантра, безусловно утюгом гвозди забивать неудобно, но разве плох для этого молоток? Другой разговор про цепочки из UNION (из-за возможных сложностей у оптимизатора при оценке требуемого memory grant'а), но опять же - нельзя что-то делать или не делать только потому что "так принято".
31. Краткая выжимка: Keep transactions as small and as short as possible. Ничего более тут не требуется разъяснять (это относится и к другим похожим пунктам)
32. Если речь об applock'ах, то это ооочень специфический инструмент и не стоит добавлять его как "generic use pattern", их как правило используют только в том случае, если не удаётся обеспечить достаточную изоляцию используя стандартные механизмы блокировки MS SQL Server, или если их использование слишком затратно для системы. (например сильно нагружает pool блокировок, или вываливается за thershold'ы и приводит к эскалации)
35. ... и на 100-й строчке кода искать определение TVV в начале модуля
36. Временные таблицы и TVV это отдельная и очень большая тема, в общем случае стоит всегда задавать себе вопрос: хорошо ли выпонимаете разницу в них, и почему именно в этом конкретном случае вам нужна именно временная таблица, а не TVV (помним про deferred compilation) в версиях 150+
38. Нельзя смешивать DML и DDL код в одном модуле, поскольку это приводит к фантомным перекомпиляциям (не столь актуально для 150+, но всё-же), это в принципе допустимо в скриптах, но никогда в модулях, создаетё времянки - делайте полное её определение, для индексов (почти всех типов) сейчас допускается inline определение прямо в конструкции CREATE TABLE / DECLARE @tvar AS TABLE
39. А ещё лучше - прочтите статью о том, что такое эскалация блокировок
41. Если вам необходимо обеспечить уникальность - поднимите ограничения UNIQUE/PK, а не изобретайте велосипед (кроме того при patter'не UPSERT через MERGE указывать SERIALIAZABLE это строго говоря правило, от которого лишь в некоторых случаях можно отказаться (например OLTP бд))
44-45 - мантры, у TRY_CAST есть всего 2 адекватеных применения: проверка возможности приведения к XML и работа с sql_variant типами (у вас они есть?), select into в скриптах более чем допустим, в модулях нет - неявное создание временнной таблицы.
46. От использования SET ROWCOUNT стоит вообще отказываться, не только в DML. Были кстати кажется где-то тикеты про "а давайте просто объявим его как deprecated", но пока всё ограничилось следующей плашкой:
Important
Using SET ROWCOUNT will not affect DELETE, INSERT, and UPDATE statements in a future release of SQL Server. Avoid using SET ROWCOUNT with DELETE, INSERT, and UPDATE statements in new development work, and plan to modify applications that currently use it. For a similar behavior, use the TOP syntax. ... Setting the SET ROWCOUNT option causes most Transact-SQL statements to stop processing when they have been affected by the specified number of rows. This includes triggers. The ROWCOUNT option does not affect dynamic cursors, but it does limit the rowset of keyset and insensitive cursors. This option should be used with caution.
т.е. на лицо "скрытый контекст исполнения", почему отвалился триггер? - кто-то в scope'е выше поставил SET ROWCOUNT
50. Не запрещено, скорее "не нужно", любителям рассказов про "немедленное освобождение места в tempdb обратно", а вы часто создаёте террабайтные таблицы в tempdb? Если да, то зачем, для этого есть обычные БД? А вообще место для "маленьких" (несколько extent'ов, я не помню точно и не смог найти источник) таблиц высвобождается мгновенно при выходе за scope, для больших - фоновым процессом за 10-20 секунд (слишком от многого зависит) Ручная очистка таких таблиц противоречит следующей парадигме: Keep your transactions as small and as short as possible.
Простите, вы не читаете
Про Ledger таблицы - я просто рассказал, что есть такой функционал из "коробки". Если вам показалось, что мне интересен вопрос о сложностях с лицензированием - отнюдь.
В enterprise версии SQL Server добавление колонки с default'ом - операция над метаданными и без dataflow.
Позвольте я закончу ваш дискус - я переводил термины на русский просто для того, чтобы мои ответы не были целиком написаны на руслише. Пользую я ofc только en-us версии всех сред разработки, на этом же языке знаком из терминологией.
Получилось так, что мы друг-друга не поняли (хотя говорили об одном и том же) - бывает. Не думал что это вызовет такой сильный резонанс, sry.
То, что время в темпоралках - всегда время транзакции это как раз таки офигительный бонус:
Представим себе ситуацию, вы отдали аудированный отчёт по допустим балансу счёта, который ведёте в таблице вроде вашей:
на время A (до секунду) баланс счёта - 100
И прилетает вам событие, которое по каким-л. причинам вы обрабатываете позже о том, что в момент А-1 со счёта было снято 50
И бежите вы доблестно задним числом править аудированные данные
Ну а потом, как водится, приходит аудиторская проверка.
В темпоралках вы храните именно состояние, с которого ваша система учла это событие, если нужна бизнес дата - пожалуйста, храните в той же таблице отдельной колонкой.
Чего вы достигаете:
1. Ваши запросы про баланс счёта на момент A всегда вернут один и тот же результат.
2. Не существует простого способа править данные в исторической части темпоралки - вы автоматически защищены от чьих-нибудь толстых пальцев.
3. Если уж совсем упороться - делаем не обычную темпоралку, а Ledger таблицу, с контрольными суммами, порядками операций аудитом из коробки и прочими прелестями.
Я слова "фильтрованный" не увидел в вашем посте вот совсем
Темпоральные таблицы, нет?
Зачем хранить в одном месте и актуальные данные, и исторические? А как чистить то, что вам более не нужно?
Тяжело иногда с теми, кто не хочет слышать. Идём и курим, что такое index density и как она связана с cardinality.
Я не предлагал IsDeleted вносить в индекс, читайте внимательнее. Я говорил, что строить индекс по колонке в которой всего 2 уникальных значения это дурь сама по себе, если уж у вас так сильно перекошенны данные, что одного значения 10%, а другого 90, то по 10% надо строить именно фильтрованный индекс и не страдать историями про частичное покрытие.
Вот люблю я "magic numbers", откуда число 10000?
Не всегда index-seek быстрее scan'а: сильно ли быстрее будет seek, если плотность созданного вами индекса = 1/3 (3 уникальных занчения в колонке) при равномерном распределении значений по таблице и объеме ну, скажем 1M+ строк?
Не нужно "изобретать". Индексы это вполне конкретный инструмент, которым как и любым другим инструментом нужно уметь пользоваться. Их создание "на всякий случай" без обдумывания - жёсткий антипаттерн, принимать решение о создании индекса в таблице только на основании того, что у вас в запросе есть фильтр по этой колонке это такое себе.
Вы здесь говорите о фильтрованном индексе (a, b) с предикатом фильтра IsDeleted = 0?
Индекс, начинающийся с IsDeleted у которого плотность = 0.5 это просто нонсенс, особенно учитывая, что в гистрограмме статистики будет только совершенно ненужный мусор. (leftmost column). При подобном "изобретении" нужно накладывать запрет на профессиию.
про 29 уточняю: не всегда нужно использовать именно sys.sp_executesql
про динамику вообще на самом деле есть отличная статья - всем рекомендую: The Curse and Blessings of Dynamic SQL (sommarskog.se)
В правильном (ну совсем правильном случае) логи хранят в секционированных таблицах с clustered columnstore индексами (если данным позволяют)
Ну давайте по существу:
1. Вы не указали о каком типе функции идёт речь, будем считать, что это TVIF
В общем и целом это называется проблемой невозможности выполнить push-down предиката, (например при использовании в функции оконок), и в таком случае да - безусловно нужно вносить параметр явно и смотреть очень пристально на то, как оно компилируется.
НО в общем случае неприятности это может вам принести только в случае если вы используете функцию в качестве ограничивающего предиката с доп. условием в
WHERE2. Только ради этого не стоит заводит MOFG, к тому же: если вы создаетё HASH индекс, то зачем вам ещё и B-TREE? И почему HASH индекс не уникальный, если B-TREE уникальный?
В общем и целом могу порекомендовать следующее: если основной шаблон использования ID в типе - singleton lookup и вы знаете предполагаемый объем данных в этом конкретном TVP - создавайте HASH, иначе просто B-Tree и лишь в очень (ОЧЕНЬ) редких случаях нужны оба индекса.
3. Я перефразирую: нельзя использовать VIEW в качестве таблиц при написания запросов: оптимизатор гарантированно не будет счастлив, если вы сделаете соединение VIEW, которая ссылается на другое VIEW, которое содержит в определении APPLY на функцию и т.д. Т.е. суть кроется в следующем: VIEW это не таблица, при написании запросов к ней НУЖНО это учитывать.
4. Не понимаю при чём здесь "рекомендуется", речь идёт о SARGability, и в таком случае простой ссылки будет достаточно. п. 5 - то же самое
6. Нет ничего плохого в OR, просто надо знать меру (как и с IN btw), оценивайте приводит ли использование OR к излишним чтениям или проблемам с компиляцией запроса, если всё в порядке - оставляйте и даже не забивайте себе голову. Мы занимаемся разработкой, а не следованием мантрам в стиле "туда не ходи"
7. Это не совсем (и не всегда) верно, поскольку может ухудшать план исполнения, поскольку после вынесения в TVV этих значений план будет скомпилирован скорее всего как multiple seek of unknown value, это не столь критично при поиске уникальных значений, но может иметь сильнейший отридцательный эффект при поиске чего либо с cardinality > 1 строки.
9. За вас это делает оптимизатор (занимается оценкой), похоже на очередную мантру.
12. Если в ваших запросах DISTINCT SORT получается всегда дороже чем STREAM AGGREGATE, то значит оптимизатор ошибается в cardinality и вам нужно либо посмотреть что со статистикой, либо упростить запрос. (вообще к DISTINCT отношусь как жёлтому флагу, но кажется теперь начну так же относиться и к GROUP BY вместо DISTINCT).
13. Если изменить формулировку на "При проектировании таблицы в OLTP БД с сильной нагрузкой на вставку...", то это одно и допустимых решений, в остальном не очень, и да ссылки как всегда достаточно.
14. В качестве оптимизации или при проектировании seek-heavy таблицы - да, как исходное решение - нет. (у вас часто пользователи ищут строки по 50+ символов?)
17. Нет, оптимизатору в общем случае виднее, если есть проблемы - да, пробовать хинтовать, но НИКОГДА не делать этого "просто потому что принято", то же самое относится и к хинту NOLOCK.
18. Это не верно для NC модулей, поскольку может приводить к падению производительности.
23. Это просто разные функции и у каждой из них есть своя область применения:
COALESCE в T-SQL так вообще сахарок, она раскрывается:
SELECT COALESCE(a, b) FROM tbl;
SELECT
CASE
WHEN a IS NOT NULL THEN a
WHEN b IS NOT NULL THEN b
END
FROM
tbl;
соответственно вы не будете вычислять b, если у вас есть a (в случае если a и b подзапросы), НО вы вычислите a дважды (!!! это важно), один раз для выполнения assert, второй для непосредственно получения значения (таков уж T-SQL),
Проверьте то же самое для ISNULL() - все выражения внутри будут вычислены (но только и ВСЕГДА один раз)
25. И зачем вам это в таблице log'ов например?
26. Должно звучать как указывать длину при работе с любыми var* типами: [n]varchar(), varbinary(), дополню от себя: не каждая строка это "по-умолчанию" [n]varchar(255) (подставьте сюда своё любимое число).
29. А если я точно знаю, что выполнение будет только одно и мне не нужно кэшировать план?
30. Мантра, безусловно утюгом гвозди забивать неудобно, но разве плох для этого молоток? Другой разговор про цепочки из UNION (из-за возможных сложностей у оптимизатора при оценке требуемого memory grant'а), но опять же - нельзя что-то делать или не делать только потому что "так принято".
31. Краткая выжимка: Keep transactions as small and as short as possible. Ничего более тут не требуется разъяснять (это относится и к другим похожим пунктам)
32. Если речь об applock'ах, то это ооочень специфический инструмент и не стоит добавлять его как "generic use pattern", их как правило используют только в том случае, если не удаётся обеспечить достаточную изоляцию используя стандартные механизмы блокировки MS SQL Server, или если их использование слишком затратно для системы. (например сильно нагружает pool блокировок, или вываливается за thershold'ы и приводит к эскалации)
35. ... и на 100-й строчке кода искать определение TVV в начале модуля
36. Временные таблицы и TVV это отдельная и очень большая тема, в общем случае стоит всегда задавать себе вопрос: хорошо ли выпонимаете разницу в них, и почему именно в этом конкретном случае вам нужна именно временная таблица, а не TVV (помним про deferred compilation) в версиях 150+
38. Нельзя смешивать DML и DDL код в одном модуле, поскольку это приводит к фантомным перекомпиляциям (не столь актуально для 150+, но всё-же), это в принципе допустимо в скриптах, но никогда в модулях, создаетё времянки - делайте полное её определение, для индексов (почти всех типов) сейчас допускается inline определение прямо в конструкции
CREATE TABLE / DECLARE @tvar AS TABLE
39. А ещё лучше - прочтите статью о том, что такое эскалация блокировок
41. Если вам необходимо обеспечить уникальность - поднимите ограничения UNIQUE/PK, а не изобретайте велосипед (кроме того при patter'не UPSERT через MERGE указывать SERIALIAZABLE это строго говоря правило, от которого лишь в некоторых случаях можно отказаться (например OLTP бд))
44-45 - мантры, у TRY_CAST есть всего 2 адекватеных применения: проверка возможности приведения к XML и работа с sql_variant типами (у вас они есть?), select into в скриптах более чем допустим, в модулях нет - неявное создание временнной таблицы.
46. От использования SET ROWCOUNT стоит вообще отказываться, не только в DML. Были кстати кажется где-то тикеты про "а давайте просто объявим его как deprecated", но пока всё ограничилось следующей плашкой:
т.е. на лицо "скрытый контекст исполнения", почему отвалился триггер? - кто-то в scope'е выше поставил SET ROWCOUNT
50. Не запрещено, скорее "не нужно", любителям рассказов про "немедленное освобождение места в tempdb обратно", а вы часто создаёте террабайтные таблицы в tempdb? Если да, то зачем, для этого есть обычные БД? А вообще место для "маленьких" (несколько extent'ов, я не помню точно и не смог найти источник) таблиц высвобождается мгновенно при выходе за scope, для больших - фоновым процессом за 10-20 секунд (слишком от многого зависит)
Ручная очистка таких таблиц противоречит следующей парадигме: Keep your transactions as small and as short as possible.