SQL Server Full Text Indexing 1

[tinapa]

my apologies guys if i double post. it seems that i posted on the inappropriate forum for sql server first.

-------------

hi guys,

i just want to ask for your advice.

i have a sql server database with more than 30 tables and i want to implement a full-text indexing on it. there's around 15 main tables, around 10 link tables and 10 lookup tables. some tables share the same lookup tables. this database is around 70MB in size. my questions are:

1. is it a good idea to have different catalogues for each related tables? or 1 catalogue for all the tables?

2. will the full-text indexing affect the performance of the win2003 server? if the updating of the indexing is done everyday.

3. there were times in the past that i need to modify the structure of a table in the database. will this affect the indexing? will it still work if i need to modify a table again? this is the reason why i asked the 1st question above so that if i need to modify a table again, the indexing will not affect the whole lot.

4. is it a good idea to just have 1 catalog per table? can i still search the joined tables even if each table has its own catalog? will it affect the performance/speed of search?


thanks for any inputs.

 

[JayKusch]

1.) I would, imho, create a single catalog per table.

2.) Many factors are depenant on the answer to the question. What do the resources of the server look like (ie how much memory, how many and what type CPUs, are all catalogs on the same disk or are they spread out). Then there is the question of what other SQL process are running during the rebuild time. Also to note is what other NON-SQL related process are running.

I might also add that you may have a choice of how the catalogs are rebuilt depending on your version of SQL. You can rebuild on a schedule or have it maintain the catalog on the fly. I know SQL 05 does this but not 100% 2000 does.

3. I do not beleive the catalog will be affected if you are changing other elements in the table that are not used in building the catalogs index. So changing a column's type or size that is part of the catalogs indexing structure will probably deem you need to adjust the catalog.

4. Question 4 seems to be the same as 1. The addition comment would be YES you can still join your tables if you have each in a seperate catalog.

Thanks

J. Kusch

 

[tinapa]

thanks very much JayKusch for that valuable tips.

regards.