----------------------------------------------------------- New Message on MumbaiUserGroup
----------------------------------------------------------- From: VinodK Message 6 in Discussion Hi all, Well was really delighted to see a SQL Server question asked ... :) ... But here is my 20Cents to the whole problem. I would also like to comment on replies from others also here. First since your DB column contains upto 2000 charaters you cannot index the column if the length of the data exceeds more than 900 charaters. This is by design we have this limitation. Now if you try having a column of Varchar(2000) and try to index the same it would work provided it contains data is lesser than 900 charaters. Once a data exceeds then you are going to get an error while insert. Hence using an Index is not a feasible option. Secondly there was a suggestion to use "%Keyword%" based query. This is one of the most costly operation you are going to have. This is because the table is not normalized to hold the services separatly hence we will scan the whole column data and hence will incurr a table scan or clustered index scan while retriving the data. This is something I donot recommend. You may use a % operator after the Keyword. But not a text search for this problem. Next is the Free-text search. This does to some extend reduce the costly operation I suggested in second point. Well this is quite a good option to have and done. But remember that there is a lot of resource overhead in maintaining this search engine outside the SQL Server process boundary. It is optimum without any doubts but there is a heavy resource utilization while it is updating the statistics. And moreover building statistics for millions of records would be a nightmare and time consuming. It is not feasible for a 24/7/365 type of requirement. And the updates have to made at designated intervals only. Hence you cannot get up-to-moment records. This is something you need to live with. And ofcourse that leaves us with one option i.e. normalizing the whole table structure and storing the Services in a separte table and reference the FKey to this master table. Hence the search on the Child table is based on the services. This is my choice ... These are specifically my views on this problem. Iam open to constructive comments from others here... :) ... Whew, HTH, Regards, Vinod Kumar Microsoft India Community Star ----------------------------------------------------------- To stop getting this e-mail, or change how often it arrives, go to your E-mail Settings. http://groups.msn.com/mumbaiusergroup/_emailsettings.msnw Need help? If you've forgotten your password, please go to Passport Member Services. http://groups.msn.com/_passportredir.msnw?ppmprop=help For other questions or feedback, go to our Contact Us page. http://groups.msn.com/contact If you do not want to receive future e-mail from this MSN group, or if you received this message by mistake, please click the "Remove" link below. On the pre-addressed e-mail message that opens, simply click "Send". Your e-mail address will be deleted from this group's mailing list. mailto:[EMAIL PROTECTED]
