Full Text Search

In my table i need to use Full Text Search on text column
here is my table
==================================
CREATE TABLE [AH_SubMaster] (
[Master_ID] [int] NOT NULL ,
[Fakra_No] [smallint] NOT NULL ,
[Fakra_Text] [text] COLLATE Arabic_BIN NOT NULL ,
[Index_ID] [int] NULL ,
[UserID] [int] NULL ,
[LastModify] [datetime] NULL ,
CONSTRAINT [MyKey_PK_1] PRIMARY KEY CLUSTERED
(
[Master_ID],
[Fakra_No]
) ON [PRIMARY] ,
CONSTRAINT [FK_AH_SubMaster_AH_Master] FOREIGN KEY
(
[Master_ID]
) REFERENCES [AH_Master] (
[ID]
) ON UPDATE CASCADE
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
==================================
When i use Query " Like '%sometext%' " it take long time, so i tried this
==========================
"select Fakra_Text from AH_SubMaster where contains(*,'sometext') "
=============================
but didn't work so how could i fix this search delay without changing my
column data type to varchar coz this is not possible And Can i use full text
search on this table without havinng full text index' and if i can then
How'
any help pleasePlease explain "didn't work". Were there errors? Did you get unexpected or n
o
results?
ML
http://milambda.blogspot.com/|||You need to create a Full-Text index (create a catalogue, add the table and
columns to index) then the CONTAINS and FREETEXT stuff will work.
You can create a Full-Text index through Enterprise Manager (or Management
Studio in 2005).
Best bet is to check the topic out in books online first to make sure you
understand the architecture fundementals first.
Tony
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"Islamegy" <NULL_Islamegy_NULL@.yahoo.com> wrote in message
news:uUbmOt6CGHA.272@.TK2MSFTNGP10.phx.gbl...
> In my table i need to use Full Text Search on text column
> here is my table
> ==================================
> CREATE TABLE [AH_SubMaster] (
> [Master_ID] [int] NOT NULL ,
> [Fakra_No] [smallint] NOT NULL ,
> [Fakra_Text] [text] COLLATE Arabic_BIN NOT NULL ,
> [Index_ID] [int] NULL ,
> [UserID] [int] NULL ,
> [LastModify] [datetime] NULL ,
> CONSTRAINT [MyKey_PK_1] PRIMARY KEY CLUSTERED
> (
> [Master_ID],
> [Fakra_No]
> ) ON [PRIMARY] ,
> CONSTRAINT [FK_AH_SubMaster_AH_Master] FOREIGN KEY
> (
> [Master_ID]
> ) REFERENCES [AH_Master] (
> [ID]
> ) ON UPDATE CASCADE
> ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
> GO
> ==================================
> When i use Query " Like '%sometext%' " it take long time, so i tried this
> ==========================
> "select Fakra_Text from AH_SubMaster where contains(*,'sometext') "
> =============================
> but didn't work so how could i fix this search delay without changing my
> column data type to varchar coz this is not possible And Can i use full
> text search on this table without havinng full text index' and if i can
> then How'
> any help please
>|||Sorry.. I was mean Like take 1 min 10 Sec and contain take 55 Sec so i don't
see any improvment...
that table that was having the problem have composite keys so it wasn't
allow full text index but i fixed this..
any help in full text search speed'
my database is arabic text
"Tony Rogerson" <tonyrogerson@.sqlserverfaq.com> wrote in message
news:evfYfI7CGHA.312@.TK2MSFTNGP09.phx.gbl...
> You need to create a Full-Text index (create a catalogue, add the table
> and columns to index) then the CONTAINS and FREETEXT stuff will work.
> You can create a Full-Text index through Enterprise Manager (or Management
> Studio in 2005).
> Best bet is to check the topic out in books online first to make sure you
> understand the architecture fundementals first.
> Tony
> --
> Tony Rogerson
> SQL Server MVP
> http://sqlserverfaq.com - free video tutorials
>
> "Islamegy" <NULL_Islamegy_NULL@.yahoo.com> wrote in message
> news:uUbmOt6CGHA.272@.TK2MSFTNGP10.phx.gbl...
>|||Hi, its probably worth posting it to microsoft.public.sqlserver.fulltext and
it will be picked up by somebody with more experience of full-text,
hopefully in non english indexing.
Sorry.
tony.
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"Islamegy" <NULL_Islamegy_NULL@.yahoo.com> wrote in message
news:uPTypn8CGHA.748@.TK2MSFTNGP12.phx.gbl...
> Sorry.. I was mean Like take 1 min 10 Sec and contain take 55 Sec so i
> don't see any improvment...
> that table that was having the problem have composite keys so it wasn't
> allow full text index but i fixed this..
> any help in full text search speed'
> my database is arabic text
>
> "Tony Rogerson" <tonyrogerson@.sqlserverfaq.com> wrote in message
> news:evfYfI7CGHA.312@.TK2MSFTNGP09.phx.gbl...
>

Full Text Index Error

I am trying to define Full Text Indexing on the following table:
CREATE TABLE [dbo].[CCINFORMATION] (
[Name] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[AWID] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Title] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Status] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Steffdt] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CostCtr] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CostCtrEffdt] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PrevCostCtr] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Email] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[MgrName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[MgrAWID] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[MgrEmail] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[MgrTitle] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ID] [int] IDENTITY (1, 1) NOT NULL
) ON [PRIMARY]
GO

