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

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

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

Важно. Индексированные представления поддерживаются только в 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 перечислены некоторые столбцы аудита, которые обычно добавляются в таблицы.

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

Настраиваем столбцы аудита

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. Цель – отслеживание любых изменений в этой таблице и обеспечение возможности отменить изменения после того, как они были зафи ксированы.