| I was working on my second site recently. It provides a | | | | table_name(unique_column_name);step 7. create |
| warehouse of projects, presentations and other | | | | fulltext search |
| resources. To enables users to find the relevant | | | | EXEC sp_fulltext_table |
| project i decided to put in a search box. Initially all | | | | 'table_name','create','catalog_name','index_name'step 8. |
| seemed 5 min job. I quickly wrote a simple SELECT | | | | add columns you want to search. you can add multiple |
| query and thought that job is done. But then i realized | | | | columns |
| that it isnt as easy as it appears. Many beginners will | | | | Exec sp_fulltext_column |
| find it hard to achieve a google like search in their site. | | | | 'table_name','column_name','add'step 9.full text index |
| There are two ways to include a search feature in | | | | tracking can be set to auto, manual or off. in auto |
| your site. | | | | mode whenever there is a change in database the |
| 1. Use google to embed a search box in your site. This | | | | index updates itself. in manual mode you need to |
| will enable surfers to search your site as well as | | | | start updation. in this real time updation is not possible. |
| googles. | | | | you can manually update using following command |
| 2. Use full text feature of SQL server to enable users | | | | EXEC sp_fulltext_table 'table_name', 'start_full'you |
| to search content of your site. | | | | can off the change tracking when you dont want the |
| Now the problem with the first solution is that it | | | | index to b updated(this itself defies the logic of fulltext |
| provides user an option to drift away from your site. | | | | search but in certain condition you may want to switch |
| Also the search results can be customized only to | | | | it off). to put the change tracking on auto mode use |
| certain extent. | | | | ALTER FULLTEXT INDEX ON uploadedfiles SET |
| I prefer the second solution for real developers. After | | | | CHANGE_TRACKING AUTOstep 10. this is the last |
| a bit of searching i found a simple way to achieve this. | | | | step. you need to activate the search. |
| Using full text feature you can get a google like search | | | | EXEC sp_fulltext_table 'uploadedfiles','activate' |
| box working on your site. Lets get to the code:step 1. | | | | Now the table is ready fo searching. but for searching |
| first create a database you want to usecreate | | | | you cannot use normal select query of types : |
| database database_namestep 2. use the database | | | | Select * from table_name where |
| you created aboveuse database_namestep 3. create | | | | column_name LIKES '% QUERY %'. this again looks |
| the table with one primary keycreate table | | | | for exact match.to search occurrence of multiple |
| table_name(column_name1 datatype | | | | words we need to use CONTAIN commandselect * |
| constraints,column_name2 datatype | | | | from table_name where contain(column_name,'query') |
| constraints,column_name3 datatype constraints, | | | | Above command search for query in mentioned |
| .... | | | | columns but it doesnt require words to be present in |
| ) | | | | same order eg. if you search for 'cat and mouse' and |
| Following commands are TSQL commands and runs | | | | if column contains 'mouse and cat' then the row will be |
| inbuilt SQL proceduresstep 4. enable fulltext search for | | | | returned but if all the words in query are not present in |
| the database | | | | same column then row wont be returned like for row |
| EXEC sp_fulltext_database 'enable'step 5. create a | | | | containing 'mouse and elephant' wont be returned. for |
| new catalog for the table you want to search | | | | success of such queries you need to search words |
| EXEC sp_fulltext_catalog 'catalog_name','create'step | | | | separately using AND |
| 6. create a unique index. the index must be made on a | | | | Select * from table_name where |
| unique column(preferrably the primary key) of the table | | | | contain(column_name,'word') and |
| you want to search. Note: index have max size of 900 | | | | contain(column_name,'word') |
| bytes so a comination of columns using more than 900 | | | | Exec sp_fulltext_column 'uploadedfiles','description','add' |
| bytes can cause problem in updation and selection | | | | And thats all. now you will be able to search your site |
| CREATE UNIQUE INDEX index_name ON | | | | on multiple columns. |