i use two containstable (one of them with 'formsof') and one freetexttable attributes in a select command and i want to combine them with the logical 'or'.
can i do this in the same command? what is the syntax?
my code :
select table1.field1,a.rank,b.rank,c.rank
from table1
containstable(table1,field,'"word"') as a
or containstable(table1,field,'formsof(inflectional(word)') as b)
or freetexttable(table1,field,'word') as c
where table1.id=a.[key] and table1.id=b.[key] and table1.id=c.[key]
the above syntax is wrong. i tried ',' instead of 'or' but the results were not right.
Can somebody help me in identifying the difference between FREETEXTABLE and CONTAINSTABLE statements?
I have recently started experimenting with the free text search and I feel that FREETEXTTABLE can look for the search string into multiple columns by breaking it into tokens.
For example
select ft_tbl.saon, ft_tbl.paon, ft_tbl.street, ft_tbl.postcode ,key_tbl.rank from temp as ft_tbl INNER JOIN freetextTABLE(temp, (saon, paon, street), '80 ridge avenue', 15) as key_tbl ON FT_tbl.ID = key_tbl.[key]
Now in my case €œ80€? is in PAON column and €œRidge Avenue€? is in STREET column. Among the resulting rows it also displays the required row. If I try to achieve this with CONTAINSTABLE using
select ft_tbl.saon, ft_tbl.paon, ft_tbl.street, ft_tbl.postcode ,key_tbl.rank from temp as ft_tbl INNER JOIN containsTABLE(temp, (saon, paon, street, postcode), '80 ridge avenue') as key_tbl ON FT_tbl.ID = key_tbl.[key]
It gives me error Syntax error near 'ridge' in the full-text search condition '80 ridge avenue'.
If I make search string as €˜€?80 ridge avenue€?€™ instead of €˜ridge avenue€™ then it doesn€™t give me error but also displays no results because 80 ridge avenue does not appear as whole in any single column. If I search by only specifying €œridge avenue€? in search string then I get a full list where street is ridge avenue.
My question is Does FREETEXTTABLE can search in all listed column by breaking the search string into tokens and CONTAINSTABLE only in one as whole?
I am using FREETEXTTABLE to enable users to search forum posts. It works extremely well (and fast!) and I am very happy with it.
I also have a top ten list of 'Most viewed' forum posts. Each time a post is accessed, a 'viewcount' value is incremented accordingly. The ten forum posts with the highest viewcount appear in the top ten list (by order of viewcount, of course).
Now... when a user searches on a search term, I would like the top ten list to also be affected by this search.
For example, the user searches on 'foo'. In response they get a list of results containing 'foo'. However, the top ten list also changes to reflect this. It now shows the ten forum posts with the highest viewcount which also contain the word 'foo'.
This is easy enough to do. The problem arises when we also add proximity searches.
For example, if I search on 'foo bar', FREETEXTTABLE will look for records which have both or either of these words. Records which contain both words will be ranked higher than records which contain either word. And of the records which contain both words, the closer together the two words are, the higher the ranking will be.
But how does this now work with the top ten list? For example, I may have two records:
- Record A has been viewed 10 times. It contains the words 'foo' and 'bar' and they are next to each other. - Record B has been viewed 10,000 times. But it only contains the word 'foo'.
Now, according to 'viewcount', record B should be ranked higher. But according to FREETEXTTABLE, record A should be ranked higher.
So, I need to combine both these rankings in some way. Regardless of how the 'top ten' records are chosen, they will be ordered by viewcount. But I'm not sure how to get the right top ten records in the first place.
I'm trying to build a search function using FREETEXTTABLE to provide rankings of the results. I've got it working on one of the tables using a query that looks something like this: SELECT DISTINCT title, recordid, rank FROM mn_records INNER JOIN FREETEXTTABLE(mn_records,*, 'sony') AS ft ON mn_records.recordid = ft.[key] ORDER BY rank DESC
The problem is that I want to search two tables mn_records and mn_items. A record has multiple items linked to it and some of the fields in the items table are different for the same record. I have the functionality working using FREETEXT: SELECT DISTINCT title, recordid FROM mn_records, mn_items WHERE mn_records.recordid = mn_items.parentrecord AND (FREETEXT (mn_records.*, 'sony') OR FREETEXT (mn_items.*, 'sony'))
How can I properly join these two tables and the FREETEXTTABLE rank so that the search results will only contain distinct record_ids yet still search the information contained in mn_items?
I have used Full-Text Search in a stored procedure with English words whithout any problems..But when i use it with Arabic words it gives me THE Error:
Server: Msg 7619, Level 16, State 1, Procedure SearchProject, Line 19 Execution of a full-text operation failed. A clause of the query contained only ignored words.
The stored procedure :
CREATE PROCEDURE SearchProject ( @SearchString nvarchar(500), @CultureName nvarchar(50), @HowManyResults int OUTPUT ) AS CREATE TABLE #SearchTable ( FieldNO int, ProjectNO int, ProjectName nvarchar(200), ProjectDescription nvarchar(1000), ProjectImage nvarchar(1000), CultureID int )
INSERT INTO #SearchTable (FieldNO,ProjectNO,ProjectName,ProjectDescription,ProjectImage,CultureID) SELECT P.FieldNO, PL.ProjectNO,PL.ProjectName,PL.ProjectDescription,P.ProjectImage,PL.CultureID FROM FREETEXTTABLE(Project_Locale,*,@SearchString) AS FT JOIN Project_Locale AS PL ON FT.[KEY]=PL.ProjectCultureID JOIN Project AS P ON P.ProjectNO=PL.ProjectNO WHERE PL.CultureID=dbo.GetCultureID(@CultureName)
INSERT INTO #SearchTable (FieldNO,ProjectNO,ProjectName,ProjectDescription,ProjectImage,CultureID) SELECT P.FieldNO, PL.ProjectNO,PL.ProjectName,PL.ProjectDescription,P.ProjectImage,PL.CultureID FROM Project_Locale AS PL ,Project AS P, ProjectField_Locale AS FL, FREETEXTTABLE(ProjectField_Locale,*,@SearchString) AS FT2 WHERE FL.FieldNO=P.FieldNO AND FL.FieldCultureID=FT2.[KEY] AND PL.ProjectNO=P.ProjectNO AND PL.CultureID=dbo.GetCultureID(@CultureName) AND FL.CultureID=dbo.GetCultureID(@CultureName)
INSERT INTO #SearchTable (FieldNO,ProjectNO,ProjectName,ProjectDescription,ProjectImage,CultureID) SELECT P.FieldNO, PL.ProjectNO,PL.ProjectName,PL.ProjectDescription,P.ProjectImage,PL.CultureID FROM Project_Locale AS PL ,Project AS P, Feature_Locale AS PFEA, ProjectFeature AS FP, Feature AS F, FREETEXTTABLE(Feature_Locale,*,@SearchString) AS FT3 WHERE FT3.[KEY]=PFEA.FeatureCultureID AND FP.ProjectNO=P.ProjectNO AND FP.FeatureNO=PFEA.FeatureNO AND PL.ProjectNO=P.ProjectNO AND F.FeatureNO=PFEA.FeatureNO AND PFEA.CultureID=dbo.GetCultureID(@CultureName) AND PL.CultureID=dbo.GetCultureID(@CultureName)
SELECT @HowManyResults=COUNT(DISTINCT ProjectNO) FROM #SearchTable SELECT DISTINCT * FROM #SearchTable RETURN GO
I called the stored procedure using the following(in the Query Analayzer) :
USE nabeel1eagle DECLARE @HowManyResults int EXEC SearchProject 'ملÙ?ات','ar-SA',@HowManyResults OUTPUT
but when I use the previous code directly(without calling the stored procedure using EXEC) in the Query Analyzer it works fine.Like the following (part of the code)code:
SELECT P.FieldNO, PL.ProjectNO,PL.ProjectName,PL.ProjectDescription,P.ProjectImage,PL.CultureID FROM FREETEXTTABLE(Project_Locale,*,'ملÙ?ات') AS FT JOIN Project_Locale AS PL ON FT.[KEY]=PL.ProjectCultureID JOIN Project AS P ON P.ProjectNO=PL.ProjectNO WHERE PL.CultureID=dbo.GetCultureID('ar-SA')
Could any one help and tell me how to solve this problem?
I have defined full-text indexing on a table a couple of months ago and the catalog was fully populated, it was working fine until now. But suddenly has stopped working, I have been scheduling incremental population on a regular basis and also have tried to remove and redefined full-text indexing but can't get it to work.
We are using freetexttable as our search function in our application and it seems to be partially working. I search for a word from a column of a table I included in the search catalog and it sometimes pickup the record and sometimes it doesn't. I got 2 records having 'business' as the keyword. And when I search that keyword, it only returned 1 record.
here is the codesnippet: select * from freetexttable(<tablename>, *, 'business')
I have the following code on a database that works perfectly (i.e. searching for 'print' will return results that have 'printer' in the field):
***************************************
INSERT INTO searchResults (articleId, articleTitle, articleSummary, articleType, reviewedBy, reviewedDate, submitDate, modifiedDate, appTitle, appVersion, rank, hasAccess) SELECT id, articleTitle, articleSummary, articleType, reviewedBy, reviewedDate, submitDate, modifiedDate, appTitle, appVersion, k.rank, hasAccess FROM articles As a INNER JOIN FREETEXTTABLE(articles, articlesummary, 'print') AS K ON a.id = K.[KEY]
***************************************
I am working on a new DB trying to accomplish the same thing, so I have the following code:
***************************************
SELECT id, firstName, lastName, k.rank FROM Pagers_Users As a INNER JOIN FREETEXTTABLE(Pagers_Users, lastName, 'smith') AS K ON a.id = K.[KEY] ORDER BY k.rank DESC
***************************************
However, this second block does not completely work (as I see it). When I enter the word 'smith' to serch for, it only brings back exact matches, not close calls, like 'smithe'.
I have also tried searching for other words (or partial words) and it only returns exact matches.
Is there something in setting up the Text Index that I forgot to turn on for "close" matches?
I am building a simple full text search engine for my site and I was wondering how would I retrieve rows 11-20 of the search result. This is required because I want to show my results only 10 at a time, like google does for instance. My query is as follows -
select top 10 ft_tbl.url, ft_tbl.title, ft_tbl.body, ft_tbl.date, (key_tbl.rank) from mytable as ft_tbl inner join freetexttable(mytable, (url, title, body), '".$searchstring."', 10) as key_tbl on ft_tbl.id = key_tbl.[key] order by (key_tbl.rank) DESC
In MySQL I would use LIMIT but I believe that doesnt exist in MS SQL
I have n Tables: T_1, T_2, ... T_n that all have the same exact fields/columns. Ultimately, I want to search through ALL n tables and return a single table of relevant results from all tables, arranged in order by rank. I'm not sure if this is possible. So far, I have: 1 SELECT RANK, field_1, field_2, ..., field_m FROM 2 ( 3 SELECT RANK, field_1, field_2, ..., field_m FROM T_1, 4 CONTAINSTABLE(T_1, field_i,@searchText) searchTable 5 WHERE KEY = T_1.field_i 6 UNION 7 SELECT RANK, field_1, field_2, ..., field_m FROM T_2, 8 CONTAINSTABLE(T_2, field_i,@searchText) searchTable 9 WHERE KEY = T_2.field_i 10 UNION 11 . 12 . 13 . 14 UNION 15 SELECT RANK, field_1, field_2, ... field_m FROM T_n, 16 CONTAINSTABLE(T_n, field_i,@searchText) searchTable 17 WHERE KEY = T_n.field_i 18 ) 19 ORDER BY RANK DESC
I haven't tried it yet, but it seems wrong. How do we actually do this?
I would like to use LINQ to generate a sql statement that does not use LIKE, but rather uses CONTAINS. Is this possible? If not, my second question is whether or not I can parameterize a SqlCommand that uses CONTAINS. For example the following statement works just fine when I pass in the parameter via SqlCommand.Parameters.AddWithValue()SELECT * FROM [event] WHERE CONTAINS(comments, @searchTerm1)However, the following results in a variable not defined error.SELECT * FROM [event] WHERE (comments LIKE @searchTerm1)Any ideas? Thanks for your help.
I'm trying to write a query to associate to an alternate key. Cansomeone provide the right syntax/keyword I need to accomplish this?Here's the line that keeps giving me the error (Operand type clash:uniqueidentifier is incompatible with int):JOIN CONTAINSTABLE(VocabularyFrench, *, 'FORMSOF(INFLECTIONAL,"remorques")') SRCH ON P.PRODUCT_ID = SRCH.[KEY]Thanks,Jeff
Hi, I realised that I am not able to do a FREETEXT search on multiple table, example:SELECT * FROM [tStaffDir], [tStaffDir_ClientExp], [tStaffDir_CoreSpecs], [tStaffDir_GlobalExp], [tStaffDir_Lang], [tStaffDir_PrevEmp], [tStaffDir_TerEdu] WHERE FREETEXT(*, @Name) ORDER BY [Name] Can I use FREETEXTTABLE instead? How do I go about doing it?
Hi,I'm doing a search function for recipe database and have the query:1 SELECT K.RANK, tRecipe.sHeadline, tRecipe.sIngredients, tRecipe.sImagePath 2 FROM tRecipe 3 INNER JOIN 4 FaktaRecipe ON tRecipe.iRecipeID = FaktaRecipe.iRecipe 5 INNER JOIN 6 CONTAINSTABLE(tRecipe, *, 'ISABOUT (chick* WEIGHT(0.2))') AS K 7 ON tRecipe.iRecipeID = K.[KEY] 8 WHERE (FaktaRecipe.iRecipeFakta = 5) 9 ORDER BY RANK DESC I want to return records like 'chicken pie' etc, hence using the wildcard in chick* BUT the wildcard doesn't work! It works fine if I use the whole word 'chicken' but of course a user won't always do that... I am using SQL server 2000. Any ideas? - I'm tearing my hair out! Thanks,Paul
This works when @searchString is used in containstable (provided searchString has value)...
set @searchStringNoneOfWords = 'not(Airplane)' SET @searchString = @searchString + ' AND ' + @searchStringNoneOfWords
This does NOT work when @searchString is used in containstable...
set @searchStringNoneOfWords = 'not(Airplane)' SET @searchString = @searchStringNoneOfWords
I understand it is because the syntax is AND NOT, but what if I have a list of words that I do not want included? How do I start out with a NOT using containstable? It is kind of like Google's advanced search except that if you enter a word in the "without words" section with the other fields blank it would return everything under the sun except for things found with those words.
I am using the following query to search all columns in the 'dashboard'table for the value 'Meets':SELECT * from dashboard AS FT_TBL INNER JOIN CONTAINSTABLE(dashboard,*,'meets') AS KEY_TBL ON FT_TBL.employee = KEY_TBL.[KEY]I have multiple records that contain the word 'Meets', but none areshowing up as a result of this query. Any ideas?Also, anytime I use a space in my search condition (Meets Expectationsinstead of Meets) I am getting an error:Syntax error occurred near 'Expectations'. Expected ''''' in searchcondition 'Meets Expectations'.Any ideas?Thanks in advance.
I have a stored procedure that uses containstable and want to make it a little dynamic so I was going to add a parameter that consist of the column names that needed to be search. But when I add a variable I get an error saying incorrect syntax....
Can you not use a variable as a column list?? I have a variable for search criteria and it works fine...
Here is my syntax
containstable([tablename],@columnlist,@srch)
I have been looking online and can't seem to find anything that says I can or cannot use a variable.
I have an issue trying to pass a search text parameter to FREETEXTTABLE via Dataset. The following code works fine if you hardcode the search word/text as shown: SELECT KEY_TBL.RANK, FT_TBL.FaqQuestion, FT_TBL.FaqAnswer, FT_TBL.SearchFROM faq_table AS FT_TBL INNER JOIN FREETEXTTABLE(faq_table, Search, 'cool') AS KEY_TBL ON FT_TBL.FaqID = KEY_TBL.[KEY]ORDER BY KEY_TBL.RANK DESC Now, I want to do this: SELECT KEY_TBL.RANK, FT_TBL.FaqQuestion, FT_TBL.FaqAnswer, FT_TBL.SearchFROM faq_table AS FT_TBL INNER JOIN FREETEXTTABLE(faq_table, Search, @Search) AS KEY_TBL ON FT_TBL.FaqID = KEY_TBL.[KEY]ORDER BY KEY_TBL.RANK DESC The error I'm getting is @Search is not declared. How am I suppose to pass in a value? I have searched almost everywhere and nobody seemed to ask this precise question. I'm sure this is a huge problem. Can anyone help me please?
Is there a simple and direct way to perform a fulltext query in a table with multiple columns, and to use AND over multiple columns? I've noticed that AND only works within one column.
Example: Take a column 'lastname' with 'jones' and 'smith' in it. Another column 'firstname' with 'alan' where lastname=smith. Search for 'alan and smith', and no results are returned because they are in different columns. If you would have a lastname 'alan smith', that would be found.
I have a few questions related to using CONTAINSTABLE in a query that I hope someone can help with.
I am working on a project to add document search capabilities to my companies product using fulltext indexing. Part of this requirement is an ability to breakdown the component parts of of the search query and provide information on *why* documentX ranked higher than documentY. This is a bit convoluted, but taking this (very simple) example - the user wishes to search for 2 skills - "HTML" and/or "XML". The generated query looks a little like :-
Select DOC.DOC_ID, RANK1.RANK, RANK2.RANK, RANK3.RANK from DOCS DOC inner join CONTAINSTABLE(docs, doc, 'HTML AND XML') as RANK1 on RANK1.DOC_ID=DOC.DOC_ID inner join CONTAINSTABLE(docs, doc, 'HTML') as RANK2 on RANK2.DOC_ID=DOC.DOC_ID inner join CONTAINSTABLE(docs, doc, 'XML') as RANK3 on RANK3.DOC_ID=DOC.DOC_ID
This returns the "overall" rank, and a rank for the 2 component parts, so I can say this doc ranked XXX overall because it scored "rank1" for HTML and scored "rank2" for XML etc....
My question on this part is about the values for the "overall rank". If the query contained an OR it always seems to return the highest of the "rankX" values, and if it doesnt, it returns the lowest. e.g. for the example for java and word and excel and access - the overall ranking is 2 , java=36, word=2, excel=16 and access=36 for java and word or excel and access - the overall ranking is 16 , java=36, word=2, excel=16 and access=36 for (java and word) or (excel and access) - the overall ranking is 16 , java=36, word=2, excel=16 and access=36
So in the first example, regardless of what the other values are, the rank returned is always 2 (the score for "word"). My resultset has 100ish rows, all with a rank of < 5 for word, but all with ranks of 18-100 for the other 3 values - yet the "overall" rank always matched the "word" rank.....?? This doesnt feel right to me somehow, I would expect a different value as if the document ranked really highly for one value but low for the other, it doesnt feel right the value is clamped to the lowest? Or am I just understanding it wrong? If I use "freetexttable" the overall rank is a little more meaningful - but unfortunately I also need to use weighting, which brings me to my next question . . .
This question is about rankings returned from the ISABOUT function. In the following example, select * from documents as DOC inner join containstable(docs,doc,'project') as doc0 on DOC.DOC_ID=doc0."key" inner join containstable(docs,doc,'ISABOUT (project weight (1.0))') as doc1 on DOC.DOC_ID=doc1."key" inner join containstable(docs,doc,'ISABOUT (project weight (0.5))') as doc2 on DOC.DOC_ID=doc2."key" inner join containstable(docs,doc,'ISABOUT (project weight (0.1))') as doc3 on DOC.DOC_ID=doc3."key" inner join containstable(docs,doc,'ISABOUT (project weight (0.0))') as doc4 on DOC.DOC_ID=doc4."key" order by doc0.rank desc
The values I get from the doc1/2/3/4.RANK columns dont seem right. In this example,
doc0.rank = 133 doc1.rank = 150 doc2.rank = 330
doc3.rank = 924
doc4.rank = 0
These values dont make any sense to me, as the rank seems to go UP when the documentation on ISABOUT says it goes down (I think it says somewhere the calculated rank is multiplied by the weight?). Once again, is there something I missed or am I understanding it wrong?
Thanks in advance for any help into understanding the whys of this...
This works when @searchString is used in containstable (provided searchString has value)...
set @searchStringNoneOfWords = 'not(Airplane)' SET @searchString = @searchString + ' AND ' + @searchStringNoneOfWords
This does NOT work when @searchString is used in containstable...
set @searchStringNoneOfWords = 'not(Airplane)' SET @searchString = @searchStringNoneOfWords
I understand it is because the syntax is AND NOT, but what if I have a list of words that I do not want included? How do I start out with a NOT using containstable? It is kind of like Google's advanced search except that if you enter a word in the "without words" section with the other fields blank it would return everything under the sun except for things found with those words.
I am using the ContainsTable function to search a database from my (c#) app. This works relatively well and all fields of the table are indexed and searched. That is, any column, but per record only one column. What I mean is this: when searching for "chris 2007", I want to retrieve all items where author contains chris and year contains 2007. Currently, a search for chris brings up all items where author (or any other field) contains chris, a search for 2007 works as well, but chris 2007 fails as there is no -one- field where chris and 2007 are located. Can anybody help me achieve this? My code is:
Code Block SELECT FT_TBL.ID, FT_TBL.Type, FT_TBL.Author, IsNull(FT_TBL.Author, FT_TBL.Editor + ' (Ed.)') AS CorrectedAuthor, FT_TBL.Editor, FT_TBL.Title, FT_TBL.Abstract, FT_TBL.Comments, FT_TBL.Year, FT_TBL.City, FT_TBL.Publisher, FT_TBL.ISBN, FT_TBL.Pages, FT_TBL.Journal, FT_TBL.Issue, FT_TBL.Hyperlink, FT_TBL.Tags, KEY_TBL.RANK FROM Sources AS FT_TBL INNER JOIN CONTAINSTABLE(Sources, *, @searchQuery) AS KEY_TBL ON FT_TBL.ID = KEY_TBL.[KEY] ORDER BY KEY_TBL.RANK DESC;
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?
I am trying to do a freetext filter with mutiple columns using a column list, but I can't get the syntax down for multiple column list. First, am I am going about this the right way...Do I need to be doing both? Second why doesn't mutiple columns work. I can't find any good samples online. What I am trying to accomplish is a refined search stored procedure that uses the freetext to do the search refinement. Any help would be appreciated.
select
b.rank,
a.ProductID,
a.ProductName,
a.Sequence,
a.ProductImage,
a.ItemID,
a.ItemName,
a.ManufacturerItemCode,
a.ItemImage,
a.ItemSourceID,
a.PackageID,
a.BrandID,
a.BrandName,
a.ManufacturerID,
a.ManufacturerName,
a.ProductCategoryID,
a.CategoryID,
a.CategoryName,
d.CustomerGroupName,
isnull(h.PackageDescription,a.ItemPKG) as PKG,
case g.StockStatus
when 1 then 'Yes'
when 0 then 'No'
else ''
end as StockStatus,
isnull(g.StandardUnitPrice,a.ListPrice) as Price,
isnull(j.SupplierAbbreviation,a.ManufacturerAbbreviation) as ItemSource
from
dbo.vw_mcProductItem a
inner join freetexttable(dbo.vw_mcProductItem, (ProductName,ItemName,ManufacturerItemCode,ItemPKG,BrandName,ManufacturerName,ManufacturerAbbreviation,CategoryName), @SearchWord) as b ON a.ItemID = b.[KEY]
inner join [dbo].[mcCustomerGroupItem] c on c.ItemID = a.ItemID
inner join [dbo].[mcCustomerGroup] d on d.CustomerGroupID = c.CustomerGroupID
inner join [dbo].[mcCustomerGroupCustomer] e on e.CustomerGroupID = d.CustomerGroupID
inner join [dbo].[mcCustomerUser] f on f.CustomerID = e.CustomerID
left outer join [dbo].[mcSupplierItem] g on g.ItemID = a.ItemID
left outer join [dbo].[mcPackage] h on h.PackageID = g.SellingPackageID
left outer join [dbo].[mcItemSource] i on i.ItemSourceId = a.ItemSourceId
left outer join [dbo].[mcSupplier] j on j.SupplierID = g.SupplierID
where
d.CustomerGroupID = @CustomerGroupID
and f.UserID = @UserID
and FREETEXT(BrandName,ManufacturerName,CategoryName, @SearchWord)
Please have a look at the following two queries, the purpose of which is to find which ten users (represented by 'Username') have created the most records which contain the term 'foo':
SELECT TOP 10 Username, COUNT(*) AS [Count] FROM Options
WHERE FREETEXT(*, 'foo')
GROUP BY Username
ORDER BY [Count] DESC
SELECT TOP 10 Username, COUNT(*) AS [Count] FROM Options
JOIN FREETEXTTABLE (Options, *, 'foo', 500) ct
ON OptionID = ct.[KEY]
GROUP BY Username
ORDER BY [Count] DESC
They both produce the same result set. However, I am wondering which is more performant. At first glance, it would seem the first one would be. It doesn't involve a JOIN and should, therefore, be more efficient.
But this depends on how the FREETEXT expression is evaluated. My concern is that internally, SQL Server would generate an entire recordset based on 'WHERE FREETEXT(*, 'foo')', which could be thousands of records, and only then restrict this to the TOP 10 by COUNT.
If this does happen, then it would be better to join to a FREETEXTTABLE, where I can at least restrict the result set using the 'top_n_by_rank' parameter (which is set as '500' in this case, as this seems a good balance of performance against the likely number of duplicates I will get in my FREETEXTTABLE results).
So... I am worrying about this unnecessarily? Should I just use the simpler first version?
I'm trying to use CONTAINSTABLE to do a product search on a number of columns. I want matches on some columns to be weighted higher than for other columns.
It seems like there should be a way to do this with CONTAINSTABLE - I know I can search by multiple strings and weight those matches variably, but what I want is to vary the weight by which column a single term matched. What I have now is a series of UNION queries, which are quite slow: =================== SELECT m.TitleCode, m.ShortName, m.ShortDescription, a.Rank, 50 as theWeight FROM CONTAINSTABLE (Product, ShortName, @theSearchTerm) as a, Product m(NoLock) WHERE a.[KEY] = m.TitleCode
UNION ALL
SELECT m.TitleCode, m.ShortName, m.ShortDescription, a.Rank, 40 as theWeight FROM FREETEXTTABLE (Product, ShortName, @theSearchTerm) as a, Product m(NoLock) WHERE a.[KEY] = m.TitleCode
UNION ALL
SELECT m.TitleCode, m.ShortName, m.ShortDescription, a.Rank, 30 as theWeight FROM FREETEXTTABLE (Product, MatchKeywords, @theSearchTerm) as a, Product m(NoLock) WHERE a.[KEY] = m.TitleCode
UNION ALL
SELECT m.TitleCode, m.ShortName, m.ShortDescription, a.Rank, 20 as theWeight FROM FREETEXTTABLE (Product, ShortDescription, @theSearchTerm) as a, Product m(NoLock) WHERE a.[KEY] = m.TitleCode
Actually I'm on the half way of making a portal and I get some problem I need your kindly helps. at first I use the membership feature of ASP.net 2.0 to have login and all so by default it has generate an ASPNETDB.MDF file which its is (Microsoft SQL Server Database File (SqlClient)) and also I have two more databases one for file managemnet and one for Calander and Contacts but I need all to be one so whenevr one user can login it can show his own file in his page but now everyone can see all,Can anybody guide me should it all be in one database and if yes how can I connect all since one is generated by default by Visual studio2005.Should I use a Microsoft SQL Server (SqlClient)??? Thanks in advance.
Hi all, I need some help in combining two results. I am using the Northwind Database and the Orders Table. The first select outputs the table shown below, Table 1 and the second select outputs the result in the second table Table 2. How can I combine these two to get the third table, Table 3 ? SELECT TOP 100 PERCENT EmployeeID, COUNT(ShipVia) AS CountShipVia1 FROM dbo.Orders WHERE (ShipVia = 1) GROUP BY EmployeeID ORDER BY EmployeeID
Table 1 Results EmployeeID CountShipVia1
1
82
2
71
3
81
4
116
5
29
6
48
7
44
8
75
9
29 SELECT TOP 100 PERCENT EmployeeID, COUNT(ShipVia) AS CountShipVia2 FROM dbo.Orders WHERE (ShipVia = 2) GROUP BY EmployeeID ORDER BY EmployeeID Table 2 results EmployeeID CountShipVia2
1
44
2
36
3
45
4
70
5
15
6
25
7
24
8
48
9
19 Table 3 the desired result: EmployeeID CountShipVia1 CountShipVia2
