0

Is it possible to create an Index on column with changes of this column. For example - I have a column A (nvarchar), But in query I should to replace values from this column to compare with values in list. Classical index will work only if I use original values from column A.

The query looks like next

SELECT
   * 
 FROM
    MyTable 
 WHERE
    REPLACE(A, ' ', '') IN ('aasa', 'asa', 'wew','wewe')
AndrewVA
  • 145
  • 2
  • 16
  • Using function in Where clause will avoid using `Index`. update your `A` column with `replace` function then replace function can be avoided in `where` clause. – Pரதீப் Feb 08 '17 at 07:05
  • @Prdp Yes, I know this way, but I need to store original values. At this moment I only see the way - to create new column B with replaced data and create index on this column. But there will be a lot of duplicated data. – AndrewVA Feb 08 '17 at 07:08
  • Possible duplicate of [SQL Server - index on a computed column?](http://stackoverflow.com/questions/1323214/sql-server-index-on-a-computed-column) – Serg Feb 08 '17 at 07:09

1 Answers1

0

You can create a computed column and then create an index on it.

Note: SQL Server Index key columns have a 900 byte size limit. Since your column is NVARCHAR, it consumes 2 bytes for each character. So, let's can cap the index at 400 characters (800 bytes).

To conserve space, we can further restrict this column to contain a value, only if it meets your required conditions.

ALTER TABLE MyTable
ADD A_NoSpace AS (CASE WHEN REPLACE(A, ' ', '') IN ('aasa', 'asa', 'wew','wewe') THEN LEFT(REPLACE(A, ' ', ''), 400) END) PERSISTED

CREATE NONCLUSTERED INDEX IX_A_NoSpace ON MyTable (A_NoSpace)

Being a PERSISTED column, calculation will take place only during INSERT or UPDATE operations.

You can now use this column in your query:

SELECT *
FROM
MyTable
-- Don't need to check if value is in the list,
-- because we're already doing this in the computed column definition
WHERE A_NoSpace IS NOT NULL
Serge
  • 3,986
  • 2
  • 17
  • 37