History Tables and Views

Because normal Microsoft SQL Server functionality cannot handle the storage and retrieval of huge quantities of rapidly changing data, plant data storage and retrieval are made possible by the AVEVA Historian storage subsystem, the history tables, and the retrieval system.

Some of the history tables are implemented as normal SQL Server tables, and the information contained in them is stored in the Runtime database file (Run110Dat.mdf). Others are implemented as a special type of table called a remote table, or extension table. Extension tables do not actually exist in the database, but rather expose data that is stored in special history files (history blocks) on disk using OLE DB technology.

For more information, see the About data retrieval in the AVEVA Historian Retrieval Guide.

Acquired tag data can be presented in the history tables in four different formats:

• Normal historical format

• "Wide" format

• "Live" format

• Analog/state summary history format

Information about the history blocks is stored in the special HistoryBlock (INSQL.Runtime.dbo.HistoryBlock) extension table.

AVEVA Historian also includes several views to make querying from the history tables easier. Instead of specifying the table name using the required four-part syntax (INSQL.Runtime.dbo.<tablename>), you can simply use the view name instead. The history tables and associated views are listed in the following table. (Backward compatibility tables and views are not included.)

The History and Live tables can accommodate a mixture of tag types and should be used for all queries. The vValue column returns a sql_variant for all tag types. The Value column returns a float value for analog and discrete tags and a NULL for string tags. The Value column is included to allow for aggregation and other operations that are not permitted on a sql_variant column.

You can relate these tables to other tables in the AVEVA Historian database.

For more information on each of these tables, see the corresponding table description in this documentation.

Note: The AnalogHistory, DiscreteHistory, StringHistory, AnalogLive, DiscreteLive, and StringLive tables are provided for backward compatibility and can only accept tagnames in the SELECT statement that are of the same type; that is, you cannot mix the tag types in the query without doing a UNION.

In SQL Server Management Studio, the extension tables are listed under the INSQL or INSQLD linked servers under the Server objects tree item.