SQL Server Optimal Index on Substring?

Running SQL Server 2008, I have a table with a few million rows of log info dump from an app. I am trying to figure out how to write the best index to get relevant info.

Table looks like this. Just a sample, the actual message data is long.

CREATE TABLE #Info(
    [infoID] [int] IDENTITY(1,1) NOT NULL,
    [ActionHappened] [datetime] NOT NULL,
    [PartialMessage] [nvarchar](1500) NULL,
    [FullMessage] [nvarchar](max) NULL,
 CONSTRAINT [pk_info] PRIMARY KEY CLUSTERED([infoID] ASC)
      WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, 
            ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIM开发者_开发技巧ARY]

Insert Into #Info Values ( GETDATE() , 'Thread:05;D:Start;Msg:Activity A' , 'Thread:05;D:Start;Msg:Activity A abcdegghijklmnopqrstuvwxyvabcdegghijklmnopqrstuvwxyvabcdegghijklmnopqrstuvwxyvabcdegghijklmnopqrstuvwxyvabcdegghijklmnopqrstuvwxyvabcdegghijklmnopqrstuvwxyvabcdegghijklmnopqrstuvwxyvabcdegghijklmnopqrstuvwxyvabcdegghijklmnopqrstuvwxyvabcdegghijklmnopqrstuvwxyvabcdegghijklmnopqrstuvwxyv' )
Insert Into #Info Values ( dateadd( minute , 3 , GETDATE() ) , 'Thread:05;D:Start;Msg:Activity B' , 'Thread:05;D:Start;Msg:Activity B abcdegghijklmnopqrstuvwxyvabcdegghijklmnopqrstuvwxyvabcdegghijklmnopqrstuvwxyvabcdegghijklmnopqrstuvwxyvabcdegghijklmnopqrstuvwxyvabcdegghijklmnopqrstuvwxyvabcdegghijklmnopqrstuvwxyvabcdegghijklmnopqrstuvwxyvabcdegghijklmnopqrstuvwxyvabcdegghijklmnopqrstuvwxyvabcdegghijklmnopqrstuvwxyv' )
Insert Into #Info Values ( dateadd( minute , 6 , GETDATE() )  , 'Thread:05;D:Start;Msg:Activity C' , 'Thread:05;D:Start;Msg:Activity D abcdegghijklmnopqrstuvwxyvabcdegghijklmnopqrstuvwxyvabcdegghijklmnopqrstuvwxyvabcdegghijklmnopqrstuvwxyvabcdegghijklmnopqrstuvwxyvabcdegghijklmnopqrstuvwxyvabcdegghijklmnopqrstuvwxyvabcdegghijklmnopqrstuvwxyvabcdegghijklmnopqrstuvwxyvabcdegghijklmnopqrstuvwxyvabcdegghijklmnopqrstuvwxyv' )
Insert Into #Info Values ( dateadd( minute , 9 , GETDATE() )  , 'Thread:05;D:Start;Msg:Activity D' , 'Thread:05;D:Start;Msg:Activity E abcdegghijklmnopqrstuvwxyvabcdegghijklmnopqrstuvwxyvabcdegghijklmnopqrstuvwxyvabcdegghijklmnopqrstuvwxyvabcdegghijklmnopqrstuvwxyvabcdegghijklmnopqrstuvwxyvabcdegghijklmnopqrstuvwxyvabcdegghijklmnopqrstuvwxyvabcdegghijklmnopqrstuvwxyvabcdegghijklmnopqrstuvwxyvabcdegghijklmnopqrstuvwxyv' )
Insert Into #Info Values ( dateadd( minute , 15 , GETDATE() )  , 'Thread:05;D:Start;Msg:Activity E' , 'Thread:05;D:Start;Msg:Activity F abcdegghijklmnopqrstuvwxyvabcdegghijklmnopqrstuvwxyvabcdegghijklmnopqrstuvwxyvabcdegghijklmnopqrstuvwxyvabcdegghijklmnopqrstuvwxyvabcdegghijklmnopqrstuvwxyvabcdegghijklmnopqrstuvwxyvabcdegghijklmnopqrstuvwxyvabcdegghijklmnopqrstuvwxyvabcdegghijklmnopqrstuvwxyvabcdegghijklmnopqrstuvwxyv' )

I need to select from PartialMessage, ignoring everything before the second semicolon, and then return ActionHappened and FullMessage. For example I want to

select ActionHappened, FullMessage 
from #Info 
where PartialMessage Like '%Activity D%'

but of course not table scan and soft-search inside each column.

All ideas appreciated.

Thanks.

Could you create an indexed view to slice the fields apart, like Martin suggests? The following might work, but with the size of the field, it might not be beneficial.

• Create a field called ReversePartialMessage
• Using a trigger to populate this field with the REVERSE(partialMessage)
• Add an index on Both PartialMessage and ReversePartialMessage fields

• Make the WHERE clause

  where PartialMessage Like 'Activity D%' And ReversePartialMessage Like 'D ytivitcA%'

However, with the size of PartialMessage, I am not sure if the effort of these indexes would gain much. If the field can be shortened, these indexes might be practical. If you do create the indexes, be sure to visit the execution plan to see if they are actually used. The optimizer might decide a table scan is more efficient than the indexes. The work of merging the output of the two indexes, particularly with such large key values, might not yield any significant gains...