best practices for rebuilding full text search

Hi
We are working with a sql server 2000 using full text search, which works
well so far. However, we are not sure which is the best way to ensure the
full text search is always working properly. As changes to the database
should be immediatly reflected in a search query, we have change tracking
enabled. But should we now rebuild the fulltext catalogue from time to time.
We are thinking about creating a job that runs every two months that
completly rebuilds the full text index.( exec sp_fulltext_catalog
N'OurFullTextCataloge', N'start_full' )
Would you suggest to do this or is having change-tracking enabled enough?
Thanks for any answer.
Greetings
Daniel
Having change tracking should be enough.
There should be no reason for you to rebuild other than adding or removing a
noise word list.
"Daniel Knpfel" <d.kn@.iph.ch> wrote in message
news:eXcSOhLjHHA.4772@.TK2MSFTNGP05.phx.gbl...
> Hi
> We are working with a sql server 2000 using full text search, which works
> well so far. However, we are not sure which is the best way to ensure the
> full text search is always working properly. As changes to the database
> should be immediatly reflected in a search query, we have change tracking
> enabled. But should we now rebuild the fulltext catalogue from time to
> time. We are thinking about creating a job that runs every two months that
> completly rebuilds the full text index.( exec sp_fulltext_catalog
> N'OurFullTextCataloge', N'start_full' )
> Would you suggest to do this or is having change-tracking enabled enough?
> Thanks for any answer.
> Greetings
> Daniel
>
>
sql

Best practice for storing long text fields

As we all know, there is a 8060 bytes size limit on SQL Server rows. I
have a table which requires a number of text fields (5 or 6). Each of
these text fields should support a max of 4000 characters. We currently
store the data in varchar columns, which worked fine untill our
appetite for text fields increased to the current requirement of 5, 6
fields of 4000 characters size. I am given to review a design, which
esentially suggests moving the text columns to a separate TextFields
table. The TextFields table will have two columns - a unique reference
and a VARCHAR (4000) column, thus allowing us to crossreference with
the original record. My first impresion is that I'd rather use the SQL
Server 'text' DB type instead, which would allow me the same
functionality with much less effort and possibly better performance.
Can anyone advise on advantages and disadvantages of the two options
and what the best practice in this case would be.
Any advise will be well appreciated.
TzankoTzanko wrote:

Quote:

Originally Posted by

As we all know, there is a 8060 bytes size limit on SQL Server rows. I
have a table which requires a number of text fields (5 or 6). Each of
these text fields should support a max of 4000 characters. We currently
store the data in varchar columns, which worked fine untill our
appetite for text fields increased to the current requirement of 5, 6
fields of 4000 characters size. I am given to review a design, which
esentially suggests moving the text columns to a separate TextFields
table. The TextFields table will have two columns - a unique reference
and a VARCHAR (4000) column, thus allowing us to crossreference with
the original record. My first impresion is that I'd rather use the SQL
Server 'text' DB type instead, which would allow me the same
functionality with much less effort and possibly better performance.
Can anyone advise on advantages and disadvantages of the two options
and what the best practice in this case would be.

I hear that VARCHAR(MAX) is the new TEXT, but it's only available
in SQL 2005.|||Tzanko (tzanko.tzanev@.strategicthought.com) writes:

Quote:

Originally Posted by

As we all know, there is a 8060 bytes size limit on SQL Server rows.

Yes, in SQL 2000. Not in SQL 2005. There a row can span pages.

Quote:

Originally Posted by

I have a table which requires a number of text fields (5 or 6).

Do these text fields hold the same text that spans fields, or are
they different texts?

Quote:

Originally Posted by

I am given to review a design, which esentially suggests moving the text
columns to a separate TextFields table. The TextFields table will have
two columns - a unique reference and a VARCHAR (4000) column, thus
allowing us to crossreference with the original record.

If they are different texts they should be in different columns, or you
should have some type column telling them apatt.

Quote:

Originally Posted by

My first impresion is that I'd rather use the SQL Server 'text' DB type
instead, which would allow me the same functionality with much less
effort and possibly better performance.

