Answering the Queries Your Users Really Want to Ask

Answering the Queries Your Users Really Want to Ask

• Dr Greg Low
• glow@solidq.com
• SolidQ Australia

When querying a database, what do users want?

1. They’re not sure
2. They don’t want to be precise
3. They do want to use their own terminology
4. The want the answers fast

What do we give them?

1. Really limited choices
2. Really strict terminology
3. …

What we will cover

• What can you do for me?

Can’t we just use LIKE?

• WHERE Description LIKE ‘%hockey%’

Strings vs Words

• Search for: Pen
• Get Back: Pencil = fail
• Or Open, pendulum, penis

CONTAINS

• AND
• OR
• (‘”exact match”’)
• FORMSOF(INFLECTIONAL, word)
• FORMSOF(THESAURUS, word)

FREETEXT

• search for a sentence
• with ranking

CONTAINSTABLE

• ISABOUT (with weighting)

For true location of where to place your personalised thesaurus:

• don’t go to the Books Online site
• Instead read Greg’s blog here: http://sqlblog.com/blogs/greg_low
• SQLDATA\MSSQL10.MSSQLSERVER\MSSQL\...

Upgrade Options

• New Index Structure (the structure has been brought internal to the database engine)
• Upgrade options:
  1. Import(default)
  2. Rebuild
  3. Reset
• Possible Upgrade methods
  1. In place
  2. Restore/attach

Potential Extensions

• New data types
  1. XML data types
  2. CLR UDT
• Extend the IFTS feature set
  1. snippets with hit-highlights
  2. field weighted relevance
  3. customizable tokenizing
  4. customizable proximity operator
  5. property level search
• Be heard now!

Summary

• IFTS can add significant value
• Implementation -> straightforward
• Management -> straightforward
• Users love this
• SQL Server 2008 has changed the game!