best/easiest way to search a table in a database?

im pretty new to SQL and i'm just reading up on full-text searches... i need to do a a full-text search on one table i have in the database.. however I'm reading about full-text indexing/searching and a lot of pages are saying that it uses a lot of resources when searching.. i was wondering how bad is it really? we have about ~100 users who would access the database, probably the peak would be 75 at a time.. would people using a full-text searching slow it down a lot? the servr is a dell poweredge 1750 server, dual 2.8ghz xeon, 1 gig ram.. and also, about the incermental population, if i read right, it populates the catalog each time a item in the table is deleted/inserted/modified.. so would that use a lot of resources as well?

i'm just trying to see if it's worth it to enable full-text indexing for searches on the database if it doesn't slow down the server too much... or are there any better/easier ways to perform searches?

thanksIf the Text is of DataType "Char", then indexing may help u. But Indexing on VarChar Data usually does'nt help.|||The efficiency depends more upon the length and cardinality of the strings than on whether they are stored as CHAR or VARCHAR.|||Full text search is not limited to just CHAR and VARCHAR, and its strength is most appreciated when dealing with TEXT and IMAGE fields.

Check the following and drill down:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/architec/8_ar_cs_5tid.asp|||hi chauzer! we have the same scenario. we develop applications for government use and with the same concern as you do, server response is great. no slow or downtime that we experienced so far. blindman is right 'bout his statement. just want to share the experience...that's all|||thanks for the replies guys, just got it set up and see no slowdown at all and works great, thanks

Best way to move datafile from C drive to D

I obviously did not search the archives on the right terms so what is
the easiest and fastest way to move a 3G database from a nearly full C
drive to the nearly empty D drive that should have been used.

I could back it up, drop it, recreate it using the D drive, and restore
it but it seems like there should be a way to just move the datafile
and use if from the new location.

I am thinking that detatch/attach is the best method, but I would like
confirmation or suggestions on how to proceed or things to be aware of
when using this method.

-- Mark D Powell --the safest method is to do a backup and restore; however, you can
detach, move, and reattach (usually) with no problems. Of coruse,
before doing any operation such as this, you should take a backup first
(which is why the first method is recommended).

Stu

Mark D Powell wrote:

Quote:

Originally Posted by

I obviously did not search the archives on the right terms so what is
the easiest and fastest way to move a 3G database from a nearly full C
drive to the nearly empty D drive that should have been used.
>
I could back it up, drop it, recreate it using the D drive, and restore
it but it seems like there should be a way to just move the datafile
and use if from the new location.
>
I am thinking that detatch/attach is the best method, but I would like
confirmation or suggestions on how to proceed or things to be aware of
when using this method.
>
-- Mark D Powell --

|||Stu (stuart.ainsworth@.gmail.com) writes:

Quote:

Originally Posted by

the safest method is to do a backup and restore; however, you can
detach, move, and reattach (usually) with no problems. Of coruse,
before doing any operation such as this, you should take a backup first
(which is why the first method is recommended).

The advantage with detach/attach is that you don't have to wait for
the new data file to be initiated. For a 3GB database that's maybe 5-10
minutes on SQL 2000. SQL 2005 has some quick initiation under some circum-
stances.

--
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|||Erland Sommarskog wrote:

Quote:

Originally Posted by

Stu (stuart.ainsworth@.gmail.com) writes:

Quote:

Originally Posted by

the safest method is to do a backup and restore; however, you can
detach, move, and reattach (usually) with no problems. Of coruse,
before doing any operation such as this, you should take a backup first
(which is why the first method is recommended).

>
The advantage with detach/attach is that you don't have to wait for
the new data file to be initiated. For a 3GB database that's maybe 5-10
minutes on SQL 2000. SQL 2005 has some quick initiation under some circum-
stances.
>
--
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

OK, the plan is

1- that the customer is going to try to lock the end-users out of the
application
2- I will backup the database
3- I will detatch it
4- I will copy the files to the new location
5- I will attach the db
6- I will test accessing some data
7- the customer will open the application