Yes, if they the column are all the same text, this might be the way
to go. You can store up to 2GB in a text column.

But better performance? Nah. If nothing else, text is difficult to
work with and there are lot of limitations. As Ed mention, SQL 2005
comes with varchar(MAX) which also can fit 2GB, but which you can
work with in the same way as a regular varchar.

If the columns are different texts, I see little point to use the
text data type.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Are you saying that in SQL 2000 you can Span VarChar's into multiple columns
automatically? If so how?

Cheers, @.sh

"Erland Sommarskog" <esquel@.sommarskog.sewrote in message
news:Xns983CEED4849AEYazorman@.127.0.0.1...

Quote:

Originally Posted by

Tzanko (tzanko.tzanev@.strategicthought.com) writes:

Quote:

Originally Posted by

>As we all know, there is a 8060 bytes size limit on SQL Server rows.

>
Yes, in SQL 2000. Not in SQL 2005. There a row can span pages.
>

Quote:

Originally Posted by

>I have a table which requires a number of text fields (5 or 6).

>
Do these text fields hold the same text that spans fields, or are
they different texts?
>

Quote:

Originally Posted by

>I am given to review a design, which esentially suggests moving the text
>columns to a separate TextFields table. The TextFields table will have
>two columns - a unique reference and a VARCHAR (4000) column, thus
>allowing us to crossreference with the original record.

>
If they are different texts they should be in different columns, or you
should have some type column telling them apatt.
>

Quote:

Originally Posted by

>My first impresion is that I'd rather use the SQL Server 'text' DB type
>instead, which would allow me the same functionality with much less
>effort and possibly better performance.

>
Yes, if they the column are all the same text, this might be the way
to go. You can store up to 2GB in a text column.
>
But better performance? Nah. If nothing else, text is difficult to
work with and there are lot of limitations. As Ed mention, SQL 2005
comes with varchar(MAX) which also can fit 2GB, but which you can
work with in the same way as a regular varchar.
>
If the columns are different texts, I see little point to use the
text data type.
>
>
>
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
>
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

|||@.sh (spam@.spam.com) writes:

Quote:

Originally Posted by

Are you saying that in SQL 2000 you can Span VarChar's into multiple
columns automatically? If so how?

No. What I said is that on SQL 2005 a row can span pages, so that you can
have more than 8060 bytes per row.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Cool!

"Erland Sommarskog" <esquel@.sommarskog.sewrote in message
news:Xns983D9BE59C16AYazorman@.127.0.0.1...

Quote:

Originally Posted by

@.sh (spam@.spam.com) writes:

Quote:

Originally Posted by

>Are you saying that in SQL 2000 you can Span VarChar's into multiple
>columns automatically? If so how?

>
No. What I said is that on SQL 2005 a row can span pages, so that you can
have more than 8060 bytes per row.
>
>
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
>
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

|||Many thnaks for your replies.

Just to clarify the issue:
The requirement is to create a table that has say 6 columns which store
strings (such as Description, Notes, etc.) Each of these 6 columns
should store a char string of max length of 4000 characters. The
problem is that SQL Server 2000 will not work if I simply defined the
columns as varchar(4000) as at some point the row size reaches the page
size of 8060 and this generates an error. There is a 8060 bytes limit
on SQL Server 2000 rows. Note that I am not trying to store the same
string into 6 different columns spanning from column to column. I have
a separate string to store in each column.

The question:
What is the best way to implement this in SQL Server 2000. In
particular I am looking at two options: Setting each of the 6 columns
to be of type 'text'. Looking at the documentation, it appears that
this would behave for as long as each string is not longer than 4000
characters and I am happy to have this limit. It however is unpleasant
to use the text type for longer than 4000 char strings, as in this case
I understand there are some specific ways of handling the data. Option
two is to create a new LongStrings table with 2 columns - long unique
number and varchar(4000). Each string is stored in this LongStrings
table and is crosreferenced (by using the unique ID) with its original
cell in its original table. Now I'd preffer option 1 (provided I do not
have to do anything special to handle the strings) and would like to
avoid option 2 because it is not easy to write queries to get the data.