But it keeps giving me the following error when I try to run the wizard:
The selected table has no
unique single column index on a column that does not allow NULLS.

Database: CCReport

Table: [dbo].[CCINFORMATION]

Both the AWID field, and the ID field (which I added after first receiving this message), are NOT NULL...what am I doing wrong here?I have a solution to this. The syntaxes which you have given..I have copied the same and run. It did give me the error which you have mentioned.

Go to table design and create a Primakry Key. I have set the ID column as the primary key. Now it creates the Full-Text Index.

Thanks & Regards,
Dipayan Sarkar
(dipayan@.covisible.com)
[CoVisible Solutions - a Knowledge Management Software Company)

Full self join problem

Hi I have the following table
CREATE TABLE [Sales] (
[StoreID] [int] NOT NULL ,
[CatName] [varchar] (100) COLLATE Latin1_General_CI_AS NOT NULL ,
[WEDate] [smalldatetime] NOT NULL ,
[Best_Sales] [real] NOT NULL ,
[Corrected_Sales] [real] NOT NULL ,
[Total_Waste] [real] NOT NULL ,
[Availability] AS (case when ([Corrected_Sales] > 0 and ([Best_Sales]
> 0)) then ([Best_Sales] / [Corrected_Sales] * 100) else 0 end) ,
[Waste] AS (case when ([Total_Waste] > 0 and ([Best_Sales] > 0)) then
([Total_Waste] / [Best_Sales] * 100) else 0 end) ,
CONSTRAINT [PK_Sales] PRIMARY KEY CLUSTERED
(
[StoreID],
[CatName],
[WEDate]
) ON [PRIMARY] ,
CONSTRAINT [FK_Sales_Stores] FOREIGN KEY
(
[StoreID]
) REFERENCES [Stores] (
[ID]
) ON DELETE CASCADE ON UPDATE CASCADE
) ON [PRIMARY]
GO
The table holds sales records of a particular product catergory, in a
particular store in a particular week.
One week a catergory may be present which is not present in all weeks.
I would like to be able to compare sales of one week against another as
shown in the following example
Week,Cat,Store,Sales,Week,Cat,Store,Sales
1,Coat,50,50,2, coat, 50,10
1Hat,50, 10,null,null,null,null
null,null,null,null,2,Gloves,50,10
I have tried many different queries using full, left, right joins and
unions but i always seem to be only to get the result of an inner join.
here is one example i have tried
select a.catname from sales a
full join MandS.dbo.Sales b
on b.catname = a.catname
and b.weDate = '2006-09-23'
and b.storeid = 259
where a.weDate = '2006-09-16'
and a.storeid = 259
any help would be greatly appreciated.
Matt
On 28 Sep 2006 11:37:39 -0700, Matt S wrote:
(snip)
>here is one example i have tried
>select a.catname from sales a
>full join MandS.dbo.Sales b
>on b.catname = a.catname
>and b.weDate = '2006-09-23'
>and b.storeid = 259
>where a.weDate = '2006-09-16'
>and a.storeid = 259
Hi Matt,
The inclusion of columns from table a in the WHERE negates the effect of
the outer join - if a row from b was retained though there were no
matching values in a, the a columns are NULL, and the WHERE will discard
the row.
Try either
FROM (SELECT ?
FROM sales
WHERE weDate = '20060916'
AND storeid = 259) AS a
FULL JOIN (SELECT ?
FROM MandS.dbo.sales
WHERE weDate = '20060923'
AND storeid = 259) AS b
ON b.catname = a.catname
Or
FROM sales AS a
FULL JOIN MandS.dbo.Sales AS b
ON b.catname = a.catname
AND b.weDate = '20060923'
AND b.storeid = 259
AND a.weDate = '20060916'
AND a.storeid = 259
Hugo Kornelis, SQL Server MVP
|||thanks hugo the 1st option worked.

