Monday, March 12, 2012
Function Suppressing Index
the clustered index on column "coldate" can be utilized. Any suggestions
would be appreciated. I realize that getting the functions off the column is
the goal, but I am not seeing how that might be done in this instance. Thank
s
in advance.
SELECT *
FROM
table t
WHERE
DateDiff(n, DateAdd(n, t.col1 - 1, t.coldate), @.enddate) <= 0
Message posted via http://www.droptable.comthis looks like homework.
you really can't because you are simultaneoulsy doing a calc on t.col1
for every row.
you could probably increase performance though by getting rid of
datediff, and just comparing the dates of hte dateadd results vs
@.enddate.|||Hi
If you don't need to have the date as a date then you may want to store
coldate as a offset for the number of minutes.
John
"cbrichards" wrote:
> I have the following query that needs to be rewritten (if possible) so tha
t
> the clustered index on column "coldate" can be utilized. Any suggestions
> would be appreciated. I realize that getting the functions off the column
is
> the goal, but I am not seeing how that might be done in this instance. Tha
nks
> in advance.
> SELECT *
> FROM
> table t
> WHERE
> DateDiff(n, DateAdd(n, t.col1 - 1, t.coldate), @.enddate) <= 0
> --
> Message posted via http://www.droptable.com
>
Function Suppressing Index
the clustered index on column "coldate" can be utilized. Any suggestions
would be appreciated. I realize that getting the functions off the column is
the goal, but I am not seeing how that might be done in this instance. Thanks
in advance.
SELECT *
FROM
table t
WHERE
DateDiff(n, DateAdd(n, t.col1 - 1, t.coldate), @.enddate) <= 0
--
Message posted via http://www.sqlmonster.comthis looks like homework.
you really can't because you are simultaneoulsy doing a calc on t.col1
for every row.
you could probably increase performance though by getting rid of
datediff, and just comparing the dates of hte dateadd results vs
@.enddate.|||Hi
If you don't need to have the date as a date then you may want to store
coldate as a offset for the number of minutes.
John
"cbrichards" wrote:
> I have the following query that needs to be rewritten (if possible) so that
> the clustered index on column "coldate" can be utilized. Any suggestions
> would be appreciated. I realize that getting the functions off the column is
> the goal, but I am not seeing how that might be done in this instance. Thanks
> in advance.
> SELECT *
> FROM
> table t
> WHERE
> DateDiff(n, DateAdd(n, t.col1 - 1, t.coldate), @.enddate) <= 0
> --
> Message posted via http://www.sqlmonster.com
>
Friday, March 9, 2012
function index
Thank,
dcExplain more clearly what you want to do.|||You have to create a computed column with the function you want indexed, then index the computed column. Then you have to change all of your applications to query the new column. Simple, eh?
In short, Function Based Indexes don't really exist in SQL Server, but they are pretty nice things to have.
create index ind on table (upper(name))|||Function based indicies (where the indexed value is the result of one or more function calls) were the most dearly missed feature of Clipper when I converted to SQL Server. They are lovely things when you don't have to deal with most of the complexities that a real relational database engine is intended to solve!
Unfortunately, they don't scale, introduce all kinds of "functional dependancies", lead to really interesting collation questions, etc. A Function Based Index is certainly convenient in many ways, but it is a scaling/supportability nightmare in search of its next victim!
-PatP
Wednesday, March 7, 2012
function based index
Such as:
create index INDEX_NAME ON TABLE_NAME (FLOOR(DECIMAL_READING));
Not directly.
But if your function is deterministic, you can create a computed column:
ALTER TABLE YourTable
ADD YourComputedColumn AS FLOOR(DECIMAL_READING);
... and then you can index it:
CREATE INDEX ComputedColumnIndex
ON YourTable(YourComputedColumn)
This won't quite be the same as a function-based index, as the index won't
be used for a query like:
SELECT *
FROM YourTable
WHERE FLOOR(DECIMAL_READING) = 10
... but you don't need to use the function at all, you can just do:
SELECT *
FROM YourTable
WHERE YourComputedColumn = 10
... And that _will_ use the index.
"Bevo" <Bevo@.discussions.microsoft.com> wrote in message
news:865521E8-FBD9-47B5-969F-2441334E9ED7@.microsoft.com...
> Does SQL Server 2000 support function-based index?
> Such as:
> create index INDEX_NAME ON TABLE_NAME (FLOOR(DECIMAL_READING));
|||Bevo wrote:
> Does SQL Server 2000 support function-based index?
> Such as:
> create index INDEX_NAME ON TABLE_NAME (FLOOR(DECIMAL_READING));
You can create an index on a computed column, but I'm not sure you can
specify functions in the create index statement.
See the section on "Considerations when indexing computed columns and
views" in the "CREATE INDEX" page of BOL.
So you could do something like this:
create table #test (col1 char(10), col2 as left(col1, 4))
create index test2 on #test(col2)
David G.
|||Thanks for the post, but, unfortunately I cannot modify the table structure.
"Adam Machanic" wrote:
> Not directly.
> But if your function is deterministic, you can create a computed column:
> ALTER TABLE YourTable
> ADD YourComputedColumn AS FLOOR(DECIMAL_READING);
> ... and then you can index it:
> CREATE INDEX ComputedColumnIndex
> ON YourTable(YourComputedColumn)
> This won't quite be the same as a function-based index, as the index won't
> be used for a query like:
> SELECT *
> FROM YourTable
> WHERE FLOOR(DECIMAL_READING) = 10
> ... but you don't need to use the function at all, you can just do:
> SELECT *
> FROM YourTable
> WHERE YourComputedColumn = 10
> ... And that _will_ use the index.
>
> "Bevo" <Bevo@.discussions.microsoft.com> wrote in message
> news:865521E8-FBD9-47B5-969F-2441334E9ED7@.microsoft.com...
>
>
|||You might also create an indexed view and do the same thing... Search for
indexed views in books online.
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Bevo" <Bevo@.discussions.microsoft.com> wrote in message
news:865521E8-FBD9-47B5-969F-2441334E9ED7@.microsoft.com...
> Does SQL Server 2000 support function-based index?
> Such as:
> create index INDEX_NAME ON TABLE_NAME (FLOOR(DECIMAL_READING));
function based index
Such as:
create index INDEX_NAME ON TABLE_NAME (FLOOR(DECIMAL_READING));Not directly.
But if your function is deterministic, you can create a computed column:
ALTER TABLE YourTable
ADD YourComputedColumn AS FLOOR(DECIMAL_READING);
... and then you can index it:
CREATE INDEX ComputedColumnIndex
ON YourTable(YourComputedColumn)
This won't quite be the same as a function-based index, as the index won't
be used for a query like:
SELECT *
FROM YourTable
WHERE FLOOR(DECIMAL_READING) = 10
... but you don't need to use the function at all, you can just do:
SELECT *
FROM YourTable
WHERE YourComputedColumn = 10
... And that _will_ use the index.
"Bevo" <Bevo@.discussions.microsoft.com> wrote in message
news:865521E8-FBD9-47B5-969F-2441334E9ED7@.microsoft.com...
> Does SQL Server 2000 support function-based index?
> Such as:
> create index INDEX_NAME ON TABLE_NAME (FLOOR(DECIMAL_READING));|||Bevo wrote:
> Does SQL Server 2000 support function-based index?
> Such as:
> create index INDEX_NAME ON TABLE_NAME (FLOOR(DECIMAL_READING));
You can create an index on a computed column, but I'm not sure you can
specify functions in the create index statement.
See the section on "Considerations when indexing computed columns and
views" in the "CREATE INDEX" page of BOL.
So you could do something like this:
create table #test (col1 char(10), col2 as left(col1, 4))
create index test2 on #test(col2)
David G.|||Thanks for the post, but, unfortunately I cannot modify the table structure.
"Adam Machanic" wrote:
> Not directly.
> But if your function is deterministic, you can create a computed column:
> ALTER TABLE YourTable
> ADD YourComputedColumn AS FLOOR(DECIMAL_READING);
> ... and then you can index it:
> CREATE INDEX ComputedColumnIndex
> ON YourTable(YourComputedColumn)
> This won't quite be the same as a function-based index, as the index won't
> be used for a query like:
> SELECT *
> FROM YourTable
> WHERE FLOOR(DECIMAL_READING) = 10
> ... but you don't need to use the function at all, you can just do:
> SELECT *
> FROM YourTable
> WHERE YourComputedColumn = 10
> ... And that _will_ use the index.
>
> "Bevo" <Bevo@.discussions.microsoft.com> wrote in message
> news:865521E8-FBD9-47B5-969F-2441334E9ED7@.microsoft.com...
>
>|||You might also create an indexed view and do the same thing... Search for
indexed views in books online.
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Bevo" <Bevo@.discussions.microsoft.com> wrote in message
news:865521E8-FBD9-47B5-969F-2441334E9ED7@.microsoft.com...
> Does SQL Server 2000 support function-based index?
> Such as:
> create index INDEX_NAME ON TABLE_NAME (FLOOR(DECIMAL_READING));
function based index
Such as:
create index INDEX_NAME ON TABLE_NAME (FLOOR(DECIMAL_READING));Not directly.
But if your function is deterministic, you can create a computed column:
ALTER TABLE YourTable
ADD YourComputedColumn AS FLOOR(DECIMAL_READING);
... and then you can index it:
CREATE INDEX ComputedColumnIndex
ON YourTable(YourComputedColumn)
This won't quite be the same as a function-based index, as the index won't
be used for a query like:
SELECT *
FROM YourTable
WHERE FLOOR(DECIMAL_READING) = 10
... but you don't need to use the function at all, you can just do:
SELECT *
FROM YourTable
WHERE YourComputedColumn = 10
... And that _will_ use the index.
"Bevo" <Bevo@.discussions.microsoft.com> wrote in message
news:865521E8-FBD9-47B5-969F-2441334E9ED7@.microsoft.com...
> Does SQL Server 2000 support function-based index?
> Such as:
> create index INDEX_NAME ON TABLE_NAME (FLOOR(DECIMAL_READING));|||Bevo wrote:
> Does SQL Server 2000 support function-based index?
> Such as:
> create index INDEX_NAME ON TABLE_NAME (FLOOR(DECIMAL_READING));
You can create an index on a computed column, but I'm not sure you can
specify functions in the create index statement.
See the section on "Considerations when indexing computed columns and
views" in the "CREATE INDEX" page of BOL.
So you could do something like this:
create table #test (col1 char(10), col2 as left(col1, 4))
create index test2 on #test(col2)
David G.|||Thanks for the post, but, unfortunately I cannot modify the table structure.
"Adam Machanic" wrote:
> Not directly.
> But if your function is deterministic, you can create a computed column:
> ALTER TABLE YourTable
> ADD YourComputedColumn AS FLOOR(DECIMAL_READING);
> ... and then you can index it:
> CREATE INDEX ComputedColumnIndex
> ON YourTable(YourComputedColumn)
> This won't quite be the same as a function-based index, as the index won't
> be used for a query like:
> SELECT *
> FROM YourTable
> WHERE FLOOR(DECIMAL_READING) = 10
> ... but you don't need to use the function at all, you can just do:
> SELECT *
> FROM YourTable
> WHERE YourComputedColumn = 10
> ... And that _will_ use the index.
>
> "Bevo" <Bevo@.discussions.microsoft.com> wrote in message
> news:865521E8-FBD9-47B5-969F-2441334E9ED7@.microsoft.com...
> > Does SQL Server 2000 support function-based index?
> >
> > Such as:
> >
> > create index INDEX_NAME ON TABLE_NAME (FLOOR(DECIMAL_READING));
>
>|||You might also create an indexed view and do the same thing... Search for
indexed views in books online.
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Bevo" <Bevo@.discussions.microsoft.com> wrote in message
news:865521E8-FBD9-47B5-969F-2441334E9ED7@.microsoft.com...
> Does SQL Server 2000 support function-based index?
> Such as:
> create index INDEX_NAME ON TABLE_NAME (FLOOR(DECIMAL_READING));
Sunday, February 26, 2012
Full-text working sporadically
issues about once every couple of weeks. Normally, a search can be done (via
webpage) on a keyword - and it works fine. But there are times that no
keyword (or other options) searches will work. The only option that is
searchable is "records updated in the last x number of days". So it's like
the full-text searching 1/2 works.
I have ran the stored procedure to ensure FTS was installed properly.
The MSSearch server is running under the local system account (as it should
be per MS).
I have deleted and rebuilt it complete. I have tried to rebuild the index
via the wizard and by right-clicking on the Full-Text Catalog -- neither
fixed the issue.
I've ran the stored procedure to ensure it was enabled.
Anyone have any idea why my FTS will work completely one day and work "a
little" the next?
Thanks!
Key
Can you check the gatherer logs to see if they reveal anything?
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"KEY" <key.hammonds@.gmail.com> wrote in message
news:F6E6798B-FA95-4C20-BBE7-104CE05B4B80@.microsoft.com...
>I have full-text running on one database only. This index seems to have
> issues about once every couple of weeks. Normally, a search can be done
> (via
> webpage) on a keyword - and it works fine. But there are times that no
> keyword (or other options) searches will work. The only option that is
> searchable is "records updated in the last x number of days". So it's
> like
> the full-text searching 1/2 works.
> I have ran the stored procedure to ensure FTS was installed properly.
> The MSSearch server is running under the local system account (as it
> should
> be per MS).
> I have deleted and rebuilt it complete. I have tried to rebuild the index
> via the wizard and by right-clicking on the Full-Text Catalog -- neither
> fixed the issue.
> I've ran the stored procedure to ensure it was enabled.
> Anyone have any idea why my FTS will work completely one day and work "a
> little" the next?
> Thanks!
> Key
Full-text usage?
Is it appropriate to use a full-text index and query to search CustomerName and CustomerDBA type fields? I've been told that this type of searching is meant for searching documents, but it seems that Full-text would be useful for fuzzy searching and noise word removal also. Does it make sense to index a varchar field? Does it add performace to searching over native 'Like' queries?
Thanks.
Full-text searching is great when you have multiple words in a column and you'd like to search on those individual words, or if you need to search on different tenses/forms of a word, eliminate noise words, or thesaurus capabilities. So yes, there are benefits to using it on varchar columns. As of SQL Server 2005 your full-text catalogs/indexes are included in your backups and restores so you don't have to rebuild your full-text indexes if you restore the database.
If you are searching on the full value or first few letters of the varchar, then a 'regular' index on the column would likely provide faster performance .. and in SQL Server 2005 we even improved the string searching for LIKEs that start with a wildcard.
Don
Full-Text Search stops working
I have a single server with Win 2000 Server and SQL 2000 SP3a.
I have a database with 1 full-text index, and everything works fine.....
BUT!!
Suddenly the search stops working. There are no errors in the eventlog and
all services are running fine.
The only way I can start the search, is to reboot the server !!
Whats wrong !?!?
/Peter
are there any messages in the gatherer logs?
"Microsoft" <pemaz@.wmdata.dk> wrote in message
news:%23hGeHL7TFHA.2124@.TK2MSFTNGP14.phx.gbl...
> Hi There
> I have a single server with Win 2000 Server and SQL 2000 SP3a.
> I have a database with 1 full-text index, and everything works fine.....
> BUT!!
> Suddenly the search stops working. There are no errors in the eventlog and
> all services are running fine.
> The only way I can start the search, is to reboot the server !!
> Whats wrong !?!?
> /Peter
>
|||No, there are no errors in the gatherer logs.
The index is only populated once.
/Peter
Hilary Cotter wrote:
> 220 0 article <uItFbR#TFHA.2664@.TK2MSFTNGP15.phx.gbl>
> From: "Hilary Cotter" <hilary.cotter@.gmail.com>
> References: <#hGeHL7TFHA.2124@.TK2MSFTNGP14.phx.gbl>
> Subject: Re: Full-Text Search stops working
> Date: Tue, 3 May 2005 09:28:58 -0400
> Lines: 23
> X-Priority: 3
> X-MSMail-Priority: Normal
> X-Newsreader: Microsoft Outlook Express 6.00.2900.2180
> X-MIMEOLE: Produced By Microsoft MimeOLE V6.00.2900.2180
> X-RFC2646: Format=Flowed; Original
> Message-ID: <uItFbR#TFHA.2664@.TK2MSFTNGP15.phx.gbl>
> Newsgroups: microsoft.public.sqlserver.fulltext
> NNTP-Posting-Host: ool-4353c4df.dyn.optonline.net 67.83.196.223
> Path: TK2MSFTNGP08.phx.gbl!TK2MSFTNGP15.phx.gbl
> Xref: TK2MSFTNGP08.phx.gbl microsoft.public.sqlserver.fulltext:14771
>
> are there any messages in the gatherer logs?
> "Microsoft" <pemaz@.wmdata.dk> wrote in message
> news:%23hGeHL7TFHA.2124@.TK2MSFTNGP14.phx.gbl...
>
|||I have checked the gather logs and theres nothing "fishy"
5/3/2005 9:22:16 AM Add The gatherer has started
5/3/2005 9:22:18 AM Add The initialization has completed
5/3/2005 12:40:48 PM Add The gatherer has started
5/3/2005 12:40:50 PM Add The initialization has completed
5/3/2005 1:13:10 PM Add The gatherer has started
5/3/2005 1:13:12 PM Add The initialization has completed
Peter Mazzarella wrote:
> No, there are no errors in the gatherer logs.
> The index is only populated once.
> /Peter
>
>
>
>
>
> Hilary Cotter wrote:
>
|||What happens if you stop and start MSSearch after your search functionality
stops?
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Peter Mazzarella" <pemaz@.wmdata.dk> wrote in message
news:uB2oNUJUFHA.1040@.TK2MSFTNGP10.phx.gbl...
> No, there are no errors in the gatherer logs.
> The index is only populated once.
> /Peter
>
>
>
>
>
> Hilary Cotter wrote:
>
Friday, February 24, 2012
fulltext search in sql express 2005
I had problem when change database from sqlserver 2000 to sql express 2005.
The fulltext index does not create automatically.
Example:
i use sqldatasource to insert new name to my table.
and then i find it by query like this : select count(*) from mytable where contains(mycol,'newname')
the result is 0
but if i run query to start full index : exec sp_fulltext_catalog 'myfulltext','start_full' and run query
select count(*) from mytable where contains(mycol,'newname')
again. The result is 1.
So i alway run exec sp_fulltext_catalog 'myfulltext','start_full' after insert or update, delete to create fulltext index. When i use sql server 2000 , i didn't need do that, it automatic.
Pls help me !!! how to make fulltext index create auto in sql express 2005 .
You need to set "Change Tracking" to automatic.
The index update will not be instantaneous, but it will be automatic.
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=680500&SiteID=1
http://technet.microsoft.com/en-us/library/ms187317.aspx
fulltext search HTML problem
currently we use text field in table to store HTML, and we use fulltext index to search this field, but we can not always get right results, for example, given text - "<P><STRONG>Access High Resorts<BR>", I can not get result if I search for "Access" or "cce", but if fine if I search for "High" or 'Resor", since I use CONTAINS, and CONTAINS can not search for postfix.
I know I can create another image data type field, and seach will filter HTML tag, but it will take long time to do.
Is there a way I can search postfix or mid-field of word in fulltext search? any idea or suggestion?
Thanks in advancethe sample string should like this - "<P><STRONG>Access High Resorst<BR>"
Full-text search does not automatically update index when CHANGE_TRACKING AUTO
On Sql Server 2005 Standard if I insert a new row into SomeTable that has a full-text index:
insert SomeTable(keywords)values('this is a test')
and then query:
SELECT * FROM Message WHERE Contains(keywords, ' "test" ');
I get the expected rows all rows that have "test" in the keyword.
On Sql Server 2005 Express new rows are not returned unless I rebuild the catalog.
Is this a known limitation of Express?
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
CREATE TABLE [dbo].[SomeTable](
[key] [int] IDENTITY(1,1) NOT NULL,
[keywords] [nvarchar](255) NOT NULL
CONSTRAINT [PK_SomeTable] PRIMARY KEY CLUSTERED
CREATE FULLTEXT CATALOG ft AS DEFAULT;
CREATE FULLTEXT INDEX ON [dbo].[SomeTable] KEY INDEX [PK_SomeTable] ON [ft] WITH CHANGE_TRACKING AUTO
ALTER FULLTEXT INDEX ON [dbo].[SomeTable] ADD ([keywords])
ALTER FULLTEXT INDEX ON [dbo].[SomeTable] ENABLE
Full-Text search and database view
search a table and not a view; i.e. you cannot create a full-text index for
a view.
1) Is there a way to full-text search a view?
2) If not, what could I be using the search data in the database using a
view which is better (faster) than a "LIKE "?
Thanks...
Denis.
In SQL 2005 you can full text index and search views.
Currently in SQL 2000 you can't full text index views, so you will have to
materialize the view as a table, i.e. create a table which has the same
columns as a view and use replication or triggers to keep this table in sync
with the base table. Then FTI this table.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Denis Blondeau" <denisblondeau@.hotmail.com> wrote in message
news:uaa144dGFHA.400@.TK2MSFTNGP14.phx.gbl...
> My understanding from full-text search (with SQL 2000) is that you can
only
> search a table and not a view; i.e. you cannot create a full-text index
for
> a view.
> 1) Is there a way to full-text search a view?
> 2) If not, what could I be using the search data in the database using a
> view which is better (faster) than a "LIKE "?
> Thanks...
> Denis.
>
|||Denis,
Yes, your understanding is correct for SQL Server 2000.
1) Is there a way to full-text search a view?
A. You cannot FT Index the "contents" of a view, however, you can include
SQL FTS statements such as CONTAINS or FREETEXT in a view.
2) If not, what could I be using the search data in the database using a
view which is better (faster) than a "LIKE "?
A. Depending upon your requirements (and more info on this would be
helpful), and assuming that you want to use FTS to search either multiple
columns or multiple tables & column in one SQL FTS statement, you may want
to checkout "SQL Server FTS across multiple tables or columns" at
http://spaces.msn.com/members/jtkane/Blog/cns!1pWDBCiDX1uvH5ATJmNCVLPQ!316.entry
If you have further questions on how to enhance SQL FTS, feel free to leave
a comment!
Thanks,
John
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/
"Denis Blondeau" <denisblondeau@.hotmail.com> wrote in message
news:uaa144dGFHA.400@.TK2MSFTNGP14.phx.gbl...
> My understanding from full-text search (with SQL 2000) is that you can
only
> search a table and not a view; i.e. you cannot create a full-text index
for
> a view.
> 1) Is there a way to full-text search a view?
> 2) If not, what could I be using the search data in the database using a
> view which is better (faster) than a "LIKE "?
> Thanks...
> Denis.
>
|||Thanks for your help, John (and Hilary)!
"John Kane" <jt-kane@.comcast.net> wrote in message
news:OttF9zhGFHA.2524@.TK2MSFTNGP15.phx.gbl...
> Denis,
> Yes, your understanding is correct for SQL Server 2000.
> 1) Is there a way to full-text search a view?
> A. You cannot FT Index the "contents" of a view, however, you can include
> SQL FTS statements such as CONTAINS or FREETEXT in a view.
> 2) If not, what could I be using the search data in the database using a
> view which is better (faster) than a "LIKE "?
> A. Depending upon your requirements (and more info on this would be
> helpful), and assuming that you want to use FTS to search either multiple
> columns or multiple tables & column in one SQL FTS statement, you may want
> to checkout "SQL Server FTS across multiple tables or columns" at
>
http://spaces.msn.com/members/jtkane/Blog/cns!1pWDBCiDX1uvH5ATJmNCVLPQ!316.entry
> If you have further questions on how to enhance SQL FTS, feel free to
leave
> a comment!
> Thanks,
> John
> --
> SQL Full Text Search Blog
> http://spaces.msn.com/members/jtkane/
>
> "Denis Blondeau" <denisblondeau@.hotmail.com> wrote in message
> news:uaa144dGFHA.400@.TK2MSFTNGP14.phx.gbl...
> only
> for
>
Sunday, February 19, 2012
Full-Text Search
I am trying to create a full text index on a column in a table and the data type is Image, but the TYPE COLUMN FILE EXTENSION is in another table. there is no way of creating the type in the same table. the wizard will not allow me to pick the data type field from another table. is there a work around this issue?
Emadkb
Adding a column to the table is not an option?|||It is a fram work table and making any changes now would be impossible. Do you see any other solutions to this issue. I really appreciate your quick response. Emadkb|||If you are using SQL 2005, you can make an indexed view and build fulltext index on the view. However that do means you dup the storage for the image columns.|||Thank you so much....I was wishing there is another solution. emadkb|||hey Feng Tian
I hope you could help me in FullText Search. No one else seems to be knowing about Fulltext Search.
I'm able to create Full text indexing catalogs, populate them etc.. when i login directly from the server using Sql Server Enterprise manager but when I try to use the same login (db owner) remotely from some other machine using SQL Server Enterprise Manager, I see disabled(stopped) Full-text Service and all full-text features disabled. So does it mean i cannot populate or create catalogs remotely using sql server enterprise manager.
I have tried this on two different servers and then accessing from two different remote/client machines. But the problem is there any way.
Server: Mssql 2000 server
Remote/Client system: Win XP service Pack2
Access Software: Microsoft Sql Server 2000 Enterprise Manager.
Thanks
Sattva
Sattva,
The behievor you are experiencing with your SQL Server 2000 Enterprise Manager (EM) is typical and expected. The EM cannnot "see remote" services such as the "Microsoft Search" (MSSearch) service on remote servers when accessed across the internet or the server is registered as an IP address. However, SQL FTS is still functional and you can use the system stored procedures to remotely manage and populate the FT Catalogs, for example:
To Create/Remove the Existing Full-Text Table Index, Catalog
-- If Full-Text Index exists, DROP that Index,
-- If Full-Text Index does not exist, CREATE that Index.
--
use pubs
go
IF OBJECTPROPERTY ( object_id('pub_info'),'TableHasActiveFulltextIndex') = 1
BEGIN
print 'Table pub_info is Full-Text Enabled, dropping Full-Text Index & Catalog...'
EXEC sp_fulltext_table 'pub_info', 'drop'
EXEC sp_fulltext_catalog 'PubInfo', 'drop'
END
ELSE IF OBJECTPROPERTY ( object_id('pub_info'),'TableHasActiveFulltextIndex') = 0
BEGIN
print 'Table pub_info is NOT Full-Text Enabled, creating FT Catalog, Index & Activating...'
EXEC sp_fulltext_catalog 'PubInfo', 'create'
EXEC sp_fulltext_table 'pub_info', 'create', 'PubInfo', 'UPKCL_pubinfo'
EXEC sp_fulltext_column 'pub_info', 'pub_id', 'add'
EXEC sp_fulltext_column 'pub_info', 'pr_info', 'add'
EXEC sp_fulltext_table 'pub_info', 'activate'
END
EXEC sp_fulltext_catalog 'PubInfo', 'start_full' -- "Full Crawl"
Wait for crawl to complete
--
Confirm above results with:
--
SELECT pub_id, pr_info
FROM pub_info WHERE CONTAINS(pr_info, '"book*"')
See my blog entry "SQL Server 2000 Full-Text Search Resources and Links" for more SQL 2000 FTS information.
Hope that helps,
John
SQL Full Text Search Blog
http://jtkane.spaces.live.com/
Thanks John. It was a great help. Now I hav it all working.
Cheers mate.
Fulltext query fails with "The content index is corrupt." on sql server 2005
I am afraid you will need to rebuild your entire index. There is no such option as 'repair'.
Please, feel free to contact me letting me know which operations toke place in order to have this corrupted status, as it is not common indeed.
Thanks.
Fulltext query fails with "The content index is corrupt." on sql server 2005
I am afraid you will need to rebuild your entire index. There is no such option as 'repair'.
Please, feel free to contact me letting me know which operations toke place in order to have this corrupted status, as it is not common indeed.
Thanks.
Full-text query (Freetexttable) returning duplicate rows
We have a query that uses the Full-text index on a view that's returning duplicate rows. We thought maybe it was the way we were joining, but we were able to simplify the query as much as possible and it still happens. Here's the query:
Code Snippet
SELECT *
FROM FREETEXTTABLE(vwSubtable, TitleSearch, 'Across five Aprils') AS KEY_TBL
ORDER BY RANK DESC
vwSubtable is an indexed view that contains some of the columns in our original table, and is also filtering out some rows from the main table in a where clause. There are no joins in the view.
This seems like it's about as simple a query as we could get. It will return some rows twice (ie. the same primary key row is returned back as two separate rows in the resultset). This is a problem since we're filling a datagrid, which is throwing a ConcurrencyException because the primary key is already in there.
I made sure we have SP2 installed on my SQL Server. Any ideas on what might be happening?
What you do get when you use LIKE.
Code Snippet
SELECT *
FROM vwSubtable
WHERE TitleSearch LIKE '%Across five Aprils%'
If you get dupes then take a closer look at your data.
|||My guess is that your fulltext catalog has become slightly corrupt maybe due to a large amount of inserts/deletes/updates to the underlying table.
Have you rebuilt your catalog recently? I'd schedule a rebuild of the catalog which should sort out the problem.
HTH!
|||Thanks! Rebuilding the catalog fixed the problem.Full-text query (Freetexttable) returning duplicate rows
We have a query that uses the Full-text index on a view that's returning duplicate rows. We thought maybe it was the way we were joining, but we were able to simplify the query as much as possible and it still happens. Here's the query:
Code Snippet
SELECT *
FROM FREETEXTTABLE(vwSubtable, TitleSearch, 'Across five Aprils') AS KEY_TBL
ORDER BY RANK DESC
vwSubtable is an indexed view that contains some of the columns in our original table, and is also filtering out some rows from the main table in a where clause. There are no joins in the view.
This seems like it's about as simple a query as we could get. It will return some rows twice (ie. the same primary key row is returned back as two separate rows in the resultset). This is a problem since we're filling a datagrid, which is throwing a ConcurrencyException because the primary key is already in there.
I made sure we have SP2 installed on my SQL Server. Any ideas on what might be happening?
What you do get when you use LIKE.
Code Snippet
SELECT *
FROM vwSubtable
WHERE TitleSearch LIKE '%Across five Aprils%'
If you get dupes then take a closer look at your data.
|||My guess is that your fulltext catalog has become slightly corrupt maybe due to a large amount of inserts/deletes/updates to the underlying table.
Have you rebuilt your catalog recently? I'd schedule a rebuild of the catalog which should sort out the problem.
HTH!
|||Thanks! Rebuilding the catalog fixed the problem.Full-text query (Freetexttable) returning duplicate rows
We have a query that uses the Full-text index on a view that's returning duplicate rows. We thought maybe it was the way we were joining, but we were able to simplify the query as much as possible and it still happens. Here's the query:
Code Snippet
SELECT *
FROM FREETEXTTABLE(vwSubtable, TitleSearch, 'Across five Aprils') AS KEY_TBL
ORDER BY RANK DESC
vwSubtable is an indexed view that contains some of the columns in our original table, and is also filtering out some rows from the main table in a where clause. There are no joins in the view.
This seems like it's about as simple a query as we could get. It will return some rows twice (ie. the same primary key row is returned back as two separate rows in the resultset). This is a problem since we're filling a datagrid, which is throwing a ConcurrencyException because the primary key is already in there.
I made sure we have SP2 installed on my SQL Server. Any ideas on what might be happening?
What you do get when you use LIKE.
Code Snippet
SELECT *
FROM vwSubtable
WHERE TitleSearch LIKE '%Across five Aprils%'
If you get dupes then take a closer look at your data.
|||My guess is that your fulltext catalog has become slightly corrupt maybe due to a large amount of inserts/deletes/updates to the underlying table.
Have you rebuilt your catalog recently? I'd schedule a rebuild of the catalog which should sort out the problem.
HTH!
|||Thanks! Rebuilding the catalog fixed the problem.Full-Text population problems
I'm getting the following error when I attempt to populate our Full Text Index on SQL
2000: The crawl seed in project SQL0000500005> cannot be accessed. Error: 800705b4 - This operation returned because
the timeout period expired. .
The crawl for project could not be started,
because no crawl seeds could be accessed. Fix the errors and try the crawl again.
I have seen in other threads and on the MS site how this is caused by the MSSQLService and the MS Search services not being syncronized and it can be fixed by changing the MSSQLService account to use the system account and back again.
The BUILTIN\Administrators group is present in SQL and has OWNER permissions. I even tried giving the SYSTEM group full access to the FTDATA folder.
This did NOT work for me. I am still getting the messages. One thing that is probably unique to my instalation is that it resides on the only server on this network so it is also the primary domain controler. Could this be why I cannot get the FT population to work?
I have another machine on a seperate network that is not a PDC and FT-Indexing works great on it but that doesn't help me for the network I need it to work on.
Does anyone have any other ideas I could try? I have been working on this for close to a week now.
The "Mav"How about the SQL Services account privileges and which accout is used?
Check what is the current state of process for indexing.
Refer to this
KBA (http://support.microsoft.com/default.aspx?scid=KB;en-us;q317746) to resovle the issue.|||I looked through that document and I have tried all that still no dice. The MS Search service is using the Local System account and the MSSQLServer service is using the local server administrator account. I tried changing this to the Local System acount using SQL Server Enterprise Manager(SSEM) this did not work so I changed it back to the local administrator account, once again using the SSEM.
Any other suggestions?
Thanks,
The Mav|||No one has any ideas? =(