Second question is what is the situation with SQL Server 2005. I
understand I can simply define the columns as varchar(max) and do not
have to do anything special. Has someone used this successfully and can
you confirm it ste case?

Thanks for your help.

Tzanko

@.sh wrote:

Quote:

Originally Posted by

Cool!
>
>
"Erland Sommarskog" <esquel@.sommarskog.sewrote in message
news:Xns983D9BE59C16AYazorman@.127.0.0.1...

Quote:

Originally Posted by

@.sh (spam@.spam.com) writes:

Quote:

Originally Posted by

Are you saying that in SQL 2000 you can Span VarChar's into multiple
columns automatically? If so how?

No. What I said is that on SQL 2005 a row can span pages, so that you can
have more than 8060 bytes per row.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

|||Tzanko (tzanko.tzanev@.strategicthought.com) writes:

Quote:

Originally Posted by

The question:
What is the best way to implement this in SQL Server 2000. In
particular I am looking at two options: Setting each of the 6 columns
to be of type 'text'. Looking at the documentation, it appears that
this would behave for as long as each string is not longer than 4000
characters and I am happy to have this limit. It however is unpleasant
to use the text type for longer than 4000 char strings, as in this case
I understand there are some specific ways of handling the data. Option
two is to create a new LongStrings table with 2 columns - long unique
number and varchar(4000). Each string is stored in this LongStrings
table and is crosreferenced (by using the unique ID) with its original
cell in its original table. Now I'd preffer option 1 (provided I do not
have to do anything special to handle the strings) and would like to
avoid option 2 because it is not easy to write queries to get the data.

The best in my opinion is to create two or three new tables and rename
the existing tbable, and the create a view that unifies them all. Then in
SQL 2005 you can scrap the view, and move the columns back to the mother
table. Very litte code would actually be affected.

If the key of the table is (cola, colb) the new tables should also have
the keys (cola, colb). Simply, what you do is that you split the columns
over several tables.

You should consider text or varchar(max) if you really need to fit more
than 8000 characters.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Best Practice for storing Blog text

Hi..

I am working on creating a blog for my site(not yet ready). Here's the idea --

I will have an 'Add a blog' page where it has a textarea and a submit button. I will write the blog in this textarea and hit submit. It will be added to the database and I will retrieve it in the actual blog page and bind it to a datalist. The blogtext even has the <br><font> tags etc... but then i'll hv to reframe the keywords with [keyword] I guess..

Is this the correct practice? Is storing blogtext in a SQL db a good idea..? Is there a better way of doing the same?

Thanks.

Sure, storing the blog data in SQL Server database is a usual thing, like the blog module in DNN. But anyway, beside of doing this funny thing, perhaps there is already (I assume so) a blog module out there for free with the source code which can be downloaded (with source code) and can be customized by yourself.

HTH, Jens Suessmeyer.

|||

Sure, storing the blog data in SQL Server database is a usual thing, like the blog module in DNN. But anyway, beside of doing this funny thing, perhaps there is already (I assume so) a blog module out there for free with the source code which can be downloaded (with source code) and can be customized by yourself.

HTH, Jens Suessmeyer.

|||

Thank you for the info. I have googled around a bit but found nothing.. hmmm.. if you happen to have a link, then lemme know.

Thanks.

|||

Did you have a look in www.sourceforge.net ? Once searched I found some entries, perhaps dasBlog: http://swik.net/dasBlog/SourceForge.net%3A+Project+File+Releases%3A+newtelligence+dasBlog+Community+Edition/dasblogce+dasBlog+1.8.5223.2+released+%28Mon%2C+07+Nov+2005+14%3A53%3A25+GMT%29/thr

HTH, jens Suessmeyer.

best practice for ragged delimited text import

I have tab delimited text files which may have optional fields (meaning they can be not present at all) to the right of the required fields that I care about. It would appear that using a Flat File Connection with Delimited Format (tab) set will choke if it is initially configured with a file that has something like:

