Backing Up

As it can take a long time to create the full text index once the database
is restored I think it is a good idea to back up the full text catalog. What
is the best method for doing do ?
GMG,
Assuming you're using either SQL Server 7.0 or SQL Server 2000, you should
review KB article: 240867 (Q240867) "INF: How to Move, Copy, and Backup
Full-Text Catalog Folders and Files" at
http://support.microsoft.com/default...b;EN-US;240867
If you're using SQL Server 2005 (codename Yukon), the FT Catalog can be
backed up and restored with the database!
Regards,
John
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/
"GMG" <nospam@.nospam.com> wrote in message
news:uDysw71tFHA.908@.tk2msftngp13.phx.gbl...
> As it can take a long time to create the full text index once the database
> is restored I think it is a good idea to back up the full text catalog.
> What
> is the best method for doing do ?
>
sql

Avoid index scan with LIKE and a variable

Hi,
Here's my problem: I want to write a stored procedure that returns all
records from a table that have a certain column starting with given
text. I however find that using LIKE and a variable always causes an
index scan... which is causing performance issues. My table has about
3.5M records.

Below is a test. In query analyser if I look at the execution plan for
the following it will come up as in index scan. However, if i just
hard-code the text it all works fine (index seek).

How can I do this with reasonable speed?
Thanks Greg

DECLARE @.find varchar(50)
SET @.find = 'start'

SELECT TOP 100
*
FROM Test
WHERE
Col1 LIKE @.find + '%'
--Col1 LIKE 'start%'gregbacchus (greg.bacchus@.gmail.com) writes:
> Here's my problem: I want to write a stored procedure that returns all
> records from a table that have a certain column starting with given
> text. I however find that using LIKE and a variable always causes an
> index scan... which is causing performance issues. My table has about
> 3.5M records.
> Below is a test. In query analyser if I look at the execution plan for
> the following it will come up as in index scan. However, if i just
> hard-code the text it all works fine (index seek).
> How can I do this with reasonable speed?
> Thanks Greg
>
> DECLARE @.find varchar(50)
> SET @.find = 'start'
> SELECT TOP 100
> *
> FROM Test
> WHERE
> Col1 LIKE @.find + '%'
> --Col1 LIKE 'start%'

You don't say whether the index on Test.Col1 is clustered or not, and
whether this is the index that is scanned. I would expect that the index
on Test.Col1 is non-clustered, and the scan you see is a clustered index
scan.

When you have the literal, SQL Server knows about the query than you
have the variable. For the variable, SQL Server can only make a standard
assumption. Had the variable instead been a parameter to a stored procedure,
SQL Server would have looked at that value.

The best way out may be to simply use an index hint. You could also use
sp_executesql and pass the variable as a parameter.

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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Avoid duplicate index while creating temp table

We are using SQL 2K with sp4.
In our stored procedure, we create a temp table (i.e. #tablename) and
various indexes within the temp table. If mutliple users execute this store
d
procedure at the same time, it would cause duplicate index error. As a
temporary fix, we attach an unique number to the index name. I like to know
what is the best way to solve this problem without extra work?
wingmanI posted a similar query yesterday ( in which I completely mispelled
temporary);
http://groups.google.co.uk/group/mi...b23b0ffd6?hl=en
the basic response was that the indexes can co-exist without naming
conflicts
Cheers
Will|||Wingman,
You can read about this in the BOL. They must be unique within a table or
view but do not need to be unique within a database. Each temporary table
(not global ones ##) is unique. SQL Server create a unique name per user.
AMB
"Wingman" wrote:

> We are using SQL 2K with sp4.
> In our stored procedure, we create a temp table (i.e. #tablename) and
> various indexes within the temp table. If mutliple users execute this sto
red
> procedure at the same time, it would cause duplicate index error. As a
> temporary fix, we attach an unique number to the index name. I like to kn
ow
> what is the best way to solve this problem without extra work?
> wingman|||dude, step back a bit, and think about a solution where you don't have
multipel users accessing the same temp table.
this has GOT to lead to integrity issues.