Sql Server 7 - Full Text Indexing

Front 1

sp_fulltext_catalog:

6 actions

Back 1

1. CREATE --> creates an empty catalog and registers it with the current db
2. DROP --> drops the specified catalog
3. START_INCREMENTAL --> performs an incremental update of the catalog. if there is a timestamp column just checks the new rows, otherwise it checks them all
4. START_FULL --> completely repopulates all indexes in the catalog
5. STOP --> halts a catalog population action
6. REBUILD --> deletes and rebuilds (but not repopulates) catalog.

Front 2

sp_fulltext_column:
1. syntax
2. two actions

Back 2

1. sp_fulltext_column 'table', 'column', 'action'
2. add or drop

Front 3

sp_fulltext_service: 3 options

Back 3

1. resource_useage --> 1-5 3 default. sets priority of search service. can run in background or suck resources
2. connection_timeout --> number of secs sql server waits for timeout
3. clean_up --> removes non-used catalogs

Front 4

sp_fulltext_catalog syntax

Back 4

sp_fulltext_catalog 'catalog name', 'action', 'root directory'

all full text procs have quoted attributes

Front 5

sp_fulltext_database: one option

Back 5

'enable' or 'disable'

Front 6

sp_fulltext_table: 4 actions

Back 6

1. CREATE --> registers a table for full text indexing in a given catalog
2. DROP --> unregisters a table for full text searching
3. ACTIVATE --> allow full text indexes on the table to be populated with the rest of the catalog
4. DEACTIVATE --> disallows populating of indexes defined on the table.

Front 7

sp_fulltext_table syntax

Back 7

sp_fulltext_table 'table','action','catalog','index'

table must have a unique index on a single column

Front 8

5 configuration levels for full_text searching

Back 8

service
database
catalog
table
column

Front 9

CONTAINSTABLE and FREETEXTABLE functions.
1. useage
2. return results

Back 9

1. search similar to their predicate counterparts, but return matches as a table that can be joined to the table searched.
2. the table returned has 2 columns --> key and rank.

rank is 0 - 1000 and higher is better

Front 10

FREETEXT predicate
definition and syntax

Back 10

1. the search engine will parse the string given and assign a weight to terms, then return what rows it finds
2. freetext(column(s), 'string')

Front 11

CONTAINS predicate <simple_term>
definition and syntax

Back 11

1. a single work or phrase. matche when found in same order. spelling counts. noise words are ignored. 1 noise word produces an error
2. word | "phrase"

Front 12

CONTAINS predicate <prefix_term>
definition and syntax

Back 12

1. searches for a word or phrase beginning with
2. "word*" | "phrase*"
contains(title, '"word*"')

Front 13

CONTAINS predicate <proximity_term>
definition and syntax

Back 13

1. search specifying 2 or more terms should be found be they can be seperated by words. returned with a rank

2. simple_term | prefix_term NEAR simple_term | prefix_term

Front 14

CONTAINS predicate <generation_term>
definition and syntax

Back 14

1. search with a simple term for various forms of the word

FORMSOF(INFLECTIONAL, <simple_term>...)

Front 15

CONTAINS predicate <weighted_term>
definition and syntax

Back 15

1. a parenthetical list of terms, each with a weight. reows with any term are returned but those with a higher weight recieve a higher rank

ISABOUT(term wieght(value)...)