TimeSeries data type

The TimeSeries data type is constructed from a row data type and is a collection of row subtypes.

To create a TimeSeries column, first you create the TimeSeries subtype, using the CREATE ROW TYPE statement.


>>-CREATE ROW TYPE--subtype_name--(----------------------------->

>--timestamp_field--DATETIME YEAR TO FRACTION(5)--,------------->

   .-,-----------------------------------.         
   V                                     |         
>----field_name--data_type--+----------+-+--)--;---------------><
                            '-NOT NULL-'

Table 1. TimeSeries data type parameter values
Value	Description
`field_name`	The name of the field in the row data type. Must be unique for the row data type. The number of fields in a subtype is not restricted. Must follow the Identifier syntax. For more information, see Identifier.
`data_type`	Can be any data type except the following data types: SERIAL, SERIAL8, or BIGSERIAL data types Types that have Assign or Destroy functions assigned to them, including large object types and some user-defined types JSON A hertz time series must have columns of only the following data types: SMALLINT, INT, BIGINT, SMALLFLOAT, FLOAT, DATE, INT8, CHAR, VARCHAR, NCHAR, NVCHAR, LVARCHAR, DATETIME, DECIMAL, and MONEY. A compressed time series must have only the following data types: SMALLINT INTEGER BIGINT SMALLFLOAT FLOAT In addition to the above data types, a compressed time series can have the following data types which can be used only with no compression, n() option. The column values will not be compressed. LVARCHAR VARCHAR NVARCHAR CHAR NCHAR Note: The CHAR and NCHAR data types can be treated in two ways: variable length string or fixed length, space padded, string. BOOLEAN INT8 DECIMAL MONEY DATE DATETIME INTERVAL BSON Any fixed length UDT (with the same TS restrictions on UDTs as column in a TS subtype) Any variable length UDT (with the same TS restrictions on UDTs as column in a TS subtype) You can include only one BSON column. A BSON document cannot exceed 4 KB in size.
`subtype_name`	The name of the TimeSeries subtype. Can be a maximum of 128 bytes. Must follow the Identifier syntax. For more information, see Identifier.
`timestamp_field`	The name of the field that contains the time stamp. Must be unique for the row data type. Must follow the Identifier syntax. For more information, see Identifier.

After you create the TimeSeries subtype, you create the table containing the TimeSeries column using the CREATE TABLE statement. You can also use the CREATE DISTINCT TYPE statement to define a new data type of type TimeSeries.

A TimeSeries column can contain either regular or irregular time series; you specify regular or irregular when you create the time series.

The maximum allowable size for a single time series element is 32704 bytes.

You cannot put an index on a column of type TimeSeries.

After loading data into a TimeSeries column, run the following commands:

update statistics high for table tsinstancetable;

update statistics high for table tsinstancetable (id);

This improves performance for any subsequent load, insert, and delete operations.