Опубликован: 29.07.2008 | Доступ: свободный | Студентов: 1170 / 115 | Оценка: 4.49 / 4.15 | Длительность: 17:53:00
Лекция 7:

Хранение архивных данных

< Лекция 6 || Лекция 7: 1234 || Лекция 8 >

Вывод итоговых данных в индексированных представлениях

Еще одна возможность сохранить итоговые данные– это использование индексированных представлений. Индексированные представления также называют материализованными представлениями, потому что они вычисляют и хранят данные. От обычных представлений их отличает наличие уникальных кластеризованных индексов, реализованных в них для повышения производительности выполняемых запросов. Как правило, наилучший выигрыш в производительности достигается при создании индексированных представлений данных, которые являются итоговыми и не требуют для получения результата множественных соединений. Если данные, к которым нужно выполнить запрос, имеют несколько агрегатов или соединений или часто обновляются, индексированные представления дадут либо небольшой выигрыш, либо вообще никакого выигрыша не будет.

Важно. Индексированные представления поддерживаются только в SQL Server Enterprise Edition (версии 2000 и 2005). Как и для всех функций, поддерживаемых только в версии Enterprise, можно использовать в разработке функциональные возможности Enterprise Edition, работая в SQL Server Developer Edition.

Далее мы создадим индексированное представление, которое будет возвращать продажи отдельных изделий в 2004 году, сгруппированные по продавцам. Индексированные представления имеют много ограничений, которые препятствуют их созданию на основе изменяющихся значений. В этом случае мы используем диапазон данных, в котором данные, вероятнее всего, не будут изменяться. Если вам нужно показать эти данные по месяцам, то придется создать отдельное индексированное представление для каждого месяца.

Совет. Если вы используете такой тип представлений главным образом для вывода итоговых данных, можно создать набор представлений, которые будут выводить итоговые данные сразу за целый год, используя месяцы только для текущего года. Например, можно иметь индексированные представления для 2004, 2005 годов, января 2006, февраля 2006 и так далее. В конце каждого года можно удалить ежемесячные представления за этот год и создать годовое представление.

Создаем индексированное представление для итогов продаж

  1. Создайте представление, выполнив следующий код (его можно найти в файлах примеров под именем Create View.sql ) в окне нового запроса в SQL Server Management Studio. Фрагменты кода, выделенные полужирным шрифтом, подробно объясняются на врезке "Параметры, обязательные при работе с индексированными представлениями". Они обязательны для применения индекса.
    USE AdventureWorks; 
    GO
    
    IF EXISTS(SELECT 1 FROM sys.objects WHERE name = 
           N'v_SalesPerson2004ProductSummary') 
      DROP VIEW Sales.v_SalesPerson2004ProductSummary 
    GO
    
    SET ANSI_NULLS ON
    SET QUOTED_IDENTIFIER ON
    GO
    
    CREATE VIEW Sales.v_SalesPerson2004ProductSummary 
      WITH SCHEMABINDING
    AS  
      SELECT hdr.SalesPersonID
            ,cntc.FirstName AS SalesPersonFirstName 
            ,cntc.LastName AS SalesPersonLastName 
            ,prod.ProductID 
            ,prod.Name AS ProductName
            ,COUNT_BIG(*) AS OrderLineCount
            ,SUM(dtl.OrderQty) as OrderQty
            ,SUM(dtl.LineTotal) as LineTotal
        FROM Sales.SalesOrderHeader hdr
        INNER JOIN Sales.SalesOrderDetail dtl
          ON hdr.SalesOrderID = dtl.SalesOrderID 
        INNER JOIN HumanResources.Employee emp
          ON hdr.SalesPersonID = emp.EmployeeID 
        INNER JOIN Person.Contact cntc
          ON emp.ContactID = cntc.ContactID 
        INNER JOIN Production.Product prod
          ON dtl.ProductID = prod.ProductID
        WHERE hdr.OrderDate BETWEEN 
              CONVERT(DATETIME,  "1/1/2004 00:00:00",120)
          AND CONVERT(DATETIME,"12/31/2004 23:59:59",120)
        GROUP BY hdr.SalesPersonID
                ,cntc.FirstName
                ,cntc.LastName
                ,prod.ProductID
                ,prod.Name; 
    GO
  2. Затем, чтобы представление стало индексированным, следует добавить к нему уникальный кластеризованный индекс. В этом случае мы не можем использовать тот же кластеризованный индекс, который уже использовался в таблице, потому что он не будет уникальным. Чтобы создать уникальный индекс, придется добавить в индекс столбец ProductID. Выполните следующий код (его можно найти среди файлов примеров под именем AddIndex.sql ).
    USE AdventureWorks
    GO
    CREATE UNIQUE CLUSTERED INDEX cidx_v_SalesPerson2004ProductSummary
      ON Sales.v_SalesPerson2004ProductSummary 
          (SalesPersonID 
          ,ProductID); 
    GO

Параметры, обязательные при работе с индексированными представлениями

