Function-Based Indexes in SQL Server

Function-based indexes in SQL Server

I researched a bit further based on Damien's comment and found an answer that comes very close to matching Oracle's/PostgreSQL's function based indexes.

I have a table named PARCELS where I created a new column COMPUTEDPARCELS by using the alter statement as given below:

ALTER TABLE [PARCELS] ADD COMPUTEDPARCELS AS CONVERT(CHAR(8), [MAPNO], 112);

And then create an index on the computed column:

CREATE INDEX function_index ON [PARCELS](COMPUTEDPARCELS);

Of course the example is pretty simple but behaves just like a function based index.

SQL Server Function Based Index Without Computed Column

You cannot create an index based on a function in SQL Server. You can however create an index on a computed column (with restrictions, such as the computation has to use only deterministic functions).

In your specific case, you don't have to use any functions, just to modify your query slightly.

Since left(column, 3) = 'abc' equals to column like 'abc%' you can safely use it. SQL Server is able to use indexes with the LIKE operator when the pattern contains a constant at the beginning (abc in this case).

Function Based Index not improving query performance

I would suggest to review your data model, the regex is really ugly. Store relevant information directly in column instead of somewhere hidden in a ticket string.

Anyway, I would propose to create a virtual column instead of view. Then you can create an index on this virtual column and it should also be used. Would be similar to this:

ALTER TABLE TTT_IMP ADD (ROOT VARCHAR2(20) GENERATED ALWAYS AS (
CAST(
    CASE
    WHEN regexp_like(ticker, '\s.*\s')
    THEN SUBSTR(ticker, 1, instr(ticker, ' ')-1)
    WHEN regexp_like(ticker, '\s')
    THEN
        CASE
            WHEN regexp_like(SUBSTR(ticker, 1, instr(ticker, ' ')-1), '(P|C)$')
            AND LENGTH(SUBSTR(ticker, 1, instr(ticker, ' ')-1)) >= 4
            THEN SUBSTR(SUBSTR(ticker, 1, instr(ticker, ' ')-1), 1, LENGTH(SUBSTR(ticker, 1
                , instr(ticker, ' ')-1))-3)
            WHEN regexp_like(SUBSTR(ticker, 1, instr(ticker, ' ')-1), '\w\d\d\w\d$')
            THEN SUBSTR(SUBSTR(ticker, 1, instr(ticker, ' ')-1), 1, LENGTH(SUBSTR(ticker, 1
                , instr(ticker, ' ')-1))-5)
            WHEN regexp_like(SUBSTR(ticker, 1, instr(ticker, ' ')), '\w\d\w\d$')
            THEN SUBSTR(SUBSTR(ticker, 1, instr(ticker, ' ')-1), 1, LENGTH(SUBSTR(ticker, 1
                , instr(ticker, ' ')-1))-4)
            ELSE SUBSTR(ticker, 1, instr(ticker, ' ')-1)
        END
    WHEN regexp_like(ticker, '(P|C)$')
    AND LENGTH(ticker) >= 4
    THEN SUBSTR(ticker, 1, LENGTH(ticker)-3)
    WHEN regexp_like(ticker, '\w\d\d\w\d$')
    THEN SUBSTR(ticker, 1, LENGTH(ticker)-5)
    WHEN regexp_like(ticker, '\w\d\w\d$')
    THEN SUBSTR(ticker, 1, LENGTH(ticker)-4)
    ELSE ticker
    END
AS VARCHAR2(20))
) VIRTUAL);

Function-based index on function returning number from abstract datatype

You need to restrict an object type returned by a function to a specific (sub)type by using treat function:

create index idx1
on my_table (treat(
  mdsys.sdo_util.get_coordinate(shape,1)
  as sdo_geometry
).sdo_point.x
);

✓

create index idx12
on my_table(shape.sdo_point.x);

✓

db<>fiddle here

Which databases can create function-based indexes?

I don't know inner details of oracle's, but postgres can create index on expression, which can be a function, from :

An index field can be an expression
computed from the values of one or
more columns of the table row. This
feature can be used to obtain fast
access to data based on some
transformation of the basic data. For
example, an index computed on
upper(col) would allow the clause
WHERE upper(col) = 'JIM' to use an
index.

EDIT:
MySQL seems to still be forging this, see virtual columns for details. Also some discussions here. Don't seem very active.

DB2 does it.

MS SQL can not do it, but using computed columns you can have similar effects; see discussion.

How do function indexes work in SQL?

If you are talking about taking the result of a function when creating the index, this feature is used from SQL like this:

CREATE INDEX index_name ON table_name (function_name(column_name));

This can be used by the planner for queries like:

SELECT foo FROM table_name WHERE bar = function_name(column_name);

For exact details see the given RDBMS's documentation (for example in PostgreSQL it is called indexes on expressions).

What INCLUDE() function does when creating index in MS SQL Server?

The INCLUDE clause adds the data at the lowest/leaf level, rather than in the index tree. This makes the index smaller because it's not part of the tree.

INCLUDE columns are not key columns in the index, so they are not ordered. This means it isn't really useful for predicates, sorting etc.. However, it may be useful if you have a residual lookup in a few rows from the key columns.

INCLUDE columns are not key columns in the index, so they are not ordered. This makes them not typically useful for JOINs or sorting. And because they are not key columns, they don't sit in the whole B-tree structure like key columns

By adding Include (or nonkey)columns, you can create nonclustered indexes that cover more queries. This is because the nonkey columns have the following benefits:

• They can be data types not allowed as index key columns.
• They are not considered by the Database Engine when calculating the number of index key columns or index key size.

An index with Included columns can significantly improve query performance when all columns in the query are included in the index either as key or nonkey columns. Performance gains are achieved because the query optimizer can locate all the column values within the index; table or clustered index data is not accessed resulting in fewer disk I/O operations.

For more info refer Microsoft docs: Create Indexes with Included Columns

SQL Using a String Function on Indexed Column Performance

The reason there is a difference is because the RIGHT(ID,10) has to be resolved for every row in order to filter it (hence the scan), whereas the ID = '00000000009123548754' clause can be resolved to a simple seek.

In technical terms, your first query is sargable, while the second isn't (due to the function).

---

Related Topics