In the event of serious problems I will drop the database, recreate it,
restore using the backup over the new version, and fix the user access.

Sounds easy enough.
-- Mark D Powell --|||Mark D Powell (Mark.Powell@.eds.com) writes:

Quote:

Originally Posted by

In the event of serious problems I will drop the database, recreate it,
restore using the backup over the new version, and fix the user access.

You don't have to create the database prior to restoring it. RESTORE will
do that for you.

--
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|||Erland Sommarskog wrote:

Quote:

Originally Posted by

Mark D Powell (Mark.Powell@.eds.com) writes:

Quote:

Originally Posted by

In the event of serious problems I will drop the database, recreate it,
restore using the backup over the new version, and fix the user access.

>
You don't have to create the database prior to restoring it. RESTORE will
do that for you.
>
--
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

I know a restore can recreate a database but since I want to change the
location of the datafiles will it in this case allow me to change them
on the screen where it shows the datafile names? I have changed them
when I overlaid a production database with a test (upgrade) version
that ran on the same server but I changed them to existing files for
the existing producion database that I wanted to overlay.

I should take a few minutes and try this on the test server.

Thanks -- Mark --|||You want to use the WITH MOVE option; check the BOL. However, detach,
move, and attach should work fine.

HTH,
Stu

Mark D Powell wrote:

Quote:

Originally Posted by

Erland Sommarskog wrote:

Quote:

Originally Posted by

Mark D Powell (Mark.Powell@.eds.com) writes:

Quote:

Originally Posted by

In the event of serious problems I will drop the database, recreate it,
restore using the backup over the new version, and fix the user access.

You don't have to create the database prior to restoring it. RESTORE will
do that for you.

--
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

>
I know a restore can recreate a database but since I want to change the
location of the datafiles will it in this case allow me to change them
on the screen where it shows the datafile names? I have changed them
when I overlaid a production database with a test (upgrade) version
that ran on the same server but I changed them to existing files for
the existing producion database that I wanted to overlay.
>
I should take a few minutes and try this on the test server.
>
Thanks -- Mark --

|||Mark D Powell (Mark.Powell@.eds.com) writes:

Quote:

Originally Posted by

I know a restore can recreate a database but since I want to change the
location of the datafiles will it in this case allow me to change them
on the screen where it shows the datafile names?

Don't know what the screen permits, and I don't want to engage in guessing
what it does.

The syntax is:

RESTORE DATABASE db FROM FILE = 'yourdump.bak',
WITH MOVE 'logicalfilename1' TO 'newlocation.mdf',
MOVE 'logicalfilename2' TO 'newlocation.ldf',
REPLACE

The logical file names talked about here, are those you see when you dp
sp_helpdb in the first column in the second result set. You can also
retrieve this with RESTORE FILELISTONLY.

The command above with create the database files, and then restore the
backup.

--
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|||Erland Sommarskog wrote:

Quote:

Originally Posted by

Mark D Powell (Mark.Powell@.eds.com) writes:

Quote:

Originally Posted by

I know a restore can recreate a database but since I want to change the
location of the datafiles will it in this case allow me to change them
on the screen where it shows the datafile names?

>
Don't know what the screen permits, and I don't want to engage in guessing
what it does.
>
The syntax is:
>
RESTORE DATABASE db FROM FILE = 'yourdump.bak',
WITH MOVE 'logicalfilename1' TO 'newlocation.mdf',
MOVE 'logicalfilename2' TO 'newlocation.ldf',
REPLACE
>
The logical file names talked about here, are those you see when you dp
sp_helpdb in the first column in the second result set. You can also
retrieve this with RESTORE FILELISTONLY.
>
The command above with create the database files, and then restore the
backup.
>
>
--
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

I used attach/detach and it seemed to work fine. The slow part was
moving the 1.2G and 3.5G datafiles to the new locations. I swear the
copies took 3X longer the making the backups I made before I tried the
detatch/attach.

Thanks to all
-- Mark D Powell --