Function-Based Indexes - Oracle to SQL Server Migration

In Oracle, you can create a function-based index that stores precomputed results of a function or expression applied to the table columns.

Function-based indexes are used to increase the performance of queries that use functions in the WHERE clause.

Oracle:

  -- Create a function-based index that stores names in upper case
  CREATE INDEX cities_fn_idx ON cities (UPPER(name));
 
  -- Index range scan will be used instead of expensive full table scan
  SELECT name FROM cities WHERE UPPER(name) = 'HOUSTON';

SQL Server does not support function-based indexes, but you can use computed columns and indexes on computed columns to increase the performance of queries that use functions in the WHERE clause.

Note that for case-insensitive search in SQL Server, you do not need function-based indexes, you have to use case-insensitive collation instead (it is default).

SQL Server:

  -- Search is case-insensitive in SQL Server by default, no need to use UPPER function
  SELECT name FROM cities WHERE name = 'HOUSTON';

In other cases, you can use a computed column and index on it:

SQL Server:

  -- Define a computed column
  CREATE TABLE cities
  (
     ...
     upper_name AS UPPER(name) 
  );
 
  CREATE INDEX cities_fn_idx ON cities (upper_name));

Function-Based Index Overview

Summary information:

Oracle SQL Server
Feature Function-based index Index on computed column
Collation for case-insensitive search
Built-in Functions
Expressions
WHERE Clause No changes required after index added No changes required after computed column and
index are added
INSERT Statement No changes required No changes required

Last Update: Oracle 11g R2 and Microsoft SQL Server 2012

Function-Based Index Details

Assume that you want to perform a case-insensitive search by a city name:

Oracle:

  -- Define a table
  CREATE TABLE cities 
  (
     name VARCHAR2(90),
     state CHAR(2),
     notes VARCHAR2(90)
  );
 
  -- Insert some data
  INSERT INTO cities VALUES ('Houston', 'TX', '4th largest city in the US, and the largest city in Texas');
  INSERT INTO cities VALUES ('Seattle', 'WA', 'the largest city in the Pacific Northwest');
 
  -- Create index (non-function based) on name column
  CREATE INDEX cities_name_idx ON cities (name);

When there is a regular (non-function based) index define on a table, it is not used when you to perform a case-insensitive search (use a function in WHERE clause):

Oracle:

  -- Enable trace in SQL*Plus
  SET AUTOTRACE ON
 
  SELECT name, state, notes
  FROM cities
  WHERE UPPER(name) = 'HOUSTON';  
 
  -- Fragment of SQL*Plus statistics
  # Execution Plan
  # 0 SELECT STATEMENT  
  # 1   TABLE ACCESS FULL | CITIES

You can see that full table scan is used that can be very expensive when the table contain a lot of rows.

Now let's create a function-based index and run the query again:

  -- Create a function-based index to index names in upper case
  CREATE INDEX cities_fn_idx ON cities (UPPER(name));
 
  SELECT name, state, notes
  FROM cities
  WHERE UPPER(name) = 'HOUSTON';  
 
  -- Fragment of SQL*Plus statistics
  # Execution Plan
  # 0 SELECT STATEMENT  
  # 1   TABLE ACCESS BY INDEX ROWID | CITIES 
  # 2      INDEX RANGE SCAN  | CITIES_FN_IDX

Now index range scan is used that can significantly increase the performance if the table contains a lot of rows and the index is selective (few rows meet the selection criteria).

Use Computed Columns to Implement Function-Based Indexes in SQL Server

In SQL Server, you can use a computed column and index defined on the computed column to increase the performance of a query that uses a function in WHERE condition.

Case-Insensitive Search

SQL Server allows you to use a case-insensitive collation (and it is default) so you do not need to use UPPER function and function-based index to perform a case-insensitive search in SQL Server, you can use a regular index on column.

SQL Server:

  -- Define a table
  CREATE TABLE cities 
  (
     name VARCHAR(90),
     state CHAR(2),
     notes VARCHAR(90)
  );
 
  -- Insert some data
  INSERT INTO cities VALUES ('Houston', 'TX', '4th largest city in the US, and the largest city in Texas');
  INSERT INTO cities VALUES ('Seattle', 'WA', 'the largest city in the Pacific Northwest');
 
    -- Create index (non-function based) on name column
  CREATE INDEX cities_name_idx ON cities (name);

UPPER function is not required to return the correct results, so index on name column can be used:

  SELECT name, state, notes
  FROM cities
  WHERE name = 'HOUSTON';

Output:

name state notes
Houston TX 4th largest city in the US, and the largest city in Texas

Implementing a Function-Based Index

But let's use UPPER function as an example how to implement a function-based index using a computed column and index on it in SQL Server:

SQL Server:

  -- Define a table with a computed column
  CREATE TABLE cities 
  (
     name VARCHAR(90),
     state CHAR(2),
     notes VARCHAR(90),
     upper_name AS UPPER(name)
  );
 
  -- Insert some data
  INSERT INTO cities VALUES ('Houston', 'TX', '4th largest city in the US, and the largest city in Texas');
  INSERT INTO cities VALUES ('Seattle', 'WA', 'the largest city in the Pacific Northwest');
 
  -- Create an index on computed column that acts as the function-based index
  CREATE UNIQUE INDEX cities_fn_idx ON cities (upper_name);

Note that SQL Server allows you to not specify columns list (name, state, notes) although INSERT omits the value for the computed column.

Now SQL Server uses the index on the computed column even if you still use UPPER(name) function in the query, not only upper_name column:

SQL Server:

  SELECT name, state, notes
  FROM cities
  WHERE UPPER(name) = 'HOUSTON';

Execution plan:

There are just 2 rows in the table, so we used UNIQUE index to force SQL Server to use it, and demonstrate that a index on a computed column can be used even if the query still uses a function in WHERE.

Resources

Oracle 11g R2 Database Concepts

Oracle 11g R2 Database Administrator's Guide

Microsoft SQL Server 2012 - Books Online

Microsoft SQL Server 2008 R2 - Books Online

SQLines Services

SQLines offers services to migrate Oracle databases and applications to Microsoft SQL Server. For more information, please Contact Us.

You could leave a comment if you were logged in.