Adding an index / Full Text Search on some partial data for a Sql 2008 table field?

Question

Hi folks,

i have a field that contains guid's. They represent a user's PunkBuster GUID.

Is it possible to create an index, on this field, which only index's the last 8 characters of the guid?

The reason i'm asking is that you cannot to a full text search like => '*abcdef'. (at least I believe I read that somewhere ... was it Pro Full-Text Search In Sql 08?)

Possible solutions (which i'm not too fond of, are) :-

• New table field -> NVARCHAR(8). This contains the last 8 characters of the guid. Add an index on this field.
• New table filed -> UNIQUE IDENTIFIER. Reverse the guid, then save it in this field. Then i can do an FTS on the guid, but reverse the query that was requested, then search on it -> 'fedcba*'

Now both ways would work. But i don't want to add EXTRA data to the table .. espcially if it's the same data, just 'viewed' differently.

I've got the feeling that most people might say i should store the guid in reverse, because what happens if someone wants to search for the last 5 chars of a guid OR the last 12 chars of a guid?

So assuming we don't want to do any of those, for the purpose of this discussion, can we create a index on some partial data, in a field?

Answer 1

The only thing I can think of is creating an indexed view with either the 8 character field or reversed guid field you mention. Then creating the full text index on the indexed view.

Answer 2

You could always create a new, computed column and make it PERSISTED and then index that.

ALTER TABLE dbo.YourTable
  ADD LastEightGUID AS RIGHT(CAST(PunkBusterID AS VARCHAR(50)), 8) PERSISTED

and then create an index on that new field

CREATE NONCLUSTERED INDEX IX_LastEight 
   ON dbo.YourTable(LastEightGUID)

Marc