> FROM (SELECT ?
> FROM sales
> WHERE weDate = '20060916'
> AND storeid = 259) AS a
> FULL JOIN (SELECT ?
> FROM MandS.dbo.sales
> WHERE weDate = '20060923'
> AND storeid = 259) AS b
> ON b.catname = a.catname
>
I don't know why this post took so long to apear over 6 hours, which is
why it it listed several times.
Thanks for your help

Full self join problem

Hi I have the following table
CREATE TABLE [Sales] (
[StoreID] [int] NOT NULL ,
[CatName] [varchar] (100) COLLATE Latin1_General_CI_AS NOT NULL ,
[WEDate] [smalldatetime] NOT NULL ,
[Best_Sales] [real] NOT NULL ,
[Corrected_Sales] [real] NOT NULL ,
[Total_Waste] [real] NOT NULL ,
[Availability] AS (case when ([Corrected_Sales] > 0 and ([Best_Sales]reen">
> 0)) then ([Best_Sales] / [Corrected_Sales] * 100) else 0 end) ,
[Waste] AS (case when ([Total_Waste] > 0 and ([Best_Sales] > 0))
then
([Total_Waste] / [Best_Sales] * 100) else 0 end) ,
CONSTRAINT [PK_Sales] PRIMARY KEY CLUSTERED
(
[StoreID],
[CatName],
[WEDate]
) ON [PRIMARY] ,
CONSTRAINT [FK_Sales_Stores] FOREIGN KEY
(
[StoreID]
) REFERENCES [Stores] (
[ID]
) ON DELETE CASCADE ON UPDATE CASCADE
) ON [PRIMARY]
GO
The table holds sales records of a particular product catergory, in a
particular store in a particular week.
One week a catergory may be present which is not present in all weeks.
I would like to be able to compare sales of one week against another as
shown in the following example
Week, Cat, Store, Sales, Week, Cat, Stor
e, Sales
1, Coat, 50, 50, 2, coat, 50, 10
1 Hat, 50, 10, null, null, null, null
null, null, null, null, 2, Gloves, 50, 1
0
I have tried many different queries using full, left, right joins and
unions but i always seem to be only to get the result of an inner join.
here is one example i have tried
select a.catname from sales a
full join MandS.dbo.Sales b
on b.catname = a.catname
and b.weDate = '2006-09-23'
and b.storeid = 259
where a.weDate = '2006-09-16'
and a.storeid = 259
any help would be greatly appreciated.
MattOn 28 Sep 2006 11:37:39 -0700, Matt S wrote:
(snip)
>here is one example i have tried
>select a.catname from sales a
>full join MandS.dbo.Sales b
> on b.catname = a.catname
> and b.weDate = '2006-09-23'
> and b.storeid = 259
>where a.weDate = '2006-09-16'
>and a.storeid = 259
Hi Matt,
The inclusion of columns from table a in the WHERE negates the effect of
the outer join - if a row from b was retained though there were no
matching values in a, the a columns are NULL, and the WHERE will discard
the row.
Try either
FROM (SELECT '
FROM sales
WHERE weDate = '20060916'
AND storeid = 259) AS a
FULL JOIN (SELECT '
FROM MandS.dbo.sales
WHERE weDate = '20060923'
AND storeid = 259) AS b
ON b.catname = a.catname
Or
FROM sales AS a
FULL JOIN MandS.dbo.Sales AS b
ON b.catname = a.catname
AND b.weDate = '20060923'
AND b.storeid = 259
AND a.weDate = '20060916'
AND a.storeid = 259
Hugo Kornelis, SQL Server MVP|||thanks hugo the 1st option worked.

> FROM (SELECT '
> FROM sales
> WHERE weDate = '20060916'
> AND storeid = 259) AS a
> FULL JOIN (SELECT '
> FROM MandS.dbo.sales
> WHERE weDate = '20060923'
> AND storeid = 259) AS b
> ON b.catname = a.catname
>
I don't know why this post took so long to apear over 6 hours, which is
why it it listed several times.
Thanks for your help