data\tdata\tdata\r\n

and it then encounters

data\tdata\tdata\toptionaldata\r\n

It chokes. I know this could be parsed line by line, but that seems silly. It seems like there should be a way to ignore columns beyond a certain point (e.g. Format "Delimited Ragged Right").

Is there some way to do this with a directly with a flat file connection?

Thanks,

--Andrew

If you are guaranteed to have at least one more column than the required ones, you can configure the columns you care about with the tab delimiter, and then a final column with the CR/LF delimiter that will get the rest of the row.

If not (and it looks like you can't, from your example), then you'll have to configure the last required column with the CR/LF delimiter. That way the last column will hold the last value that you want, and the rest of the row, if it exists. Then use a Derived Column transform with the FINDSTRING and SUBSTRING functions to get everything up to the tab.

Sorry, but I don't think there is an easier solution.

Best match text search

Does the new text search method in SQL Server provide a “best match” text
search? Does it work similar to a Google search, for example, where the most
words that match gets the highest relevancy score, then these should appear
first. I wondered if the new text search provided a accuracy/relevancy score
on the results?
Help i appreciated.
Thanks
Message posted via droptable.com
http://www.droptable.com/Uwe/Forums.aspx/sql-server-search/200702/1
Yes, the freetext predicate is most similar to the way searches are
conducted in Google.
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
"ARPREET via droptable.com" <u31535@.uwe> wrote in message
news:6db8ff6e9d053@.uwe...
> Does the new text search method in SQL Server provide a "best match" text
> search? Does it work similar to a Google search, for example, where the
> most
> words that match gets the highest relevancy score, then these should
> appear
> first. I wondered if the new text search provided a accuracy/relevancy
> score
> on the results?
> Help i appreciated.
> Thanks
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forums.aspx/sql-server-search/200702/1
>

Beginner Help Wanted With PL/SQL

Hi I was wondering if anybody could help me with this problem:

Step 1: Create a table to store text strings entered by a database user. When a text string is entered to the database, various information about the entry should be recorded including:
A unique identifier for the entry that will be the primary key. You must set the primary key using a constraint
The actual text string entered by the user
The name of the database user that entered the text string
The date that the string was entered

Step 2: Create a sequence that starts off with an initial value of 100 and increments by 10

Step 3: Declare a PL/SQL block that:
Declares four appropriately named variables using anchored datatypes that match each column in the table defined in Step 1
Prompts the user to enter a text string
Assign the value entered by the user to the variable defined to store the string entered
Assign the date, user and next value in the sequence to the other variable defined previously. Note that you should use the SELECT INTO FROM DUAL method of assignment
Within the PL/SQL Block, insert the information assigned to the variables into the table defined in Step 1Which specific part are you having trouble with? Clearly you are not asking us to do all your homework for you!|||Homework? Yeah hold on I have to go ask my mammy if I can use the computer!|||Originally posted by iknownothing
Homework? Yeah hold on I have to go ask my mammy if I can use the computer!
Don't forget to say "please"!|||Jaysus you're hilarious!!|||Originally posted by iknownothing
Jaysus you're hilarious!!
You are too kind. But seriously, is there any specific help you want with your "homework" ("class assignment", call it what you will), or did you just want someone to do it all for you? You will find that people round here are very helpful if you are prepared to put in some of the effort yourself. On the other hand, people are less inclined to help when it appears that someone just wants to pass off someone else's effort as their own.

So: what have you come up with so far, and where are you stuck?|||Well, you are already using a cursor-based record! :-

student_val c_student%ROWTYPE;

i.e. the record type is defined in terms of the cursor c_student.

The table-based cursor would be student%ROWTYPE.

If you use a cursor FOR loop you can get rid of the declaration altogether, along with a lot of other code:

SET SERVEROUTPUT ON;

DECLARE

CURSOR c_student IS
SELECT * FROM student;

begin

open c_student;

for student_val in c_student loop

DBMS_OUTPUT.PUT_LINE('Student Details: ' || student_val.salutation || student_val.first_name
|| student_val.last_name || student_val.phone || student_val.Registration_date );

end loop;

end;|||Yeah I figured it out later and deleted the post coz it was pointless but what I need to know now is how to change it from a cursor to a table based! Im in the process of trying but am getting nowhere!!

BCP's /F switch

We have a text file in which the first row has four fields and it needs
to get BCP'ed into a table with four columns. All remaining rows are
much longer with 75 fields and get BCP'ed into a table with 75 columns.
I am doing this second BCP execution with the /F2 option to indicate
that the first row should be row 2. Yet when I run it, it skips row 2
(the first of the longer rows) and starts grabbing at row 3. I have a
feeling BCP gets over that first short row and starts grabbing
at the second longer row (really row 3).
Any thoughts about what I could do? Thanks much.BCP is certainly going to have problems with that. We tend to think
of BCP first breaking the data into lines, then the lines into fields.
That isn't what it does, however. BCP goes field by field, and what
we call the line terminator is really just the field terminator of the
last field on the line. When you tell it to skip the first line, it
actually skips the first 75 fields. This takes it well into the
second line, and the 75th field will include everything to the end of
the second line.
I would start by looking for some string in the first line that does
not appear in any of the other lines, or which appears in all the
other lines that does not appear in the first. If something like that
can be found it may be possible to pre-process the data into two files
using the DOS (well not really DOS, the command line that looks like
DOS to us old fogeys) command FIND, and redirection. Something like:
find "some string" input.txt > withstring.txt
find /V "some string" input.txt > withOUTstring.txt
Otherwise, I would write a very simple program to read the file and
write it out to two files as you need. Or perhaps see if DTS parses
lines on a line-then-field basis.
Roy Harvey
Beacon Falls, CT
On Mon, 22 May 2006 17:17:53 -0400, Rick Charnes
<rickxyz--nospam.zyxcharnes@.thehartford.com> wrote:

>We have a text file in which the first row has four fields and it needs
>to get BCP'ed into a table with four columns. All remaining rows are
>much longer with 75 fields and get BCP'ed into a table with 75 columns.
>I am doing this second BCP execution with the /F2 option to indicate
>that the first row should be row 2. Yet when I run it, it skips row 2
>(the first of the longer rows) and starts grabbing at row 3. I have a
>feeling BCP gets over that first short row and starts grabbing
>at the second longer row (really row 3).
>Any thoughts about what I could do? Thanks much.|||Rick Charnes (rickxyz--nospam.zyxcharnes@.thehartford.com) writes:
> We have a text file in which the first row has four fields and it needs
> to get BCP'ed into a table with four columns. All remaining rows are
> much longer with 75 fields and get BCP'ed into a table with 75 columns.
> I am doing this second BCP execution with the /F2 option to indicate
> that the first row should be row 2. Yet when I run it, it skips row 2
> (the first of the longer rows) and starts grabbing at row 3. I have a
> feeling BCP gets over that first short row and starts grabbing
> at the second longer row (really row 3).
BCP ? BCP never gets , but users of BCP often gets
over it!
I think the best description of BCP is that it reads a *binary* file.
The file may look like a text file to you and me, for BCP it's binary.
When BCP reads the file, it looks the format definition of the first
field as specified by the format file or command-line switches. When
it has found the end of that column, it goes for the next etc. The
row terminator is just the terminator for the last field.
When you say -F2, that is not the second line in the file. It's the second
record in the file according to the format specification. Given your
description, I can guess that will be line 3 in the file. That first
skipped record has an embedded newline in field 4, and some embedded
field terminators in field 75.
As for what you want to do, you will have to split the file. BCP
is not smart enough to handle two tables or two different formats.
Or, hm, maybe you can get away with a single file - but requires
quite some luck. Getting that single line into a table should be
simple. Define a format file for those four fields, and then specify
-L 1, to get only the first row.
Skipping that first line may be more problematic. If you can post a
sample, I might be able to come with something. But no promises.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx