1. Forum
  2. Usenet
  3. COMP.DATABASES.MS-SQLSERV

  • No covering indices for table types?

    From Anton Shepelev@21:1/5 to All on Fri Jul 12 15:15:01 2024
    Hello, all.

    I wanted to create a convenient table-type for storing time
    series (with possibly non-uniqe sample marks):

    CREATE TYPE series_t AS TABLE
    ( moment DATETIME NOT NULL,
    value FLOAT
    )

    For efficient querying and joining, I need a covering index
    on the [moment] field that includes [value], but the INCLUDE
    keyword does not seem to be suported:

    https://learn.microsoft.com/en-us/sql/t-sql/statements/create-type-transact-sql

    so that I can only create a simple non-convering column
    index:

    CREATE TYPE series_t AS TABLE
    ( moment DATETIME NOT NULL INDEX moment,
    value FLOAT
    )

    or a compound table index for both columns:

    CREATE TYPE series_t AS TABLE
    ( moment DATETIME NOT NULL,
    value FLOAT
    INDEX Main(moment, value)
    )

    What is best practice in cases when a covering index is
    required for a tale-type?

    --
    () ascii ribbon campaign -- against html e-mail
    /\ www.asciiribbon.org -- against proprietary attachments

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)
  • From Erland Sommarskog@21:1/5 to Anton Shepelev on Fri Jul 12 23:48:29 2024
    Anton Shepelev (anton.txt@g{oogle}mail.com) writes:
    Hello, all.

    I wanted to create a convenient table-type for storing time
    series (with possibly non-uniqe sample marks):

    CREATE TYPE series_t AS TABLE
    ( moment DATETIME NOT NULL,
    value FLOAT
    )

    For efficient querying and joining, I need a covering index
    on the [moment] field that includes [value], but the INCLUDE
    keyword does not seem to be suported:


    Starting with SQL 2019, it is:

    CREATE TYPE series_t AS TABLE
    ( moment DATETIME NOT NULL,
    value FLOAT,
    INDEX Main(moment)
    INCLUDE (value)
    )

    If you are an older version, I would suggest adding value as an index key
    is the best in most situations.

    I will submit an edit to the CREATE TYPE topic.

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)
  • From Anton Shepelev@21:1/5 to All on Mon Jul 15 14:27:08 2024
    Erland Sommarskog to Anton Shepelev:

    For efficient querying and joining, I need a covering
    index on the [moment] field that includes [value], but
    the INCLUDE keyword does not seem to be suported:

    Starting with SQL 2019, it is:

    CREATE TYPE series_t AS TABLE
    ( moment DATETIME NOT NULL,
    value FLOAT,
    INDEX Main(moment)
    INCLUDE (value)
    )

    Glad to know MSSQL is improving, although I am using an
    older version.

    If you are an older version, I would suggest adding value
    as an index key is the best in most situations.

    Indeed. That's that I did.

    I will submit an edit to the CREATE TYPE topic.

    Thank you. I myself have fixed a few of their documentation
    articles about .NET, including those describing the
    connection-string syntax. How strange that Microsoft has
    decidied to let the community wrote their documentation...

    --
    () ascii ribbon campaign -- against html e-mail
    /\ www.asciiribbon.org -- against proprietary attachments

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)