Движок для построения отчётов на SQL. Черновик решения

    Введение


    В первой статье ( Движок для построения отчётов на SQL. Идея ) я поделился идеей. Теперь поделюсь решением ( черновиком ). Этот черновик — мой первый опыт «серьезной» работы с T-SQL, поэтому не стоит его принимать за образец «хорошего» кода.
    Самое важное в этом черновике это механизм подстановки формул в динамический запрос. Второе по важности это механизм сохранения результатов вычислений.

    Когда я приступал к работе — я ожидал больших трудностей, но на самом деле все оказалось очень просто. Много писанины и всего пара моментов когда пришлось остановиться и подумать. Первый момент это генерация номера строки в выдаче запроса, Второй — генерация значения для ключевого поля.
    Глаза бояться — руки делают!
    Я начну сразу с самого основного и интересного, для тех кому этого мало — ниже будет доскональный разбор логики. Приступим.

    Вычисление формул



    Разница между Колонками и Разделами

    Есть существенная разница между вычислением формулы для заполнения колонки и вычислением формулы для заполнения полей раздела ( шапки или подвала ). Эта разница заключается в том что колонка вычисляется для каждой строки отдельно, а раздел вычисляется один раз для всех строк разом.
    Формулы для шапки это всегда агрегатные функции и результат вычисления формулы надо «вклеить» в «посадочное место» шаблона.
    Вычисленное значение для колонки надо «забиндить» ( связать ) со строкой для которой эта колонка была вычислена.
    Поэтому для вычисления колонки и шапки разработаны разные шаблоны.

    Шаблон для Раздела

    SET @sql_text = 
    N' SELECT  @result = '
    + @formula 
    + N' FROM table '
    

    Все линейно:
    • вычислили формулу;
    • результат подставили в шаблон;
    • что получились сохранили в соответствующей таблице ( report_region_instances );
    • конец — шапка сформирована;


    Шаблон для Колонки

    С колонками посложней. Если результат вычисления шапки это одно значение, то результат вычисления колонки это множество значений, то есть это таблица состоящая из одной колонки и некоторого количества строк.
    Что бы во время вывода отчёта все строки колонок можно было синхронизировать между собой, необходимо при сохранения результата ( в таблицу report_cell_instances ), каждую строку пронумеровать.
    Для этого необходимо строки некоторым единым способом упорядочить — отсортировать. Добавляем в запрос на вычисление колонки фразу "ORDER BY", в "SELECT" добавляем " ROW_NUMBER() OVER( ORDER BY )".
    Шаблон запроса:
    SET @sql_text = 
    N' SELECT 
        ROW_NUMBER() OVER( ORDER BY key_column) ,'
     + @formula 
     + N' FROM table ORDER BY key_column'
    

    Не сложно. Следующий интересный момент это сохранение вычислений — результатов нашей работы.

    Сохранение результата.


    С сохранением раздела ( шапки или подвала ) нет ни каких трудностей — банальный "INSERT" чего надо куда надо ( в таблицу report_region_instances ).
    С сохранением вычисленной колонки тоже ни чего сложно, надо наш динамический запрос дополнить оператором "INSERT".
    Проблема только в генерации значения поля уникального ключа. Есть отличное решение этой задачи с использованием автоинкрементной колонки ( свойство IDENTITY ), но я люблю иметь максимальный контроль за тем что делает моя программа, поэтому я воспользовался другим инструментом — "SEQUENCE" — и генерирую каждый номер в ручную.
    Шаблон запроса:
    SET @sql_text = 
    N' INSERT INTO report_cell_instances 
    ( id , row_order , value )
    SELECT (NEXT VALUE FOR [dbo].[report_cell_instances_sequence] OVER( ' 
    + @C_ORDER_BY 
    + N' ) ) AS Record_Id , ROW_NUMBER() OVER( ' 
    + @C_ORDER_BY 
    + N' ) AS Row_Order , '
    + @formula
    + N' AS Formula_Result FROM table' + @C_ORDER_BY
    


    Доскональный разбор реализации


    Реализация выполнена в форме T-SQL скрипта, в рабочей реализации это должна быть хранимая процедура, состав входных параметров под вопросом — зависит от потребностей заказчика. У меня в скрипте это:
    1. клиент — выбирается случайным образом из таблицы consumer_reference
    2. номер станции — выбирается случайным образом из таблицы meteo_stations_reference,
    3. период дат — выбираются две случайные даты из таблицы meteo_measurements для выбранной станции

    Другие вещи которые должны быть как минимум константами сделаны в стиле «hard code» aka «magic number», считайте это издержками «черновика».
    Код я писал в dbForge Studio, у этого IDE самый лучший форматировщик исходников ( это единственный плюс этого IDE ), но у меня он не настроен, поэтому форматирование выполнено в ручную, и только там где я про него помнил.
    По привычке к C# и PL/SQL каждое предложение заканчивается знаком ";".
    Остальное читайте в комментариях к коду ( совсем очевидные вещи не имеют комментария, извините я не достаточно зануден ):
    скрипт с подробными комментариями
    BEGIN
    /*
    Способ сортировки, используется в одном варианте для сортировки входных данных, для сортировки при нумерации строк и при генерации значения ключа
    */
      DECLARE @C_ORDER_BY NVARCHAR(MAX) = ' ORDER BY mm.meteo_station_id , mm.read_timestamp ' ;
    /*
    Шаблон для сохранения результирующей колонки
    */
      DECLARE @C_COLUMN_FORMULA_INSERT NVARCHAR(MAX) = 
    N' INSERT INTO report_cell_instances 
      (id 
      ,instance_id 
      ,consumer_id 
      ,column_id 
      ,row_order 
      ,value)
    ';
    /*
    Шаблон для запроса на вычисление формулы, включает в себя все поля необходимые для вставки записи в таблицу report_cell_instances
    */
      DECLARE @C_COLUMN_FORMULA_SELECT NVARCHAR(MAX) = 
      N' SELECT 
          (NEXT VALUE FOR [dbo].[report_cell_instances_sequence] OVER( ' 
    + @C_ORDER_BY 
    + N' ) ) AS RecordId ,       
          @Instance_Id AS InstanceId ,
          @Consumer_Id AS ConsumerId , 
          @Column_Id AS ColumnId ,
          ROW_NUMBER() OVER( ' 
    + @C_ORDER_BY + N' ) AS Row_Order , ';
    /*
    Завершающая часть шаблона запроса на вычисление колонки
    */
      DECLARE @C_COLUMN_FORMULA_FROM NVARCHAR(MAX) = 
      N' FROM 
          meteo_measurements mm 
       WHERE 
          mm.meteo_station_id = @Station_Id 
          AND mm.read_timestamp BETWEEN @FromDate AND @ThruDate   
        ' + @C_ORDER_BY
      ;
    /*
    Параметры динамического запроса на вычисление колонки
    @Station_Id - станция источник данных
    @FromDate - брать данные для вычисления от даты
    @ThruDate - брать данные для вычисления по дату
    @Column_Id - значение ключа для колонки которая вычисляется
    @Instance_Id - значение ключа экземпляра отчёта
    @Consumer_Id - значение ключа клиента
    */
      DECLARE @ColumnFormulaParams NVARCHAR(MAX); 
      SET @ColumnFormulaParams = 
        N' @Station_Id bigint , ' + 
        N' @FromDate datetimeoffset(7) , ' +
        N' @ThruDate datetimeoffset(7) , ' +
        N' @Column_Id INT , ' +
        N' @Instance_Id INT , ' +
        N' @Consumer_Id INT '
      ;
    /*
    Станция на основе данных которой будет сформирован отчёт, берётся случайная из имеющихся
    */
      DECLARE @Station BIGINT ;
      SELECT TOP 1 @Station = sr.id FROM meteo_stations_reference sr ORDER BY NEWID();
    /*
    Вывод отладочной информации в консоль, в скрипте все вызовы "PRINT" служат только этой цели
    */
      PRINT N' @Staton  = ' + CAST ( @Station AS NVARCHAR ) ;
    /*
    параметры отбора данных для формирования отчёта,
    @From - от даты
    @Thru  - по дату
    */
      DECLARE @From DATETIMEOFFSET(7) ;
      DECLARE @Thru DATETIMEOFFSET(7) ;
    /*
    берём случайные даты
    */
      SELECT TOP 1 @From = mm.read_timestamp FROM meteo_measurements mm ORDER BY NEWID();
      SELECT TOP 1 @Thru = mm.read_timestamp FROM meteo_measurements mm ORDER BY NEWID();
    /*
    даты "сортируем" в порядке возрастания
    */
      DECLARE @SwapVariable DATETIMEOFFSET(7) ;
      IF ( @From > @Thru )
        BEGIN
          SET @SwapVariable = @Thru;
          SET @Thru = @From ;
          SET @From = @SwapVariable ;
        END; 
      PRINT N' @From = ' + CAST ( @From  AS NVARCHAR )+ N' @Thru = ' + CAST ( @Thru  AS NVARCHAR );
    /*
    ключ записи экземпляра отчёта, берётся из соответствующей последовательности
    */
      DECLARE @Instance INT ;
      SET @Instance = NEXT VALUE FOR [dbo].[report_instances_sequence] ;
    /*
    Добавили запись экземпляра отчёта, с ключом состояния 1 - "формируется"
    */
      INSERT INTO report_instances 
        ( id , name , description , state_id )
      VALUES 
        (@Instance,CAST(@Instance AS NVARCHAR ),' DEBUG ', 1 )
      ;
    /*
    Клиент, выбирается из существующих случайным образом
    */
      DECLARE @ConsumerId INT ;
      SELECT TOP 1 @ConsumerId = cr.id FROM consumer_reference cr ORDER BY NEWID();
      PRINT N' @ConsumerId  = ' + CAST ( @ConsumerId AS NVARCHAR ) ;
    /*
    Перебор и индивидуальная обработка записей набора данных в T-SQL возможна только с использованием механизма курсоров ( буду рад оказаться не правым ).
    Курсор может накладывать некоторые ограничения на источник данных, которые могут привести к блокировке источника для доступа другими процессами.
    Что бы не разбираться с грамотным использованием курсоров в T-SQL я решил сохранить результат выборки в коллекцию.
    T-SQL не имеет механизма коллекций ( буду рад оказаться не правым ), заменой ему служит механизм временных таблиц, либо табличных переменных.
    Временная таблица может быть записана на диск и проиндексирована, временная таблица существует даже после завершения выполнения блока кода.
    Табличная переменная существует только в оперативной памяти и только внутри блока кода и не может быть проиндексирована.
    Мне было нужно последовательно пробежаться по всем записям - индексация не требуется. 
    Использовать данные в других блоках кода не предполагалось.
    Размер выборки - в пределах десятка записей, даже если их будет 1000, то это не создаст существенной нагрузки на оперативную память.
    Поэтому я выбрал сохранение выборки в табличную переменную, но для истории сохранил вариант с временной таблицей.
    */  
    --  CREATE TABLE #consumers_report_columns(
    --  column_id int )
    --
    --  INSERT INTO #consumers_report_columns ( column_id )
    --  SELECT 
    --    rc.column_id 
    --  FROM 
    --    consumers_report_columns rc 
    --  WHERE
    --    rc.consumer_id = @ConsumerId
    --  ;
    /*
    Табличная переменная для колонок отчёта
    */
      DECLARE @consumers_report_columns TABLE ( column_id INT )
      INSERT INTO @consumers_report_columns (column_id)
      SELECT 
        rc.column_id 
      FROM 
        consumers_report_columns rc 
      WHERE
        rc.consumer_id = @ConsumerId
      ;
    
      /* -=* CYCLE BEGIN *=- */
    
    --  DECLARE consumers_report_columns_cursor CURSOR FOR 
    --  SELECT 
    --    rc.column_id 
    --  FROM 
    --    #consumers_report_columns rc
    --  ;
    /*
    Курсор для перебора записей табличной переменной с колонками
    */
      DECLARE consumers_report_columns_cursor CURSOR FOR 
      SELECT 
        rc.column_id 
      FROM 
        @consumers_report_columns rc
      ;
    /*
    переменная для текущей вычисляемой колонки
    */  
      DECLARE @ColumnId INT ;
    
      OPEN consumers_report_columns_cursor ;
      
      FETCH NEXT FROM consumers_report_columns_cursor 
      INTO 
          @ColumnId
      
      WHILE @@FETCH_STATUS = 0
      BEGIN
      
        PRINT N' @ColumnId  = ' + CAST ( @ColumnId AS NVARCHAR ) ;
    /*
    Код получения текста формулы конечно надо было выполнить единым запросом с получением колонок отчёта, но на тот момент я не был достаточно уверен в себе, поэтому процесс разбит на элементарные действия.
    */
    /*
    Получаем "ссылку" на формулу из записи текущей колонки
    */
        DECLARE @FormulaId INT;
        SELECT 
          @FormulaId = cl.formula_id
        FROM 
          columns cl
        WHERE
          cl.id = @ColumnId  ;
        PRINT N' @FormulaId  = ' + CAST ( @FormulaId AS NVARCHAR ) ;
    /*
    Получаем формулу для вычисления текущей колонки
    */    
        DECLARE @formula NVARCHAR(MAX);
        SELECT 
          @formula = fm.formula 
        FROM 
          formulas fm 
        WHERE
          fm.id = @FormulaId  ;
        PRINT N' @formula  = ' + @formula ;
    /*
    Текст динамического запроса для вычисления текущей колонки, здесь происходит только выборка данных без сохранения в таблицу
    */  
        DECLARE @column_formula_phrase NVARCHAR(MAX);  
        SET @column_formula_phrase = @C_COLUMN_FORMULA_SELECT + @formula + @C_COLUMN_FORMULA_FROM ;
        PRINT N' @column_formula_phrase  = ' + @column_formula_phrase ;
    /*
    лишнее присваивание, но мне в отладке так удобней
    */
        DECLARE @column_formula_sql NVARCHAR(MAX);
        SET @column_formula_sql = @column_formula_phrase ;
    /*
    Вызываем встроенную процедуру для выполнения динамического запроса вычисления колонки, вызов отладочный, в рабочей реализации не нужен, будут отображены результаты вычисления колонки
    */
        EXEC sp_executesql 
            @column_formula_sql
          , @ColumnFormulaParams
          , @Station_Id = @Station
          , @FromDate = @From
          , @ThruDate =  @Thru 
          , @Column_Id = @ColumnId
          , @Instance_Id = @Instance
          , @Consumer_Id = @ConsumerId
    /*
    формирование рабочего запроса для вычисления колонки и её сохранения в таблицу report_cell_instances
    */
        SET @column_formula_phrase = @C_COLUMN_FORMULA_INSERT + @C_COLUMN_FORMULA_SELECT + @formula + @C_COLUMN_FORMULA_FROM ;
        PRINT N' @column_formula_phrase  = ' + @column_formula_phrase ;
    /*
    Выполняем динамический запрос для вычисления и сохранения
    */
        SET @column_formula_sql = @column_formula_phrase ;                 
        EXEC sp_executesql 
            @column_formula_sql
          , @ColumnFormulaParams
          , @Station_Id = @Station
          , @FromDate = @From
          , @ThruDate =  @Thru 
          , @Column_Id = @ColumnId
          , @Instance_Id = @Instance
          , @Consumer_Id = @ConsumerId
      
        FETCH NEXT FROM consumers_report_columns_cursor 
        INTO 
            @ColumnId
      END 
      CLOSE consumers_report_columns_cursor;
    /*
    не уверен что "DEALLOCATE" действительно необходим
    */
      DEALLOCATE consumers_report_columns_cursor;  
    
      /* -=* CYCLE END *=- */
    /*
    освобождаем оперативную память
    */
    --  DROP TABLE #consumers_report_columns
      DELETE @consumers_report_columns ;
    /*
    Вычисление колонок завершено
    */
    /*
    табличная переменная для разделов отчёта
    */
      DECLARE @consumers_report_regions TABLE ( region_id INT )
      INSERT INTO @consumers_report_regions (region_id)
      SELECT 
        rr.region_id 
      FROM 
        consumers_report_regions rr 
      WHERE
        rr.consumer_id = @ConsumerId
      ;
    /*
    Курсор по разделам отчёта записанным в табличную переменную 
    */
      DECLARE consumers_report_regions_cursor CURSOR FOR 
      SELECT 
        rr.region_id 
      FROM 
        @consumers_report_regions rr
      ;
    /*
    Часть шаблона для вычисления формулы раздела
    */
      DECLARE @C_REGION_FORMULA_SELECT NVARCHAR(MAX) = N' SELECT @Result = ' ;
    /*
    Завершающая часть шаблона для вычисления формулы раздела
    */
      DECLARE @C_REGION_FORMULA_FROM NVARCHAR(MAX) = 
    N'
    FROM 
      meteo_measurements mm 
    WHERE 
      mm.meteo_station_id = @Station_Id 
      AND mm.read_timestamp BETWEEN @FromDate AND @ThruDate   
    ';
    /*
    Параметры динамического запроса вычисления формулы раздела
    @Station_Id - вычисление формулы на данных со станции
    @FromDate - вычисление на данных от даты
    @ThruDate - вычисление на данных до даты
    @Result - результат вычисления формулы
    */
      DECLARE @C_REGION_FORMULA_PARAMS NVARCHAR(MAX) = 
        N' @Station_Id bigint , ' + 
        N' @FromDate datetimeoffset(7) , ' +
        N' @ThruDate datetimeoffset(7) , ' +
        N' @Result NVARCHAR(MAX) OUT '
      ;   
    /*Переменная для ключа текущего вычисляемого раздела*/
      DECLARE @RegionId INT ;
    
      OPEN consumers_report_regions_cursor ;
      
      FETCH NEXT FROM consumers_report_regions_cursor 
      INTO 
          @RegionId
      
      WHILE @@FETCH_STATUS = 0
      BEGIN
      
        PRINT N' @RegionId  = ' + CAST ( @RegionId AS NVARCHAR ) ;
    /*
    Получаем шаблон раздела
    */
        DECLARE @Pattern NVARCHAR(MAX) ;
        SELECT
          @Pattern = rg.pattern
        FROM
          regions rg
        WHERE
          rg.id = @RegionId
        ;
        PRINT N' @Pattern  = ' + @Pattern ;
    /*
    Табличная переменная. для формул и их отметок в шаблоне раздела
    */
        DECLARE @region_formulas_and_placeholders TABLE ( formula NVARCHAR(MAX) , placeholder NVARCHAR(MAX) )
    /*
    Записываем формулы раздела в табличную переменную
    */
        INSERT INTO @region_formulas_and_placeholders ( formula , placeholder )
        SELECT
           fr.formula
          , rf.placeholder
        --  , rg.pattern
        FROM
          regions rg
          JOIN region_formulas rf 
          ON rg.id = rf.region_id
          JOIN formulas fr 
          ON rf.formula_id = fr.id
        WHERE
          rg.id = @RegionId
        ;
    /*
    Курсор для перебора записей табличной переменной с формулами раздела 
    */
        DECLARE region_formulas_and_placeholders_cursor CURSOR FOR 
        SELECT 
            fp.formula
          , fp.placeholder 
        FROM 
          @region_formulas_and_placeholders fp
        ;
    /* переменная для текущей формулы раздела */
        DECLARE @region_formula NVARCHAR(MAX);
    /* 
    переменная для текущей отметки в шаблоне раздела. места куда надо вставить результат вычисления формулы
    */
        DECLARE @placeholder NVARCHAR(MAX);
    
        OPEN region_formulas_and_placeholders_cursor ;
        
        FETCH NEXT FROM region_formulas_and_placeholders_cursor 
        INTO 
            @region_formula
          , @placeholder
        
        WHILE @@FETCH_STATUS = 0
        BEGIN
    
          PRINT N' @region_formula  = ' + @region_formula + N' @placeholder = ' + @placeholder;
    /*
    Переменная для запроса на вычисление текущей формулы раздела
    */    
          DECLARE @region_formula_phrase NVARCHAR(MAX) ;  
          SET @region_formula_phrase = @C_REGION_FORMULA_SELECT + @region_formula + @C_REGION_FORMULA_FROM ;
          PRINT N' @region_formula_phrase  = ' + @region_formula_phrase ;
          
          DECLARE @region_formula_sql NVARCHAR(MAX) ; 
          SET @region_formula_sql = @region_formula_phrase ;
    /*
    Переменная для результата вычисления формулы раздела и подстановки в шаблон раздела в соответствии с текущей меткой
    */
          DECLARE @Substitute NVARCHAR(MAX) ; 
    /*
    Выполняем динамический запрос вычисления поля раздела, результат получаем в @Substitute
    */                   
          EXEC sp_executesql 
              @region_formula_sql
            , @C_REGION_FORMULA_PARAMS
            , @Station_Id = @Station
            , @FromDate = @From
            , @ThruDate =  @Thru
            , @Result = @Substitute OUT
          ;
          PRINT N' @Substitute = ' + @Substitute ;
    
    /* Выполняем замену метки на вычисленное значение */
          SET @Pattern = REPLACE ( @Pattern , @placeholder , @Substitute ) ; 
    
          FETCH NEXT FROM region_formulas_and_placeholders_cursor 
          INTO 
              @region_formula
            , @placeholder
        END 
        CLOSE region_formulas_and_placeholders_cursor;
        DEALLOCATE region_formulas_and_placeholders_cursor;
    
    /* удаляем из табличной переменной обработанные данные */
        DELETE @region_formulas_and_placeholders ;
    
        PRINT N' FINISH @Pattern ' + @Pattern ;
    /*
    Сохраняем вычисленный раздел в таблицу report_region_instances
    */
        INSERT INTO report_region_instances
        ( instace_id ,consumer_id ,region_id ,value )
        VALUES( @Instance , @ConsumerId , @RegionId , @Pattern )    
        ;
      
        FETCH NEXT FROM consumers_report_regions_cursor 
        INTO 
            @RegionId
      END
    
      CLOSE consumers_report_regions_cursor;
      DEALLOCATE consumers_report_regions_cursor;
    /* очищаем табличную переменную - освобождаем оперативную память */
      DELETE @consumers_report_regions ; 
    /*
    Все колонки вычислены и сохранены. все разделы вычислены и сохранены - мы молодцы :)
    */
    END;
    



    Тестирование решения


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

    Тестовый набор данных

    Для генерации тестового набора я использовал генератор dbForge Studio.
    В таблице meteo_measurements, тип для колонки read_timestamp пришлось поменять с "timestamp" на "datetimeoffset(7)", потому что значение с типом "timestamp" может создать только сервер, в ручную запрещено, а генерация набора данных — в dbForge Studio выполняется в ручном режиме — скриптом с конкретно прописанными операторами «INSERT».
    Кроме того значение для колонки «meteo_station_id» пришлось подставлять руками, в смысле допиливать сгенерированный скрипт:
    1. заменить «measurements(read_timestamp,» на «measurements(meteo_station_id,read_timestamp,»
    2. заменить "wind_speed) VALUES ('" на "wind_speed) VALUES ((SELECT TOP 1 id FROM meteo_stations_reference ORDER BY NEWID()),'"

    Тестовый набор пришлось ограничить на 15 000 записей, при генерации скрипта более чем на 16 000 сбивался перенос строк.
    Таблицы с настройками

    Кроме того для тестирования были добавлены записи в другие таблицы. Пара уникальных индексов была изменена, и я уже не помню которые это индексы, поэтому я просто повторю все основные таблицы.
    DDL скрипты создания таблиц и DML скрипты со вставкой данных есть на GitHub.
    DDL таблиц и DML со вставкой данных
    CREATE TABLE Linegro.dbo.meteo_stations_reference (
      id BIGINT NOT NULL
     ,name NVARCHAR(4000) NOT NULL
     ,description NVARCHAR(MAX) NULL
     ,CONSTRAINT PK_meteo_stations_reference PRIMARY KEY CLUSTERED (id)
     ,CONSTRAINT UK_meteo_stations_reference_name UNIQUE (name)
    ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
    GO
    SET DATEFORMAT ymd
    SET ARITHABORT, ANSI_PADDING, ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL, QUOTED_IDENTIFIER, ANSI_NULLS, NOCOUNT ON
    SET NUMERIC_ROUNDABORT, IMPLICIT_TRANSACTIONS, XACT_ABORT OFF
    GO
    INSERT Linegro.dbo.meteo_stations_reference(id, name, description) VALUES (1, N'ТАГАНАЙ', N'Метеостанция "Таганай"')
    INSERT Linegro.dbo.meteo_stations_reference(id, name, description) VALUES (2, N'свердловск', N'Метеостанция на Метео горке')
    INSERT Linegro.dbo.meteo_stations_reference(id, name, description) VALUES (3, N'Центральная', N'Москва Центральная')
    INSERT Linegro.dbo.meteo_stations_reference(id, name, description) VALUES (4, N'Владик', N'Морской порт Владивосток')
    INSERT Linegro.dbo.meteo_stations_reference(id, name, description) VALUES (5, N'Якутия', N'Метеостанция республики Саха - Якутия')
    GO
    CREATE TABLE Linegro.dbo.meteo_measurements (
      meteo_station_id BIGINT NOT NULL
     ,read_timestamp DATETIMEOFFSET NOT NULL
     ,temperature DECIMAL(4, 1) NULL
     ,pressure INT NULL
     ,wind_direction INT NULL
     ,wind_speed INT NULL
     ,CONSTRAINT PK_meteo_measurements PRIMARY KEY CLUSTERED (meteo_station_id, read_timestamp)
     ,CONSTRAINT FK_meteo_measurements_meteo_stations_reference_id FOREIGN KEY (meteo_station_id) REFERENCES dbo.meteo_stations_reference (id)
    ) ON [PRIMARY]
    GO
    CREATE TABLE Linegro.dbo.consumer_reference (
      id INT NOT NULL
     ,name NVARCHAR(4000) NOT NULL
     ,description NVARCHAR(MAX) NULL
     ,CONSTRAINT PK_consumer_reference PRIMARY KEY CLUSTERED (id)
     ,CONSTRAINT UK_consumer_reference_name UNIQUE (name)
    ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
    GO
    SET DATEFORMAT ymd
    SET ARITHABORT, ANSI_PADDING, ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL, QUOTED_IDENTIFIER, ANSI_NULLS, NOCOUNT ON
    SET NUMERIC_ROUNDABORT, IMPLICIT_TRANSACTIONS, XACT_ABORT OFF
    GO
    
    INSERT Linegro.dbo.consumer_reference(id, name, description) VALUES (1, N'ИП Наибенько', N'наиби прохожего на тебя похожего')
    INSERT Linegro.dbo.consumer_reference(id, name, description) VALUES (2, N'МО РФ', N'Министерство обороны Российской Федерации')
    INSERT Linegro.dbo.consumer_reference(id, name, description) VALUES (3, N'Рота 23', N'Артиллерийский расчёт рота 23')
    INSERT Linegro.dbo.consumer_reference(id, name, description) VALUES (4, N'426 Горный 2016', N'группа номер 426 ( 2016 ) Горного университета города Екатеринубурга ')
    GO
    CREATE TABLE Linegro.dbo.formulas (
      id INT NOT NULL
     ,code NCHAR(50) NOT NULL
     ,formula NVARCHAR(MAX) NOT NULL
     ,CONSTRAINT PK_formulas PRIMARY KEY CLUSTERED (id)
     ,CONSTRAINT UK_formulas_code UNIQUE (code)
    ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
    GO
    SET DATEFORMAT ymd
    SET ARITHABORT, ANSI_PADDING, ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL, QUOTED_IDENTIFIER, ANSI_NULLS, NOCOUNT ON
    SET NUMERIC_ROUNDABORT, IMPLICIT_TRANSACTIONS, XACT_ABORT OFF
    GO
    
    INSERT Linegro.dbo.formulas(id, code, formula) VALUES (1, N'temperature', N'COALESCE(temperature ,0) AS temperature')
    INSERT Linegro.dbo.formulas(id, code, formula) VALUES (2, N'pressure', N'COALESCE(pressure,0) AS pressure')
    INSERT Linegro.dbo.formulas(id, code, formula) VALUES (3, N'wind_direction', N'COALESCE(wind_direction,0) AS wind_direction')
    INSERT Linegro.dbo.formulas(id, code, formula) VALUES (4, N'wind_speed', N'wind_speed AS wind_speed')
    INSERT Linegro.dbo.formulas(id, code, formula) VALUES (5, N'temperature_max', N'MAX(COALESCE(temperature,0)) ')
    INSERT Linegro.dbo.formulas(id, code, formula) VALUES (6, N'temperature_min', N'MIN(COALESCE(temperature,0)) ')
    INSERT Linegro.dbo.formulas(id, code, formula) VALUES (7, N'temperature_avg', N'AVG(COALESCE(temperature,0)) ')
    INSERT Linegro.dbo.formulas(id, code, formula) VALUES (8, N'speed_m_s', N'CAST ( COALESCE(wind_speed  ,0) AS NVARCHAR ) + N'' ( $M_S$ )'' AS speed_m_s')
    GO
    CREATE TABLE Linegro.dbo.columns (
      id INT NOT NULL
     ,formula_id INT NOT NULL
     ,name NVARCHAR(MAX) NOT NULL
     ,description NVARCHAR(MAX) NULL
     ,CONSTRAINT PK_columns PRIMARY KEY CLUSTERED (id)
     ,CONSTRAINT FK_columns_formulas_id FOREIGN KEY (formula_id) REFERENCES dbo.formulas (id)
    ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
    GO
    SET DATEFORMAT ymd
    SET ARITHABORT, ANSI_PADDING, ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL, QUOTED_IDENTIFIER, ANSI_NULLS, NOCOUNT ON
    SET NUMERIC_ROUNDABORT, IMPLICIT_TRANSACTIONS, XACT_ABORT OFF
    GO
    
    INSERT Linegro.dbo.columns(id, formula_id, name, description) VALUES (1, 1, N'температура', N'температура воздуха ( градусов Цельсия )  ')
    INSERT Linegro.dbo.columns(id, formula_id, name, description) VALUES (2, 2, N'давление', N'атмосферное давление ( мм рт ст )')
    INSERT Linegro.dbo.columns(id, formula_id, name, description) VALUES (3, 3, N'ветер', N'направление ветра')
    INSERT Linegro.dbo.columns(id, formula_id, name, description) VALUES (4, 4, N'скорость', N'скорость ветра ( м/с )')
    INSERT Linegro.dbo.columns(id, formula_id, name, description) VALUES (5, 8, N'скорость', N'скорость ветра')
    GO
    CREATE TABLE Linegro.dbo.regions (
      id INT NOT NULL
     ,pattern NVARCHAR(MAX) NOT NULL
     ,name NVARCHAR(4000) NOT NULL
     ,description NVARCHAR(MAX) NULL
     ,CONSTRAINT PK_regions PRIMARY KEY CLUSTERED (id)
     ,CONSTRAINT UK_regions_name UNIQUE (name)
    ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
    GO
    SET DATEFORMAT ymd
    SET ARITHABORT, ANSI_PADDING, ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL, QUOTED_IDENTIFIER, ANSI_NULLS, NOCOUNT ON
    SET NUMERIC_ROUNDABORT, IMPLICIT_TRANSACTIONS, XACT_ABORT OFF
    GO
    
    INSERT Linegro.dbo.regions(id, pattern, name, description) VALUES (1, N'max temp = $MAX_TEMP$ , min temp = $MIN_TEMP$ , average temp = $AVG_TEMP$', N'temp_statistics', N'статистика по температуре')
    INSERT Linegro.dbo.regions(id, pattern, name, description) VALUES (2, N'группа номер 426 ( 2016 ) Горного университета города Екатеринубурга ', N'426_2016_title', N'шапка')
    INSERT Linegro.dbo.regions(id, pattern, name, description) VALUES (3, N' ', N'empty', N'пустое поле')
    INSERT Linegro.dbo.regions(id, pattern, name, description) VALUES (4, N'Министерство обороны Российской Федерации', N'МО РФ', NULL)
    INSERT Linegro.dbo.regions(id, pattern, name, description) VALUES (5, N'ИП Наибенько', N'ИП Наибенько', NULL)
    GO
    CREATE TABLE Linegro.dbo.consumers_report_columns (
      column_id INT NOT NULL
     ,consumer_id INT NOT NULL
     ,column_order INT NOT NULL
     ,CONSTRAINT PK_consumers_report_columns PRIMARY KEY CLUSTERED (consumer_id, column_id)
     ,CONSTRAINT UK_consumers_report_columns_column_order UNIQUE (consumer_id, column_order)
     ,CONSTRAINT FK_consumers_report_columns_columns_id FOREIGN KEY (column_id) REFERENCES dbo.columns (id)
     ,CONSTRAINT FK_consumers_report_columns_consumer_reference_id FOREIGN KEY (consumer_id) REFERENCES dbo.consumer_reference (id)
    ) ON [PRIMARY]
    GO
    SET DATEFORMAT ymd
    SET ARITHABORT, ANSI_PADDING, ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL, QUOTED_IDENTIFIER, ANSI_NULLS, NOCOUNT ON
    SET NUMERIC_ROUNDABORT, IMPLICIT_TRANSACTIONS, XACT_ABORT OFF
    GO
    
    INSERT Linegro.dbo.consumers_report_columns(column_id, consumer_id, column_order) VALUES (3, 1, 1)
    INSERT Linegro.dbo.consumers_report_columns(column_id, consumer_id, column_order) VALUES (3, 2, 1)
    INSERT Linegro.dbo.consumers_report_columns(column_id, consumer_id, column_order) VALUES (4, 2, 3)
    INSERT Linegro.dbo.consumers_report_columns(column_id, consumer_id, column_order) VALUES (2, 2, 5)
    INSERT Linegro.dbo.consumers_report_columns(column_id, consumer_id, column_order) VALUES (1, 2, 14)
    INSERT Linegro.dbo.consumers_report_columns(column_id, consumer_id, column_order) VALUES (3, 3, 10)
    INSERT Linegro.dbo.consumers_report_columns(column_id, consumer_id, column_order) VALUES (5, 3, 20)
    INSERT Linegro.dbo.consumers_report_columns(column_id, consumer_id, column_order) VALUES (2, 4, 11)
    INSERT Linegro.dbo.consumers_report_columns(column_id, consumer_id, column_order) VALUES (1, 4, 22)
    GO
    CREATE TABLE Linegro.dbo.consumers_report_regions (
      consumer_id INT NOT NULL
     ,region_id INT NOT NULL
     ,region_order INT NOT NULL
     ,type_id INT NULL
     ,CONSTRAINT PK_consumers_report_base PRIMARY KEY CLUSTERED (consumer_id, region_id)
     ,CONSTRAINT UK_consumers_report_regions_region_order UNIQUE (consumer_id, region_order)
     ,CONSTRAINT FK_consumers_report_regions_consumer_reference_id FOREIGN KEY (consumer_id) REFERENCES dbo.consumer_reference (id)
     ,CONSTRAINT FK_consumers_report_regions_regions_id FOREIGN KEY (region_id) REFERENCES dbo.regions (id)
     ,CONSTRAINT FK_consumers_report_regions_report_region_types_id FOREIGN KEY (type_id) REFERENCES dbo.report_region_types (id)
    ) ON [PRIMARY]
    GO
    SET DATEFORMAT ymd
    SET ARITHABORT, ANSI_PADDING, ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL, QUOTED_IDENTIFIER, ANSI_NULLS, NOCOUNT ON
    SET NUMERIC_ROUNDABORT, IMPLICIT_TRANSACTIONS, XACT_ABORT OFF
    GO
    
    INSERT Linegro.dbo.consumers_report_regions(consumer_id, region_id, region_order, type_id) VALUES (1, 5, 1, 2)
    INSERT Linegro.dbo.consumers_report_regions(consumer_id, region_id, region_order, type_id) VALUES (2, 1, -1, 4)
    INSERT Linegro.dbo.consumers_report_regions(consumer_id, region_id, region_order, type_id) VALUES (2, 4, 1, 1)
    INSERT Linegro.dbo.consumers_report_regions(consumer_id, region_id, region_order, type_id) VALUES (3, 1, 50, 5)
    INSERT Linegro.dbo.consumers_report_regions(consumer_id, region_id, region_order, type_id) VALUES (3, 3, -100, 3)
    INSERT Linegro.dbo.consumers_report_regions(consumer_id, region_id, region_order, type_id) VALUES (3, 4, 5, 1)
    INSERT Linegro.dbo.consumers_report_regions(consumer_id, region_id, region_order, type_id) VALUES (4, 2, 10, 3)
    GO
    CREATE TABLE Linegro.dbo.region_formulas (
      id INT NOT NULL
     ,formula_id INT NOT NULL
     ,region_id INT NOT NULL
     ,placeholder NVARCHAR(4000) NOT NULL
     ,CONSTRAINT PK_region_formulas PRIMARY KEY CLUSTERED (id)
     ,CONSTRAINT UK_region_formulas UNIQUE (region_id, formula_id)
     ,CONSTRAINT FK_region_formulas_formulas_formula_id FOREIGN KEY (formula_id) REFERENCES dbo.formulas (id)
     ,CONSTRAINT FK_region_formulas_regions_id FOREIGN KEY (region_id) REFERENCES dbo.regions (id)
    ) ON [PRIMARY]
    GO
    SET DATEFORMAT ymd
    SET ARITHABORT, ANSI_PADDING, ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL, QUOTED_IDENTIFIER, ANSI_NULLS, NOCOUNT ON
    SET NUMERIC_ROUNDABORT, IMPLICIT_TRANSACTIONS, XACT_ABORT OFF
    GO
    
    INSERT Linegro.dbo.region_formulas(id, formula_id, region_id, placeholder) VALUES (1, 5, 1, N'$MAX_TEMP$')
    INSERT Linegro.dbo.region_formulas(id, formula_id, region_id, placeholder) VALUES (2, 6, 1, N'$MIN_TEMP$')
    INSERT Linegro.dbo.region_formulas(id, formula_id, region_id, placeholder) VALUES (3, 7, 1, N'$AVG_TEMP$')
    GO
    CREATE TABLE Linegro.dbo.report_instace_states_reference (
      id INT NOT NULL
     ,code NCHAR(50) NOT NULL
     ,description NVARCHAR(MAX) NULL
     ,CONSTRAINT PK_report_instace_states_reference PRIMARY KEY CLUSTERED (id)
     ,CONSTRAINT UK_report_instace_states_reference_code UNIQUE (code)
    ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
    GO
    SET DATEFORMAT ymd
    SET ARITHABORT, ANSI_PADDING, ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL, QUOTED_IDENTIFIER, ANSI_NULLS, NOCOUNT ON
    SET NUMERIC_ROUNDABORT, IMPLICIT_TRANSACTIONS, XACT_ABORT OFF
    GO
    
    INSERT Linegro.dbo.report_instace_states_reference(id, code, description) VALUES (1, N'формируется', N'формируется')
    INSERT Linegro.dbo.report_instace_states_reference(id, code, description) VALUES (2, N'готов', N'готов')
    INSERT Linegro.dbo.report_instace_states_reference(id, code, description) VALUES (3, N'сохранён', N'сохранён')
    INSERT Linegro.dbo.report_instace_states_reference(id, code, description) VALUES (4, N'удалён', N'удалён')
    GO
    


    Значения вычисляемых формул «сохраняются» как NVARCHAR(MAX), но шаблоны сохранения результатов не предусматривают преобразования типа — это на совести пользователей и их квалификации.

    Упущенные моменты


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

    Заключение


    Собственно теперь всё в ваших руках, из этой болванки можно смастерить всё что угодно и как вам угодно :)
    Если я что то упусти, то могу дополнить в комментариях.
    Спасибо за внимание.

    Ссылки


    1. Движок для построения отчётов на SQL. Идея
    2. Исходники — GitHub
    3. Как заполнить базу данных MS SQL разнородными случайными данными или 17 часов ожидания
    4. dbForge Studio for SQL Server
    5. How to request a random row in SQL?
    6. The Curse and Blessings of Dynamic SQL
    7. Temporary Tables по обсуждению в коментах выяснилась лукавство в статье по ссылке, действительное использование оперативной и постоянной памяти табличными переменными раскрыто по ссылке What's the difference between a temp table and table variable in SQL Server?
    8. SQL Server 2014 In Memory OLTP: Memory-Optimized Table Types and Table Variables
    AdBlock has stolen the banner, but banners are not teeth — they will be back

    More
    Ads

    Comments 14

      +1
      Код я писал в dbForge Studio, у этого IDE самый лучший форматировщик исходников (это единственный плюс этого IDE), но у меня он не настроен, поэтому форматирование выполнено в ручную

      Функционал форматирования у них действительно классный, но то что не он настроен – это поправимо. Недавно разработчикам я уже выслал свои настройки, которые как они мне сказали «включат как дефолтные в следующем релизе». Позавчера они их на сайте выложили… Поэтому для нетерпеливых публикую ссылки: мой стиль форматирования, стиль форматирования «под SQL Prompt».

      Относительно «единственного плюса» не согласен. У них хороший функционал есть для анализа плана выполнения (которым я пользуюсь попеременно с Plan Explorer), дата и схема компараторы. Но самое главное – IntelliSense, который взят от SQL Complete, которым я пользуюсь не один год. И возвращаться обратно на промпт банально нет желания...
        0
        Мне не везёт с "dbForge Studio" у меня он падает в случайные моменты времени. Версия для MS SQL сервера на порядок стабильней версии для ORACLE SQL, но тем не менее тоже падает. конечно не проблема обратно открыть 10 окошек, но вымораживает.

        Два простых примера — простейший рефакторинг — переименовать переменную — переименовывает во всём документе, до и после DECLARE этой переменной, то есть переименовывает там где заведомо делать этого не надо, при этом если вызвать переименование не на DECLARE, а просто в коде то как раз про DECLARE dbForge забудет.
        Второе — а) генерация тестового набора данных( описано в статье ) — не генерил значение внешнего ключа, пришлось руками доделывать, б) при наборе записей больше 16 000 скрипт вставки данных превращался в венегрет, который конечно можно было распутать, но желания нет, в) Если задать генерацию справочника ( 10 записей ) и данных ( 10 000 записей ), то генерил данных ровно столько же сколько записей в справочнике, вместо 10 000 записей в таблице данных, всего 10.

        "dbForge Studio" несомненно отличный продукт, если знать где раскиданы грабли.

        Единственное что в нём отлично работает это форматировщик, у которого очень гибкие настройки, но и его порой заставить работать без бубна не получается.
        на мой вкус dbForge — сырой продукт. и спорить с вами о вкусах я не буду.
          0
          У меня версия 5.1.178. А какая у Вас стоит? Может билд от "30 декабря", то и причина всех проблем :)
          Как вариант, можно и на саппорт им написать… народ за все время адекватно мои реквесты обрабатывал.
            0
            у меня тоже 5.1.1.178. Я не знаю что мне в саппорт написать, закономерность установить не получается, писать "оно у меня падает" мне совесть не позволяет. я сам по техподдержке постоянно такие письма получаю :)
            Не суть. Когда мне надо будет по работе писать много T-SQL кода, тогда и разберёмся.
        +1
        Просмотрел Ваши запросы. Не знаю уместно ли, но много лишнего кода. Например:

        DECLARE @consumers_report_columns TABLE (column_id INT)
        INSERT INTO @consumers_report_columns (column_id)
        SELECT rc.column_id
        FROM consumers_report_columns rc
        WHERE rc.consumer_id = @ConsumerId
        
        DECLARE consumers_report_columns_cursor CURSOR
            FOR
                SELECT rc.column_id
                FROM @consumers_report_columns rc

        можно упростить до:

        DECLARE consumers_report_columns_cursor CURSOR LOCAL READ_ONLY
            FOR
                SELECT column_id
                FROM dbo.consumers_report_columns
                WHERE consumer_id = @ConsumerId

        Также немного смутили комментарии:

        --освобождаем оперативную память
        DELETE @consumers_report_columns

        Что временные таблицы, что табличные переменные — все хранится в tempdb. А то что будет оно в BufferPool или нет… это как повезет. Единственная гарантия (из известных мне), что данные в табличной переменной будут в памяти — использовать InMemory:

        USE test
        GO
        
        ALTER DATABASE test
            ADD FILEGROUP test_mem CONTAINS MEMORY_OPTIMIZED_DATA
        
        ALTER DATABASE test
            ADD FILE (name='test_mem', filename='D:\test_mem') TO FILEGROUP test_mem
        GO
        
        CREATE TYPE dbo.ListInt AS TABLE (
            ID INT NOT NULL,
            INDEX Type_IX_ID HASH (ID) WITH (BUCKET_COUNT = 1000)
        )
        WITH (MEMORY_OPTIMIZED = ON)
        GO
        
        DECLARE @a dbo.ListInt
        INSERT INTO @a VALUES (1)
        SELECT * FROM @a

          –2
          Не знаю уместно ли, но много лишнего кода

          у меня в коде есть комментарий который поясняет этот момент — с правильным использование курсоров в T-SQL не было времени разбираться, это согласитесь не на пол часа занятие.
          Получить набор данных и спокойно с ним работать, не парясь ни за какие нюансы — на мой вкус это хорошая практика.
          Кто то пишет супер оптимизированный код, кто то пишет много элементарного атомарного кода — мне ближе второй подход, с точки сопровождения это более удобный вариант.

          я привожу ссылку Временные таблицы на статью про временные таблицы и табличные переменные, там написано, что табличная переменная живёт только в оперативке, на stack overflow пишут, что дропнуть табличную переменную нельзя, потому что её на жёстком диске нет, это как бы намекает что табличные переменные в tempdb не пишутся.
          Кто из вас прав выясняйте без меня.
            0
            Кто из вас прав выясняйте без меня.

            Не знаю, почему Вы в негативном ключе воспринимаете мои слова. Выяснять кто прав не стоит… Вот написанная "на коленке" элементарная проверка:

            SELECT COUNT(1) FROM tempdb.sys.tables
            GO
            DECLARE @t TABLE (i INT)
            SELECT COUNT(1) FROM tempdb.sys.tables

            -----------
            1
            -----------
            2
              –1
              про tempdb ок, пусть пишется. не в этом суть, суть моих возражений была в том что табличная переменная всегда использует оперативную память. вы с этим не согласились :

              Также немного смутили комментарии

              можно поиграться с кодом :

              -- Test registration in tempdb
              SELECT COUNT(1) AS BEFOR FROM tempdb.sys.tables -- До объявления табличной переменной
              GO
              DECLARE @t1 TABLE (i INT)
              SELECT COUNT(1) AS AFTER FROM tempdb.sys.tables -- После объявления табличной переменной
              GO
              SELECT COUNT(1) AS PAST FROM tempdb.sys.tables -- После блока кода с объявлением табличной переменной
              -- Test Drop
              SELECT COUNT(1) AS BEFOR FROM tempdb.sys.tables 
              GO
              DECLARE @t2 TABLE (i INT)
              SELECT COUNT(1) AS AFTER FROM tempdb.sys.tables 
              DROP TABLE @t2
              GO
              -- Test Trunc
              SELECT COUNT(1) AS BEFOR FROM tempdb.sys.tables
              GO
              DECLARE @t3 TABLE (i INT)
              SELECT COUNT(1) AS AFTER FROM tempdb.sys.tables
              TRUNCATE TABLE @t3
              GO
              -- Test Delete
              SELECT COUNT(1) AS BEFOR FROM tempdb.sys.tables
              GO
              DECLARE @t4 TABLE (i INT)
              SELECT COUNT(1) AS AFTER FROM tempdb.sys.tables
              DELETE @t4
              GO

              но этом нам даст только, то что "DELETE " это единственный способ удалить записи из табличной переменной.

              из моего списка "литературы" идём по ссылке Temporary Tables

              находим текст :

              If you are using SQL Server 2000 or higher, you can take advantage of the new TABLE variable type. These are similar to temporary tables except with more flexibility and they always stay in memory.

              теперь курим MSDN

              Like memory-optimized tables, memory-optimized table variables,
              • Must fit in memory and do not use disk resources.

              Disk-based table variables exist in tempdb. Memory-optimized table variables exist in the user database (but they do not consume storage and are not recovered).
              You cannot create a memory-optimized table variable using in-line syntax. Unlike disk-based table variables, you must create a type first.

              оказывается табличные переменные бывают двух типов:

              1. Disk-based
              2. Memory-optimized

              Memory-optimized должны быть объявлены с использованием пользовательского типа, пример:

              CREATE TYPE [Sales].[SalesOrderDetailType_inmem] AS TABLE(
                [ProductID] [int] NOT NULL,
                [SpecialOfferID] [int] NOT NULL,
              
                INDEX [IX_ProductID] HASH ([ProductID]) WITH ( BUCKET_COUNT = 8),
                INDEX [IX_SpecialOfferID] NONCLUSTERED 
              )
              WITH ( MEMORY_OPTIMIZED = ON )

              1. должен быть минимум один индекс
              2. обязательно "MEMORY_OPTIMIZED = ON"

              ок.

              справедливость восстановлена?
                0
                справедливость восстановлена?

                Нет.… и все потому, что Вы раскидываетесь понятиями не до конца понимая как все работает.

                Временная таблица и табличная переменная используют базу tempdb (обсуждаем общий случай). Вот пруф, что данные туда действительно попадают:

                DECLARE @t TABLE(i INT)
                INSERT INTO @t
                VALUES (1), (2)
                SELECT sys.fn_PhysLocFormatter(%%physloc%%)
                FROM @t

                Далее происходит чтение страниц в BufferPool:

                SELECT *
                FROM sys.dm_os_buffer_descriptors
                WHERE database_id = DB_ID('tempdb')



                И уже потом когда страницы в буфер-пуле SQL Server ними может оперировать… И как быть если размер моей таблицы больше, чем размер свободного места в BufferPool? Как он сможет хранить такой объект в памяти? Делать пример, имитировать недостаток памяти и нагрузку… и что-то доказывать я желания не имею.

                И еще… используя термины "память" Вы упускаете из виду другие другие особенности табличных переменных. Отсутствие статистики. Неверную оценку кардинальности. Необходимость делать рекомпиляцию. Еще можно вспомнить про транзакции и прочие приколы.

                Не верите мне. Почитайте, что говорит Martin Smith в этом случае.
                  +1
                  я ни чего не упускаю, вы пишите :

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

                  MSDN пишет:
                  Table variables does not have distribution statistics, theywill not trigger recompiles. Therefore, in many cases, the optimizer will build a query plan on the assumption that the table variable has no rows. For this reason, you should be cautious about using a table variable if you expect a larger number of rows (greater than 100).

                  что я упустил? у меня что количество колонок, что количество разделов в пределах десятка, мне надо тупо перебрать записи как элементы массива, зачем мне индексы? в чём я не прав?
                  В том что вы игнорируете описанные нюансы и настаиваете на единственно верном решении?

                  в нашем с вами обсуждении табличных переменных мы выяснили, что они бывают двух разных видов:
                  1. Disk-based
                  2. Memory-optimized

                  Теперь почитаем, что
                  Martin Smith

                  в своём коменте пишет:
                  Caveat

                  This answer discusses «classic» table variables introduced in SQL Server 2000. SQL Server 2014 in memory OLTP introduces Memory-Optimized Table Types. Table variable instances of those are different in many respects to the ones discuss

                  мой перевод «оптимизированные для памяти сильно отличаются от тех которые обсуждаются ниже, подробности по ссылке»,
                  перейдём по ссылку, и увидим:
                  The use of memory-optimized table variables has a number of advantages over traditional table variables:

                  • The variables are truly in memory: they are guaranteed to never spill to disk

                  Мой перевод: «Эти переменных истинно в памяти, они гарантировано ни когда не скидываются на диск»

                  поэтому касательно моей комента к коду, за который вы меня мурыжите:
                  ```sql
                  /* очищаем табличную переменную — освобождаем оперативную память */
                  ```
                  можно сказать следующее:
                  • если табличная переменная объявлена с типом имеющим «MEMORY_OPTIMIZED = ON» ( Memory-optimized ), то всё верно
                  • если это old school table variable — Disk-based, то комментарий вводит в заблуждение


                  а теперь вернёмся к:
                  Не знаю, почему Вы в негативном ключе воспринимаете мои слова

                  Который касается вашей адекватности, я о себе пишу:
                  мой первый опыт «серьезной» работы с T-SQL

                  Но вы из контекста статьи вырываете отдельные части и относитесь к моим словам как к изречению гуру по T-SQL.
                  А потом понеслось «в Интернете кто то не прав» :))
                  Надо оно вам?
                    0
                    Уважаемый, я не гуру и никогда этого о себе не говорил. Заявляете, что если в табличную переменную записать 4Гб данных при 2Гб ОЗУ на машине и все будет в памяти — это Ваше право. Относительно адекватности, лично я Вас не провоцировал. И Вы правы мне оно не надо — тратить свое рабочее время, чтобы доказать Вам что-то...
          +1
          Код я писал в dbForge Studio, у этого IDE самый лучший форматировщик исходников ( это единственный плюс этого IDE ), но у меня он не настроен, поэтому форматирование выполнено в ручную, и только там где я про него помнил.

          Единственный плюс!? Хотите подкину еще парочку?

          Русскоязычная редакция dbForge Studio for SQL Server Professional абсолютно бесплатна, а это значит, что Вы можете пользоваться абсолютно всеми фичами при решении своих задач: генерировать тестовые данные (сотни тысяч записей в десятки таблиц) в два клика, сравнивать схемы баз и данные на различных инстансах, профилировать запросы (сравнивать планы выполнения), дебажить процедуры, версионировать базу при помощи популярных систем контроля версий, быстро анализировать состояние индексов и выполнять дефрагментацию при необходимости, экспортировать и импортировать данные в различных форматах, создавать бекапы и ресторить их (этот процесс можно автоматизировать/настроить по расписанию).
          Поскольку статья об отчетах, имеется там и встроенный SQL Server Report Builder, который позволяет очень быстро и удобно настроить генерацию отчетов по данным.
          Ну, и как уже написали выше, встроенный Intellisense — очень крут! Форматирование работает из коробки. Если не устраивает дефолтное форматирование — есть куча предустановленных профилей на выбор, или можно создать свой. В общем, плюсов гораздо больше, чем 1-н :)
            0
            В Report Builder когда то искал возможность добавить параметры в отчет. Не знаете такой функционал там есть вообще?
              0
              по поводу бесплатности это как бы не аргумент, суровая российская реальность с вами не согласиться. Одна половина фишек из джентельменского набора, другой половиной я не пользуюсь. По поводу стабильности читайте мой комент
              Не знаю какой хороший инструментарий для MS SQL, а для Oracle я пользуюсь PL/SQL Developer и TOAD, мне хватает, dbForge — только как форматировщик.
              ЗЫ
              ссылку на dbForge я даю в своём списке ссылок, вы до этого места видимо не дочитали?

            Only users with full accounts can post comments. Log in, please.