Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 Auto Updating an Index

Author  Topic 

doom
Starting Member

7 Posts

Posted - 2005-07-31 : 02:24:32
I have a indexed a table. How do i update the index automatically?
Is it possible to update the index periodically?

Moreover is it worth while to index large coulumns like a varchar (4000) or a text field?

Kristen
Test

22859 Posts

Posted - 2005-07-31 : 04:38:33
Hi Doom - Welcome to SQLTeam!

The index will update automatically as the data in teh column(s) changes.

An index will NOT be used (AFAIK) is you search for a field "containing" some string - e.g. SELECT * FROM MyTable WHERE MyColumn LIKE '%FOO%', so your index on a varchar(4000) column is only useful if you search for things that that columns starts with (or exactly matches)

There is also a free-text indexing system in SQL, so if you want to find text in a manner similar to Google etc. then that would be the tool of choce

Kristen
Go to Top of Page

doom
Starting Member

7 Posts

Posted - 2005-07-31 : 09:52:30
Thanks. I am trying to make a full site search system.
I wanted to use queries like containstable etc. For this i think i have to make a search system (Full text indexing i think).

I have a news and article publishing system. I want to give the users an option to search (like we have on google, advanced search etc) the entire site, entire news and articles section, or a particular category. Now the news is stored in a text field.

From what i have learnt from a few articles online, i should make a catalogue and then index the columns of the table. Now it was also said that indexing should always be done on columns which can be sorted and meet a complete match or something like abc%.

Now i dont know what kind of system to use for this. I can index usernames to search for users registered on the site etc.

Moreover i want to update this index that i have made in a catalogue automatically when i insert, update or delete some record.
Go to Top of Page

Kristen
Test

22859 Posts

Posted - 2005-07-31 : 11:29:42
I don't have practical experience of Full Text searching, but others here will.

The sort of full-site searching you mention would work well using Full Text searching. In particular if you can combine your Articles and News into a single "Text matter" table (you don't have to, but I think it would probably make the querries easier).

For the Username searches and so on then regular SQL indexes will work nicely, and seamlessly.

I don't know if Full Text searching automatically updates it indexes, but if not I'm sure there is a routine you can call to force an update - either on a timer schedule (SQL provides a scheduler that can be used) or every time a record in a given table is changed (you can use SQL's "Triggers" for this job)

Kristen
Go to Top of Page

doom
Starting Member

7 Posts

Posted - 2005-08-01 : 13:31:11
Thanks. I found this:

You need to use SQL Query Analyzer tool for this.

This will enable full-text indexing for the current database:
exec sp_fulltext_database 'enable'



This creates a catalog:
exec sp_fulltext_catalog 'catalogname', 'create'



This enables indexing of a table:
exec sp_fulltext_table 'tablename', 'create',
'catalogname', 'indexname'



This adds a column to an index:
exec sp_fulltext_column 'tablename', 'columnname', 'add'



This activates fulltext on a table:
exec sp_fulltext_table 'tablename', 'activate'



These two enable automatic filling of the full-text index when changes occur to a table:

exec sp_fulltext_table 'tablename',
'start_change_tracking'

exec sp_fulltext_table 'tablename',
'start_background_updateindex'


But if the index is updated on every change, wont it slow down?
Moreover can and should text fields be indexed?

Kirsten for username searches using the regular SQL indexes,u meant something like select columnnames from news where userid = ' somename '

The things are in a single table.
Go to Top of Page

Kristen
Test

22859 Posts

Posted - 2005-08-02 : 00:20:48
But if the index is updated on every change, wont it slow down?

Well that's how SQL Server works so its pretty well optimised, but there is a balance. For a column which a high number of changes (lots of UPDATES to existing records) the churn on the index will be significant. Indexing every column in a table will make INSERTs slow .... but if you do 10,000 SELECTs for each INSERT then that might be acceptible. "It all depends" , but there is certainly no point having an index which is not used (e.g. because it has poor selectivity)

Moreover can and should text fields be indexed?

Depends what you mean by a text field. A varchar(10)? Maybe. A varchar(8000) - I doubt it! If your WHERE clause is using an EQUALS test then indexes work well, but a LIKE '%FOO%' is unlikely to gain much from an index. So for columns that are used in [equal] JOINS indexes work well.

Kirsten for username searches using the regular SQL indexes,u meant something like select columnnames from news where userid = ' somename '

Yup, an index on the "userid" column would help with that.

Kristen

Go to Top of Page
   

- Advertisement -