search for data in a column using containstable function

Question

Hi

We have a application where customers search for data. In this page we provide search type is a drop down list box.There are four choices are

1.Exact Phrase

2.Any of the Word

3.All of the words

4.Boolean Search

the user type a string and select one of the search type from this list,Then accordingly we have to show the results. We have to write a sql query to return results.We have wriiten sql using containstable().But it does not return all the results.

The below query we are using..

SELECT Distinct TOP 50 c.case_id,cal.cas_details
From g_case_action_log cal (READUNCOMMITTED)                    
Inner Join g_case c (READUNCOMMITTED) ON (cal.case_id = c.case_id)
INNER JOIN containstable(es.g_case_action_log, cas_details,
 ' "searchword" OR "<br>searchword" OR "searchword<br> " ') AS key_tbl 
 ON cal.log_id = key_tbl.[key] 

Is there any improvements to get all the results

Or Any one has implemented the same scenario. can you please help this issue.

With Regards

Krishna

Answer 1

Hi Krishna,

Thanks for your question!

Please post the DDL and sample data for us to recover your scenario. We cannot detect what is the problem only depends on your script. And also provide the real result and your expected result returned by this statement.

---

Best Regards,
Iric
Please remember to mark the replies as answers if they help and unmark them if they provide no help.

Answer 2

did your table has full text index..?

you should be using contains or freetext functions...

Answer 3

Hi Iric Wen and skc_chat,

We have implemented the above logic but not returning results all the time.I am surprised that the query not able to searching in recent log entries. I mean my table having 200 lakhs records till now.If I try to search for a word that is recently inserted,this word is not coming in results.

After I debug the results by date ,the rows are returned if  the inserted date is below Feb'2012 . this is the problem i am struggling .

And we enabled FTS , catalog and FTindexed ,but not able to search for the recent entries.

Please find the below script that is my table create with schemas.

/****** Object:  Table [es].[g_case_action_log]    Script Date: 08/03/2012 18:39:21 ******/
SET ANSI_NULLS OFF
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [es].[g_case_action_log](
	[log_id] [int] IDENTITY(501700,1) NOT FOR REPLICATION NOT NULL,
	[case_id] [int] NOT NULL,
	[event_type] [tinyint] NOT NULL,
	[event_description] [nvarchar](255) NOT NULL,
	[created_time] [datetime] NOT NULL,
	[username] [int] NOT NULL,
	[is_internal] [bit] NOT NULL,
	[status_id] [smallint] NULL,
	[product_id] [int] NOT NULL,
	[client_username] [int] NULL,
	[company_id] [int] NOT NULL,
	[case_email_address] [nvarchar](100) NULL,
	[user_notify] [tinyint] NULL,
	[severity_id] [tinyint] NULL,
	[activity_owner] [int] NULL,
	[cas_comm_pref] [nvarchar](15) NULL,
	[cas_notify_support] [tinyint] NULL,
	[cas_release] [int] NULL,
	[cas_type] [nvarchar](30) NULL,
	[cas_responsibility] [int] NOT NULL,
	[cas_escalated_case] [tinyint] NULL,
	[cas_case_owner] [int] NULL,
	[previous_action_text] [nvarchar](80) NULL,
	[cas_response_due] [datetime] NULL,	
	[cas_details] [nvarchar](max) NULL, -- this is the column contains the raw data to search for 
	[no_html_cas_details] [nvarchar](max) NULL,
	[cas_route] [int] NULL,
	[cas_action_text] [nvarchar](32) NULL,
	[action_date] [datetime] NULL,
	[lock_time_start] [datetime] NULL,
	[lock_time_end] [datetime] NULL,
	[previous_action_date] [datetime] NULL,
	[internal_only] [bit] NOT NULL,
	[type_id] [tinyint] NULL,
	[is_internal_report_event] [bit] NOT NULL,
	[owner_group] [int] NULL,
	[is_internal_staracct_event] [bit] NOT NULL,
	[initial_case_response] [bit] NOT NULL,
	[is_geac_internal] [bit] NOT NULL,
	[geac_internal_only] [bit] NOT NULL,
	[time_spent] [int] NULL,
	[alt_user_notify] [tinyint] NULL,
	[alt_case_email_address] [nvarchar](100) NULL,
	[hardware_vendor_name] [nvarchar](500) NULL,
	[hardware_vendor_mailIds] [nvarchar](500) NULL,
 CONSTRAINT [pk_g_case_action_log] PRIMARY KEY CLUSTERED 
(
	[log_id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]

GO

ALTER TABLE [es].[g_case_action_log] ADD  DEFAULT (getdate()) FOR [created_time]
GO

ALTER TABLE [es].[g_case_action_log] ADD  DEFAULT (0) FOR [internal_only]
GO

ALTER TABLE [es].[g_case_action_log] ADD  DEFAULT (0) FOR [is_internal_report_event]
GO

ALTER TABLE [es].[g_case_action_log] ADD  DEFAULT (0) FOR [is_internal_staracct_event]
GO

ALTER TABLE [es].[g_case_action_log] ADD  DEFAULT (0) FOR [initial_case_response]
GO

ALTER TABLE [es].[g_case_action_log] ADD  DEFAULT (0) FOR [is_geac_internal]
GO

ALTER TABLE [es].[g_case_action_log] ADD  DEFAULT (0) FOR [geac_internal_only]
GO

/****** Object:  Index [ix_g_case_action_log]    Script Date: 08/03/2012 18:58:06 ******/
CREATE NONCLUSTERED INDEX [ix_g_case_action_log] ON [es].[g_case_action_log] 
(
	[case_id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 90) ON [PRIMARY]
GO

And providing real results is difficult as there are crores of records.

please let me know if you need any further info.

Regards

Krishna