18.03.2023 13:39
Tzimie 9 7600 Источник

Мне часто приходится видеть чужой код на T-SQL. Я уже привык видеть в конце процедур привычное

drop table #a
drop table #b

Таблица сама себя не удалит, видимо думает автор. Ну или это делает "на всякий случай". Вдруг SQL server забудет ее удалить? Впрочем, эти удаления не столь страшны, так как SQL server их оптимизирует (не делает перекомпиляции, о чем мы поговорим дальше, так как удаление происходит строго в конце). Кроме того, если отладка тела процедуры производится скриптом, то удаления нужны, и когда в самом конце этот скрипт оборачивают в процедуру, то удаления остаются.

Но недавно я стал встречать совершенно жуткий антипаттерн. Не знаю, откуда он распространился.

Встречайте:

  if object_id('tempdb..#mytemp') is not null
    DROP TABLE #mytemp
  create table #mytemp (...)

Мне обидно, что SQL server считают идиотом со стекающими от вырождения слюнями, идиотом, неспособным заботиться о контексте выполнения. Но важнее то, что это код - потенциальная бомба с часовым механизмом.

Покажем это на примере. Создадим внешнюю процедуру:

create procedure ALPHA
as
  create table #mytemp (n int, ALPHA varchar(128))
  insert into #mytemp select 1, 'ALPHA'
  select 1 as point, * from #mytemp
  exec BETA
  select 2 as point, * from #mytemp
GO

Как вы видите, этот код вызывает внутреннюю процедуру BETA:

create procedure BETA
as
  create table #mytemp (n int, BETA varchar(128))
  insert into #mytemp select 1, 'BETA'
  select 3 as point, * from #mytemp
GO

Выполним процедуру ALPHA и увидим, что каждая процедура видит объекты в своей области видимости. Временные таблицы также доступны, если они не экранированы созданными локально:

две таблицы сосуществуют вместе, в чем можно убедиться добавив оператор -- ***

create procedure BETA
as
  create table #mytemp (n int, BETA varchar(128))
  insert into #mytemp select 1, 'BETA'
  select 3 as point, * from #mytemp
  select * from tempdb.dbo.sysobjects where name like '%mytemp%' -- ***
GO

Вот они, две наши таблички мирно сосуществуют. Мы можем усложнить задачу SQL так:

Я привел скриншот, чтобы обратить внимание на то, что редактор подозревает, что тут ошибка: таблица #mytemp используется после удаления. Но мы знаем, что делаем:

В 3-й отладочной печати выводится локальная таблица, а в 4-й - внешняя, из ALPHA. После drop SQL server вынужден перекомпилировать хвост процедуры, потому что у другой таблицы могут быть другие поля, как в данном случае.

Теперь вас не должно удивить, что произойдет при использовании антипаттерна:

create procedure BETA
as
  if object_id('tempdb..#mytemp') is not null
    DROP TABLE #mytemp  
  create table #mytemp (n int, BETA varchar(128))
  insert into #mytemp select 1, 'BETA'
  select 3 as point, * from #mytemp
GO
Проверьте себя

Так как проверка и удаление таблицы производится вначале, то удаляется внешняя таблица, созданная процедурой ALPHA. Дальше все идет хорошо до момента, пока ALPHA не пытается прочитать из уже несуществующей таблицы.

Если временные таблицы в процедурах ALPHA и BETA называются по-разному, то все будет хорошо. Все будет хорошо до первого случайного пересечения имен.

Комментарии (9)


  1. Racheengel
    00.00.0000 00:00
    #25340562
    +2

    Простите, но тут проблема не в удалении и создании таблиц, а в неправильном использовании процедур Alpha и Beta. Редактор совершенно верно указал, где ошибка.


    1. Tzimie Автор
      00.00.0000 00:00
      #25340578

      Ее там нет. Вполне допустимо писать процедуру, которая использует временную таблицу, созданную вне ее. У этого есть небольшие последствия в виде перекопиляций, но криминала нет.


  1. Klajnor
    00.00.0000 00:00
    #25340792

    Этот "антипатерн" растёт оттуда же - если надо код из хранимки выполнить отдельно


  1. Klajnor
    00.00.0000 00:00
    #25340832
    +1

    Мне обидно, что SQL server считают идиотом со стекающими от вырождения слюнями, идиотом, неспособным заботиться о контексте выполнения

    Ваш код способен сделать из разработчика, который читает ваш код, - такого идиота. Он очень удивится, что в начале и конце хранимки у таблицы поля разные


  1. hmpd
    00.00.0000 00:00
    #25342772

    Таблицы по завершении сессии сами удалятся, так? Как-то даже в голову не приходило дропать их. А что с ##-таблицами происходит? Когда они чистятся?


    1. Tzimie Автор
      00.00.0000 00:00
      #25342786
      +1

      Да, удаляются сами. А ## таблицы глобальны, надо удалять ручками, и можно использовать для передачи данных между коннекциями

      Да, ещё забыл написать: уровень контекста образует не только процедура, но и exec(string)


  1. Klajnor
    00.00.0000 00:00
    #25347386

    Дополню свои прошлые сообщения тут таким примером.

    И да, я реально считаю MSSQL тупым идиотом. Но я знаю как с этим бороться.

    create or alter procedure up_second
as
begin
  create table #t (
      inc int not null identity(1,1)
    , value_i int not null
    , primary key (inc)
  )

  insert into #t (value_i)
  select 2

  select 'DEBUG 2' [DEBUG 2], * from #t

end
GO

create or alter procedure up_main
as
begin
  create table #t (
      inc int not null identity(1,1)
    , value_s varchar(max) not null
    , primary key (inc)
  )
  insert into #t (value_s)
  select 's1'


  select 'DEBUG 1' [DEBUG 1], * from #t

  exec up_second

  select 'DEBUG 3' [DEBUG 3], * from #t

end
GO

--exec up_second
exec up_main
GO

drop procedure up_main
drop procedure up_second

    Вот пример кода. Мы ожидаем тут увидеть Во втором отладочном селекте value_i = 2

    Но этого не произойдёт, потому что мы на самом деле получим ошибку

    Msg 207, Level 16, State 1, Procedure up_second, Line 10 [Batch Start Line 37]
    Invalid column name 'value_i'.

    Дропнуть таблицу в начале - тоже не приведёт ни к чему хорошему.

    Дальше - интереснее. Если добавить вызов up_second перед up_main - то все работает и не падает и каждая из процедур нормально воспринимает свой контекст. Потому что в этому случае сервер компилирует up_second на использование таблицы с интовым полем.

    И вывод из всего этого нужно сделать такой:

    1) Конструкции когда дропаются в начале хранимки временные таблицы - они допустимы

    Но надо быть уверенным, что это "головная" хранимка. Во внутренних - это действительно может быть проблемой

    if object_id('tempdb..#mytemp') is not null
    DROP TABLE #mytemp

    или

    DROP TABLE if exists #mytemp

    2) Внутренние хранимки не должны сами создавать таблицы, если ожидают данные из какой-то известной внешней временной таблицы

    И при разработке таких хранимок нужно помнить, какую структуру имеет таблица


    1. Tzimie Автор
      00.00.0000 00:00
      #25349704

      Странно что список колонок в insert так влияет. Без него все ok


  1. kirichenec
    00.00.0000 00:00
    #25350666

    Часто такие дропы нужны в скриптах, пока идет активная разработка: добавил поле - словил ошибку выполнения, так как времянка в контексте еще хранит старый состав. А если всунул дроп в начало - то всё ок.

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