"Wide" History Table Format

The WideHistory table contains the same data as the History table, but in a different format. The WideHistory table presents data for one or more tag values for a single timestamp, thus providing a "wide" view of the data. To query for values in the WideHistory table, you must specify the timestamp and one or more tagnames as the column names in the query syntax. The results will contain a column for the timestamp and columns for the value of each specified tag at that timestamp. In the following example, Temp1, Temp2, Temp3, and Temp4 are tagnames:

Using the History table to perform the same task is much more difficult.

You can also specify search criteria for the values you want to return (for example, where Temp1 > 75). The WideHistory table can only be related to other tables based on the timestamp.

Note: The AnalogWideHistory, DiscreteWideHistory, and StringWideHistory tables are provided for backward compatibility and can only accept tagnames in the SELECT statement that are of the same type; that is, you can't mix the tag types in the query. The WideHistory table, however, can accommodate a mixture of tag types and should be used instead of the AnalogWideHistory, DiscreteWideHistory, or StringWideHistory tables.

The WideHistory table column type returns a SQL Server type float for analog, a SQL Server type int for discrete tags, and an nvarchar(512) for string tags. The schema of the definition table, WideHistory_OLEDB, indicates a sql_variant type. This is simply a shorthand notation; it does not represent the type actually returned.

There is no Quality column for the WideHistory table because there is more than one tag value for each row returned. However, a value returned for a specified tag will be set to NULL if the quality of the value is invalid, inhibited, or unavailable.

The following restrictions apply when performing a query against the WideHistory table:

• Column names must be specified.

• The table is only accessible using the OPENQUERY statement.

Because tagnames are used for column names, the tagname can include any characters as defined by the rules for Microsoft SQL Server identifiers. An identifier that does not comply with the rules for the format of regular identifiers must always be delimited using brackets ( [ ] ). For more information on identifiers and delimiters, see your Microsoft SQL Server documentation.

If you include an illegal column name in your query and do not use delimiters, no data will be returned.

The StateWideHistory table is similar to the WideHistory table, except that it allows for retrieval of calculated "time in state" values for multiple tags, instead of actual history values. This table includes a vValue column, and the tag columns contain the time in state for the corresponding value. For more information on this table, see StateWideHistory (INSQL.Runtime.dbo.StateWideHistory). For information on how to query this table, see -OLD-ValueState Retrieval in the AVEVA Historian Concepts Guide.