none
Help! Create index with substring

    Question

  • Good day!

    We had decided to migrate from Oracle to SQL Server, so faced some problems. Using Oracle we could create indexes like that

    create index obj_id_cnum on obj_id (substr(cnum,1,2))

    But Microsoft SQL Server doesn't allow this code. How can we do the same using SQL Server. Thanks you.

    Monday, July 24, 2006 1:00 PM

Answers

  • Is obj_id a table, I am assuming?   If so, then you are right, you cannot apply an index on a function directly.  Two ways you can approach this:

    1.  If using enterprise edition, you can index a view and it will be used:

    create view obj_id_indexed
    as
             select obj_id_key, cnum, substring(cnum,1,2) as cnumSub,
             from obj_id

    create unique clustered index on obj_id_cnum(obj_id_key, cnumSub)

    Now your queries will see this index on the the view and apply it just like the

    2.  You can however add a computed column to your table and then index it:

    alter table obj_id
       add  cnumSub as substring(cnum,1,2)

     

    I would suggest that all of these techniques are probably the "wrong" way to go about this sort of thing.  Almost any time you need to use a substring on a value in a SQL table there is a problem with normalization.  Better would be to break the column into two parts, and then you have a much better chance of making the indexes work for you. 

    Monday, July 24, 2006 4:13 PM
    Moderator

All replies

  • dynamic sql...

    declare @SQL nvarchar(100)

    set @SQL = 'create index [obj_id_cnum] on object_id(' + substring(cnum,1,2) + ')'

    exec sp_executesql @SQL

    Monday, July 24, 2006 1:29 PM
  • Is obj_id a table, I am assuming?   If so, then you are right, you cannot apply an index on a function directly.  Two ways you can approach this:

    1.  If using enterprise edition, you can index a view and it will be used:

    create view obj_id_indexed
    as
             select obj_id_key, cnum, substring(cnum,1,2) as cnumSub,
             from obj_id

    create unique clustered index on obj_id_cnum(obj_id_key, cnumSub)

    Now your queries will see this index on the the view and apply it just like the

    2.  You can however add a computed column to your table and then index it:

    alter table obj_id
       add  cnumSub as substring(cnum,1,2)

     

    I would suggest that all of these techniques are probably the "wrong" way to go about this sort of thing.  Almost any time you need to use a substring on a value in a SQL table there is a problem with normalization.  Better would be to break the column into two parts, and then you have a much better chance of making the indexes work for you. 

    Monday, July 24, 2006 4:13 PM
    Moderator
  • Thanks you very much! I do appreciate your help!
    Monday, July 24, 2006 8:44 PM
  •  Derek Comingore - RSC wrote:

    dynamic sql...

    declare @SQL nvarchar(100)

    set @SQL = 'create index [obj_id_cnum] on object_id(' + substring(cnum,1,2) + ')'

    exec sp_executesql @SQL

    set @SQL = 'create index [obj_id_cnum] on object_id(' + substring(cnum,1,2) + ')' gives an error 'Invalid column name 'cnum'.

    Monday, July 24, 2006 9:08 PM
  • Note that we are considering adding function / expression based indexes for a future version of SQL Server. For now, the easiest workaround is to use an indexed computed column.
    Monday, July 24, 2006 10:00 PM