Хотя использование индексированных представлений способно повысить производительность при извлечении итоговых данных из среды, оно связано с некоторыми обязательными параметрами и ограничениями. Представление, созданное в разделе "Создаем индексированное представление для итогов продаж" было разработано для обхода некоторых из таких ограничений.

  • При создании представления параметры ANSI_NULLS и QUOTED_IDENTIFIER должны быть установлены на ON. Параметр ANSI_NULLS должен быть включен также и для базовой таблицы, лежащей в основе представления.
  • Необходимо создать представление, использующее параметр SCHEMA_BINDING. Оно объединит схему со схемами базовых таблиц.
  • При использовании агрегатов и предложений GROUP BY необходимо включить в список SELECT COUNT_BIG(*).
  • В синтаксисе представления допускается использовать только детерминированные функции. Нельзя использовать функцию GET-DATE(), поскольку она не является детерминированной. Необходимо также конвертировать даты в строковых форматах в даты в детерминированных форматах. В данном примере мы конвертируем строковое выражение в тип данных DATETIME при помощи канонического стандартного стиля ODBC (120).

Следует иметь в виду еще несколько параметров и ограничений. Полный список можно найти в Электронной документации по SQL Server 2005 в теме "Создание индексированных представлений". Обязательно ознакомьтесь с этим списком, прежде чем приступать к использованию индексированных представлений, поскольку окончательный проект может оказаться слишком усложненным и не даст желательных преимуществ.

Отслеживание изменений при помощи столбцов и таблиц аудита

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

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

Аудит при помощи столбцов

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

Таблица 7.1. Различные типы столбцов аудита
Отслеживаемые события Типы данных Комментарии
INSERT, UPDATE, или DELETE DATETIME Используется для отслеживания даты и времени выполнения отслеживаемого действия.
Обычно используется с функцией GETDATE() как значение по умолчанию, но значение может задаваться и вызывающим приложением.
INSERT, UPDATE или DELETE VARCHAR Используется для отслеживания имени пользователя или приложения, выполняющего отслеживаемое действие.
DELETE BIT/TINYINT Используется для того, чтобы пометить данные как удаляемые. Это может с большой эффективностью применяться в индексировании и фильтрации.

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

Настраиваем столбцы аудита
  1. Сначала нужно определить события, которые нужно отслеживать. В этом примере вы научитесь добавлять столбцы аудита для отслеживания инициатора изменений, даты и времени создания записи, даты и времени последнего обновления записи и того, была ли удалена запись из таблицы Person.Address базы данных Adventure Works.
  2. Выбрав таблицу ( Person.Address ) и определив события, которые будут отслеживаться, нужно решить, какие столбцы добавить в таблицу.
    • Столбец ModifiedDate уже существует в таблице. Он будет отслеживать дату, показывающую, когда запись была в последний раз изменена или удалена.
    • Столбец CreatedDate будет отслеживать, когда была создана запись. Тип данных этого столбца DATETIME, с использованием функции GETDATE() для предоставления текущей даты как значения по умолчанию.
    • Столбец ModifiedBy —это столбец VARCHAR, который будет содержать имя пользователя или некоторые другие средства для идентификации пользователя или приложения, которые внесли изменения.
    • Столбец IsDeleted - столбец с типом данных BIT, который будет использоваться для записи об удалении строки. Дата и пользователь будут отслеживаться через столбцы ModifiedDate и ModifiedBy. Если запись была удалена, этот столбец будет помечен, а в измененном столбце будут сведения о том, кто и когда удалил запись.
  3. Теперь можно выполнить представленный ниже сценарий, чтобы изменить таблицу Person.Address (этот код можно найти в файлах примеров под именем AlterTable.sql ).
    USE AdventureWorks
    GO
    ALTER TABLE Person.Address
      ADD CreatedDate DATETIME NULL DEFAULT GETDATE()
         ,ModifiedBy VARCHAR(50) NULL
         ,IsDeleted BIT DEFAULT (0)
  4. Далее, если вы изменяете таблицу с уже имеющимися данными, следует задать в столбце CreatedDate значение, показывающее, что столбец был создан до того, как был начат аудит. Чтобы задать значение CreatedDate, выполните следующий код:
    UPDATE Person.Address 
    SET CreatedDate = "1/1/1980";
  5. Теперь нужно изменить хранимые процедуры и код приложения для заполнения этих столбцов нужными результатами. Для обновления столбцов можно использовать триггеры, но обычно лучше контролировать изменение данных и использовать для обновления столбцов аудита код приложения.
  6. Последнее действие в этом процессе – это добавление фильтра ко всем процедурам и программам, ссылающимся на данную таблицу, чтобы предотвратить возвращение удаленных записей. Вот фильтр, который нужно использовать:
    WHERE IsDeleted = 0

Аудит с помощью таблиц

Теперь мы знаем, как использовать аудит для уведомления о сделанных изменениях. Однако единственное изменение, которое может быть легко отменено - это событие DELETE. Достаточно просто сбросить флаг IsDeleted, и данные будут снова доступны. Существует также возможность отменить событие CREATE, если об этом действии имеется достаточная информация. Однако если нужно иметь возможность полностью отслеживать состояние данных перед изменением, возможно, лучшим вариантом окажется использование таблиц аудита. Эту возможность следует использовать с осторожностью, потому что она может вызвать много проблем с обслуживанием и производительностью. Такие проблемы возникают потому, что приходится копировать данные в таблицу аудита и изменять их в исходной таблице. Для этого примера мы зададим аудит на базе таблицы в таблице Sales.Special Offer. Цель – отслеживание любых изменений в этой таблице и обеспечение возможности отменить изменения после того, как они были зафи ксированы.

< Лекция 6 || Лекция 7: 1234 || Лекция 8 >
Гаральд Егоркин
Гаральд Егоркин
Россия
Павел Шелякин
Павел Шелякин
Россия