|
As I've understood, it's not possible to have full-text search indexing on columns larger than 900 bytes. I know I can overcome this problem by adding large columns as non-key included columns to non-clustered indexes. But still I'm not able to use the powerful search capabilities of full-text search. I can only use queries like "select c from t where c like '%find%'". That's, I can not easily look for combination of words. Is there any known solution for searching large fields that can not be easily indexed in SQL Server? As an example, how has it been implemented to search multiple words in codeproject forums? Is it something simple like this?
SELECT * FROM Messages WHERE Title LIKE '%word1%' OR Title LIKE '%word2%'
Thanks in advance for any help.
|
|
|
|
|
Full-text index doesn't have a limit of 900 bytes on indexed columns. The limit is only for key index. So if you have a good primary key, you should not have any problems using full-text indexes.
The need to optimize rises from a bad design
|
|
|
|
|
What do you mean by key index Mika? Primary clustered index? Should I necessarily add a column as a non-key included column to a non-clustered index if I want to have full-text search on it?
Thanks
|
|
|
|
|
Not necessarily clustered index, but primary key or unique key of the table.
See KEY INDEX argument in CREATE FULLTEXT INDEX (Transact-SQL)[^]
The need to optimize rises from a bad design
|
|
|
|
|
Thank you. So there should be no problem. I don't know why somebody told me it's not possible to use it on fields larger than 900 bytes! Maybe I misunderstood the point.
_
modified on Sunday, October 5, 2008 4:47 PM
|
|
|
|
|
i need to know which is the best about from dataset and coding database connection by your self.i need brief description who is better on performance.
|
|
|
|
|
Peformance in database access should be more or less the same, because basically you will have the same queries in both scenarios.
But at client side, a designed dataset will produce lots of souce code, not all really needed. I think a manually created database access will produce far less code than the designed one.
The advantage of the dataset is that is easier to mantain, add and modify the queries and views.
Best regards,
Jaime.
|
|
|
|
|
Hi all,
I am migrating a C++ application to C#. One of the classes can read/write DBF (dBase) files.
The question is:
Does it worth to migrate this class?
What are all the ready-made .net alternatives?
Do these alternatives have some deployment requirements?
Thanks in advance.
Best regards,
Jaime.
|
|
|
|
|
Only thing that comes in mind is an ODBC-driver, which can be used with OdbcConnection Class. I think Microsoft may have free drivers against dBase.
The need to optimize rises from a bad design
|
|
|
|
|
Yeah, I know about the ODBC alternative, but:
* Is it always pre-installed in common client Windows OSs? (XP home/pro, Vista home/pro) ?
* If not, how easy or complex is to configure it when deploying an application that need to use DBF files?
Best regards,
Jaime.
|
|
|
|
|
Don't know if it's pre-installed. I would believe that not. I think that MDAC installs also dBase drivers, but I'm not sure.
The installation of a single driver is basically copying the dll files and then registering the dll's. After that a DSN can be created programmatically or using Control Panel.
The need to optimize rises from a bad design
|
|
|
|
|
Recently I installed MDAC to have a look to an old VB6 application. It causes me some troubles while installing.
So, I think maybe would be a good idea to use my own DBF class, so I won't have deployment problems...
Best regards,
Jaime.
|
|
|
|
|
Hi , i need all dates of the current month . m using sql server 2000 .
Thanx in Advance .
|
|
|
|
|
Thanx i got it . And the sol is :
declare @current varchar(100)
set @current =(select convert(varchar,getdate(),101))
declare @firstdate varchar(100)
declare @lastdate varchar(100)
set @firstdate=(SELECT convert( varchar,DATEADD(dd,-(DAY(DATEADD(mm,1,@current))-1),DATEADD(mm,0,@current)),101))
set @lastdate=(SELECT convert(varchar, DATEADD(dd, -DAY(DATEADD(m,1,@current)), DATEADD(m,1,@current)),101))
print @firstdate
print @lastdate
declare @i int
declare @k varchar(100)
select @i = datediff(dd,@firstdate,@lastdate)
print @i
declare @j int
set @j=0
while(@j<@i)
begin
select @k = dateadd( dd,1,@firstdate)
print @k
set @firstdate=@k
set @j =@j + 1
end
|
|
|
|
|
I have a table named test in SQL 2005 database. It has three columns (ourKey, ourData1, ourData2). Its first column (ourKey) is primary key column and it updates itself. It has one entry (1,45,56).
I would like to have another entry based on any other entry. I would like to insert entry which should produce (2,46,56)
I would like to read first entry from ourData1 then add one and rest of the table should be same.
Any help will be very much appreciated.
in previous thread
Re: delete duplicate rows by Ashfield
#table is used. What is #table?
Agha Khan
modified on Friday, October 3, 2008 11:42 PM
|
|
|
|
|
Do yo mean something like:
INSERT INTO test (ourData1, ourData2) SELECT MAX(ourData1) + 1, MAX(ourData2) FROM test;
Tables prefixed with # are local temporary tables.
The need to optimize rises from a bad design
|
|
|
|
|
Thank you. but I was looking to copy of Nth row not the Max of colum. i.e. I want to copy 3rd row or 2nd row.
My table has three entries.
1,45,30
2,3,40
3,9,80
the 4th should be if 2 row (2,3,40) is selected
4,3,40
if 1st row (1,45,30) is selected
4,46,30
This means answer depends on selection.
But your answer helped me to understand.
this works
INSERT INTO test (ourData1, ourData2) SELECT (select ourData1 from test where ourkey = 3 ) + 1 , (select ourData2 from test where ourkey = 3 );
but is there short syntex? 
I tried something like this
Select * into #table from test where ourkey = 3
select * from #table
update #table set ourData1 = ourData1 + 1
insert into test select * from #table
But I cant not insert identity column, it is my primey key and it is default. I not have to supply it.
Otherwise it looks it works.
Kind regards
Agha
modified on Saturday, October 4, 2008 11:05 AM
|
|
|
|
|
If I understood you correctly, you could modify the insert statement to following:
INSERT INTO test (ourData1, ourData2)
SELECT ourData1 + 1 , ourData2
FROM test
WHERE ourkey = 3 ;
The need to optimize rises from a bad design
|
|
|
|
|
This works fine.
But that was just an example. In real application I have many columns, so I tried with #table, which works fine, but unable to update primey key column. Please check previous reply.
Kind regards
Agha
I tried something like this
Select * into #table from test where ourkey = 3
select * from #table
update #table set ourData1 = ourData1 + 1
insert into test select * from #table
unable to update primey key column
modified on Saturday, October 4, 2008 11:28 AM
|
|
|
|
|
Yes, since the primary key is autogenerated, you cannot insert into it or update it. Can't you list all of the columns instead of * and leave the primary key out? Like:
INSERT INTO Test (NotKeyColumn1, NotKeyColumn2, ...)
SELECT NotKeyColumn1, NotKeyColumn2, ... FROM #table
An alternative could be that you drop the key column from temp table before insert. Something like:
...
UPDATE #table SET ourData1 = ourData1 + 1
ALTER TABLE #table DROP COLUMN KeyColumn
INSERT INTO test SELECT NULL, * FROM #table
However, I'm not sure if it works. I added NULL to the beginning, because if you do not list the target columns in insert, you must supply a value for all columns (in the order they appear in the target table).
The need to optimize rises from a bad design
|
|
|
|
|
Thank you.
There is no need for adding null,
but it works very well.
Thank you again.
Kind regards
Agha
|
|
|
|
|
Thank you.
It works.
Best regards
Agha
|
|
|
|
|
You're welcome.
The need to optimize rises from a bad design
|
|
|
|
|
Can any one tell the query to delete only duplicate rows from any table ?
|
|
|
|
|
You need to do it in steps:
Select * into #table from mytable where 1 = 2
insert into #table select distinct * from mytable
truncate table mytable
insert into mytable select * from #table
Bob
Ashfield Consultants Ltd
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
