Partial Success

Log Explorer from Lumigent absolutely rocks. I was able to view the three
major delete transactions, generate sql scripts for them, and run them on
the db. I've now got files back in sharepoint.
The sharepoint strucure does not look like the users are saying it should,
so I'm either executing the scripts in the wrong order or they're not
shooting me straight. But I can deal with that. ;-)
Thanks a million for all of your help.
"Jack" <anonymous@.microsoft.com> wrote in message
news:%23N8QDhV7GHA.4604@.TK2MSFTNGP03.phx.gbl...
>I goofed and did not have a maintenance plan running. Someone deleted a
>folder in Sharepoint with thousands of documents.
> Since no backup has been run, the transaction log has not been truncated.
> Is there any way to roll back all transactions since Monday, or am I
> hosed?
>
I'm happy for you, Jack. :-)
I'm also a bit surprised that the transaction still existed in the log. Either you were *very* lucky
that the log records are still around. Or, a db backup was actually taken of the database at some
point in time. In any case, I have a feeling that you will look over your backup strategy now... :-)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Jack" <anonymous@.microsoft.com> wrote in message news:OKGZU8Y7GHA.3836@.TK2MSFTNGP02.phx.gbl...
> Log Explorer from Lumigent absolutely rocks. I was able to view the three major delete
> transactions, generate sql scripts for them, and run them on the db. I've now got files back in
> sharepoint.
> The sharepoint strucure does not look like the users are saying it should, so I'm either executing
> the scripts in the wrong order or they're not shooting me straight. But I can deal with that.
> ;-)
> Thanks a million for all of your help.
>
> "Jack" <anonymous@.microsoft.com> wrote in message news:%23N8QDhV7GHA.4604@.TK2MSFTNGP03.phx.gbl...
>

Partial Success

Log Explorer from Lumigent absolutely rocks. I was able to view the three
major delete transactions, generate sql scripts for them, and run them on
the db. I've now got files back in sharepoint.
The sharepoint strucure does not look like the users are saying it should,
so I'm either executing the scripts in the wrong order or they're not
shooting me straight. But I can deal with that. ;-)
Thanks a million for all of your help.
"Jack" <anonymous@.microsoft.com> wrote in message
news:%23N8QDhV7GHA.4604@.TK2MSFTNGP03.phx.gbl...
>I goofed and did not have a maintenance plan running. Someone deleted a
>folder in Sharepoint with thousands of documents.
> Since no backup has been run, the transaction log has not been truncated.
> Is there any way to roll back all transactions since Monday, or am I
> hosed?
>I'm happy for you, Jack. :-)
I'm also a bit surprised that the transaction still existed in the log. Eith
er you were *very* lucky
that the log records are still around. Or, a db backup was actually taken of
the database at some
point in time. In any case, I have a feeling that you will look over your ba
ckup strategy now... :-)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Jack" <anonymous@.microsoft.com> wrote in message news:OKGZU8Y7GHA.3836@.TK2MSFTNGP02.phx.gbl
..
> Log Explorer from Lumigent absolutely rocks. I was able to view the three
major delete
> transactions, generate sql scripts for them, and run them on the db. I've
now got files back in
> sharepoint.
> The sharepoint strucure does not look like the users are saying it should,
so I'm either executing
> the scripts in the wrong order or they're not shooting me straight. But I
can deal with that.
> ;-)
> Thanks a million for all of your help.
>
> "Jack" <anonymous@.microsoft.com> wrote in message news:%23N8QDhV7GHA.4604@.
TK2MSFTNGP03.phx.gbl...
>

PARTIAL SQL Server RESTORE's

Please..
Can someone confirm that with a PARTIAL FILEGROUP SQL Server RESTORE you
have to have a Full Database Backup (containing all the Filegroups including
the PRIMARY) before you can do a PARTIAL filegroup RESTORE.
Even if you have a performed a filegroup Backup which contains the PRIMARY
filegroup and all the other filegroups then it will not work and you get the
error message:
Server: Msg 3135, Level 16, State 2, Line 1
"was created by BACKUP DATABASE...FILE=<name> and cannot be used for this
restore operation."
All the examples I have seen on the net use a FULL Database backup as a
starting point and I have seen the following posting which kind of confirms
my suspisions from someone with a microsoft.com email address
http://groups.google.com/groups?q=pa...TNGP12&rnum=25
Furtheremore, all the documentation in BOL is very very murky on this
particular subject area:
Partial Database Restore Operations
"Partial restore operations work with database filegroups. The primary
filegroup is always restored, along with the files that you specify and
their corresponding filegroups. The result is a subset of the database.
Filegroups that are not restored are marked as offline and are not
accessible.
Partial restore operations are accomplished with the PARTIAL clause of the
RESTORE statement. You can also use the PARTIAL option when restoring a full
database backup. Partial database restore of file backups is not supported."
What it says is "Partial database restore of file backups is not supported"
I think it should ALSO say "Partial database restore of filegroup and file
backups is not supported"
Many Thanks
Paul McMillan
Paul,
I agree with your points, and this is how I understand that things work as well. Also, I just ran a test,
which also confirms it (see script at below, adapted from example in BOL). Did you perform the Books Online
feedback on this (the envelope top left in the right pane)?
USE master
GO
DROP DATABASE mywind_part
GO
DROP DATABASE mywind
GO
CREATE DATABASE mywind
GO
ALTER DATABASE mywind ADD FILEGROUP new_customers
ALTER DATABASE mywind ADD FILEGROUP sales
GO
ALTER DATABASE mywind ADD FILE
(NAME='mywind_data_1',
FILENAME='c:\mw.dat1')
TO FILEGROUP new_customers
ALTER DATABASE mywind
ADD FILE
(NAME='mywind_data_2',
FILENAME='c:\mw.dat2')
TO FILEGROUP sales
GO
CREATE TABLE mywind..t2 (id int) ON sales
-- A full database backup is performed.
-- Then the t1 table is created on new_customers.
-- The transaction log is backed up:
BACKUP DATABASE mywind
filegroup = 'sales', filegroup='primary', filegroup = 'new_customers'
TO DISK ='c:\mywind.dmp'
WITH INIT
GO
USE mywind
GO
CREATE TABLE t1 (id int) ON new_customers
GO
BACKUP LOG mywind TO DISK='c:\mywind.dmp'
WITH NOINIT
GO
-- At some point, it becomes necessary to restore the t2 table
-- on the sales filegroup. RESTORE FILELISTONLY lists the database
-- files and the filegroups in which they reside.
-- RESTORE HEADERONLY lists the contents of the backup medium:
-- RESTORE FILELISTONLY FROM DISK='c:\mywind.dmp'
-- GO
-- RESTORE HEADERONLY FROM DISK='c:\mywind.dmp'
-- GO
-- The RESTORE DATABASE statement restores the database under a different name
-- and the sales filegroup using the WITH PARTIAL and NORECOVERY options.
-- In addition, the primary file and filegroup (mywind), the log (mywind_log),
-- and all files in the restored filegroup (in this example, mywind_data_2 is the
-- only file in sales) are moved to a new location. The log is then recovered:
RESTORE DATABASE mywind_part
FILEGROUP = 'sales'
FROM DISK='c:\mywind.dmp'
WITH FILE=1,RECOVERY,PARTIAL,
-- WITH FILE=1,NORECOVERY,PARTIAL,
MOVE 'mywind' TO 'c:\mw2.pri',
MOVE 'mywind_log' TO 'c:\mw2.log',
MOVE 'mywind_data_2' TO 'c:\mw2.dat2'
GO
-- RESTORE LOG mywind_part
-- FROM DISK = 'c:\mywind.dmp'
-- WITH FILE = 2,RECOVERY
-- GO
--Notice that t2 is accessible after the partial restore operation.
SELECT COUNT(*) FROM mywind_part..t2
--Notice that t1 is not accessible after the partial log restore operation.
SELECT COUNT(*) FROM mywind_part..t1
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Paul McMillan" <paul.mcmillan@.email4u.com> wrote in message news:%23xZPXcXTEHA.2716@.tk2msftngp13.phx.gbl...
> Please..
> Can someone confirm that with a PARTIAL FILEGROUP SQL Server RESTORE you
> have to have a Full Database Backup (containing all the Filegroups including
> the PRIMARY) before you can do a PARTIAL filegroup RESTORE.
> Even if you have a performed a filegroup Backup which contains the PRIMARY
> filegroup and all the other filegroups then it will not work and you get the
> error message:
> Server: Msg 3135, Level 16, State 2, Line 1
> "was created by BACKUP DATABASE...FILE=<name> and cannot be used for this
> restore operation."
>
> All the examples I have seen on the net use a FULL Database backup as a
> starting point and I have seen the following posting which kind of confirms
> my suspisions from someone with a microsoft.com email address
>
http://groups.google.com/groups?q=pa...TNGP12&rnum=25
> Furtheremore, all the documentation in BOL is very very murky on this
> particular subject area:
> Partial Database Restore Operations
> "Partial restore operations work with database filegroups. The primary
> filegroup is always restored, along with the files that you specify and
> their corresponding filegroups. The result is a subset of the database.
> Filegroups that are not restored are marked as offline and are not
> accessible.
> Partial restore operations are accomplished with the PARTIAL clause of the
> RESTORE statement. You can also use the PARTIAL option when restoring a full
> database backup. Partial database restore of file backups is not supported."
> What it says is "Partial database restore of file backups is not supported"
> I think it should ALSO say "Partial database restore of filegroup and file
> backups is not supported"
> Many Thanks
> Paul McMillan
>
|||Tibor
Thanks - As you get the same, it proves I am not going mad and I tried your
code with the same results. So I believe the documentation is a bit
misleading in this area...
Re: Did you perform the Books Online feedback on this (the envelope top left
in the right pane)?
No - But I have now! - outlining what I believe is unclear documentation.
Thanks again
Paul

PARTIAL SQL Server RESTORE's

Please..
Can someone confirm that with a PARTIAL FILEGROUP SQL Server RESTORE you
have to have a Full Database Backup (containing all the Filegroups including
the PRIMARY) before you can do a PARTIAL filegroup RESTORE.
Even if you have a performed a filegroup Backup which contains the PRIMARY
filegroup and all the other filegroups then it will not work and you get the
error message:
Server: Msg 3135, Level 16, State 2, Line 1
"was created by BACKUP DATABASE...FILE=<name> and cannot be used for this
restore operation."
All the examples I have seen on the net use a FULL Database backup as a
starting point and I have seen the following posting which kind of confirms
my suspisions from someone with a microsoft.com email address
http://groups.google.com/groups?q=partial+sql+server+restore+full+backup&start=20&hl=en&lr=&ie=UTF-8&selm=eJxAxlwzCHA.1576%40TK2MSFTNGP12&rnum=25
Furtheremore, all the documentation in BOL is very very murky on this
particular subject area:
Partial Database Restore Operations
"Partial restore operations work with database filegroups. The primary
filegroup is always restored, along with the files that you specify and
their corresponding filegroups. The result is a subset of the database.
Filegroups that are not restored are marked as offline and are not
accessible.
Partial restore operations are accomplished with the PARTIAL clause of the
RESTORE statement. You can also use the PARTIAL option when restoring a full
database backup. Partial database restore of file backups is not supported."
What it says is "Partial database restore of file backups is not supported"
I think it should ALSO say "Partial database restore of filegroup and file
backups is not supported"
Many Thanks
Paul McMillanPaul,
I agree with your points, and this is how I understand that things work as well. Also, I just ran a test,
which also confirms it (see script at below, adapted from example in BOL). Did you perform the Books Online
feedback on this (the envelope top left in the right pane)?
USE master
GO
DROP DATABASE mywind_part
GO
DROP DATABASE mywind
GO
CREATE DATABASE mywind
GO
ALTER DATABASE mywind ADD FILEGROUP new_customers
ALTER DATABASE mywind ADD FILEGROUP sales
GO
ALTER DATABASE mywind ADD FILE
(NAME='mywind_data_1',
FILENAME='c:\mw.dat1')
TO FILEGROUP new_customers
ALTER DATABASE mywind
ADD FILE
(NAME='mywind_data_2',
FILENAME='c:\mw.dat2')
TO FILEGROUP sales
GO
CREATE TABLE mywind..t2 (id int) ON sales
-- A full database backup is performed.
-- Then the t1 table is created on new_customers.
-- The transaction log is backed up:
BACKUP DATABASE mywind
filegroup = 'sales', filegroup='primary', filegroup = 'new_customers'
TO DISK ='c:\mywind.dmp'
WITH INIT
GO
USE mywind
GO
CREATE TABLE t1 (id int) ON new_customers
GO
BACKUP LOG mywind TO DISK='c:\mywind.dmp'
WITH NOINIT
GO
-- At some point, it becomes necessary to restore the t2 table
-- on the sales filegroup. RESTORE FILELISTONLY lists the database
-- files and the filegroups in which they reside.
-- RESTORE HEADERONLY lists the contents of the backup medium:
-- RESTORE FILELISTONLY FROM DISK='c:\mywind.dmp'
-- GO
-- RESTORE HEADERONLY FROM DISK='c:\mywind.dmp'
-- GO
-- The RESTORE DATABASE statement restores the database under a different name
-- and the sales filegroup using the WITH PARTIAL and NORECOVERY options.
-- In addition, the primary file and filegroup (mywind), the log (mywind_log),
-- and all files in the restored filegroup (in this example, mywind_data_2 is the
-- only file in sales) are moved to a new location. The log is then recovered:
RESTORE DATABASE mywind_part
FILEGROUP = 'sales'
FROM DISK='c:\mywind.dmp'
WITH FILE=1,RECOVERY,PARTIAL,
-- WITH FILE=1,NORECOVERY,PARTIAL,
MOVE 'mywind' TO 'c:\mw2.pri',
MOVE 'mywind_log' TO 'c:\mw2.log',
MOVE 'mywind_data_2' TO 'c:\mw2.dat2'
GO
-- RESTORE LOG mywind_part
-- FROM DISK = 'c:\mywind.dmp'
-- WITH FILE = 2,RECOVERY
-- GO
--Notice that t2 is accessible after the partial restore operation.
SELECT COUNT(*) FROM mywind_part..t2
--Notice that t1 is not accessible after the partial log restore operation.
SELECT COUNT(*) FROM mywind_part..t1
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Paul McMillan" <paul.mcmillan@.email4u.com> wrote in message news:%23xZPXcXTEHA.2716@.tk2msftngp13.phx.gbl...
> Please..
> Can someone confirm that with a PARTIAL FILEGROUP SQL Server RESTORE you
> have to have a Full Database Backup (containing all the Filegroups including
> the PRIMARY) before you can do a PARTIAL filegroup RESTORE.
> Even if you have a performed a filegroup Backup which contains the PRIMARY
> filegroup and all the other filegroups then it will not work and you get the
> error message:
> Server: Msg 3135, Level 16, State 2, Line 1
> "was created by BACKUP DATABASE...FILE=<name> and cannot be used for this
> restore operation."
>
> All the examples I have seen on the net use a FULL Database backup as a
> starting point and I have seen the following posting which kind of confirms
> my suspisions from someone with a microsoft.com email address
>
http://groups.google.com/groups?q=partial+sql+server+restore+full+backup&start=20&hl=en&lr=&ie=UTF-8&selm=eJxAxlwzCHA.1576%40TK2MSFTNGP12&rnum=25
> Furtheremore, all the documentation in BOL is very very murky on this
> particular subject area:
> Partial Database Restore Operations
> "Partial restore operations work with database filegroups. The primary
> filegroup is always restored, along with the files that you specify and
> their corresponding filegroups. The result is a subset of the database.
> Filegroups that are not restored are marked as offline and are not
> accessible.
> Partial restore operations are accomplished with the PARTIAL clause of the
> RESTORE statement. You can also use the PARTIAL option when restoring a full
> database backup. Partial database restore of file backups is not supported."
> What it says is "Partial database restore of file backups is not supported"
> I think it should ALSO say "Partial database restore of filegroup and file
> backups is not supported"
> Many Thanks
> Paul McMillan
>|||Tibor
Thanks - As you get the same, it proves I am not going mad and I tried your
code with the same results. So I believe the documentation is a bit
misleading in this area...
Re: Did you perform the Books Online feedback on this (the envelope top left
in the right pane)?
No - But I have now! - outlining what I believe is unclear documentation.
Thanks again
Paul

PARTIAL SQL Server RESTORE's

Please..
Can someone confirm that with a PARTIAL FILEGROUP SQL Server RESTORE you
have to have a Full Database Backup (containing all the Filegroups including
the PRIMARY) before you can do a PARTIAL filegroup RESTORE.
Even if you have a performed a filegroup Backup which contains the PRIMARY
filegroup and all the other filegroups then it will not work and you get the
error message:
Server: Msg 3135, Level 16, State 2, Line 1
"was created by BACKUP DATABASE...FILE=<name> and cannot be used for this
restore operation."
All the examples I have seen on the net use a FULL Database backup as a
starting point and I have seen the following posting which kind of confirms
my suspisions from someone with a microsoft.com email address
&start=20&
hl=en&lr=&ie=UTF-8&selm=eJxAxlwzCHA.1576%40TK2MSFTNGP12&rnum=25" target="_blank">http://groups.google.com/groups? q=...FTNGP12&rnum=25
Furtheremore, all the documentation in BOL is very very murky on this
particular subject area:
Partial Database Restore Operations
"Partial restore operations work with database filegroups. The primary
filegroup is always restored, along with the files that you specify and
their corresponding filegroups. The result is a subset of the database.
Filegroups that are not restored are marked as offline and are not
accessible.
Partial restore operations are accomplished with the PARTIAL clause of the
RESTORE statement. You can also use the PARTIAL option when restoring a full
database backup. Partial database restore of file backups is not supported."
What it says is "Partial database restore of file backups is not supported"
I think it should ALSO say "Partial database restore of filegroup and file
backups is not supported"
Many Thanks
Paul McMillanPaul,
I agree with your points, and this is how I understand that things work as w
ell. Also, I just ran a test,
which also confirms it (see script at below, adapted from example in BOL). D
id you perform the Books Online
feedback on this (the envelope top left in the right pane)?
USE master
GO
DROP DATABASE mywind_part
GO
DROP DATABASE mywind
GO
CREATE DATABASE mywind
GO
ALTER DATABASE mywind ADD FILEGROUP new_customers
ALTER DATABASE mywind ADD FILEGROUP sales
GO
ALTER DATABASE mywind ADD FILE
(NAME='mywind_data_1',
FILENAME='c:\mw.dat1')
TO FILEGROUP new_customers
ALTER DATABASE mywind
ADD FILE
(NAME='mywind_data_2',
FILENAME='c:\mw.dat2')
TO FILEGROUP sales
GO
CREATE TABLE mywind..t2 (id int) ON sales
-- A full database backup is performed.
-- Then the t1 table is created on new_customers.
-- The transaction log is backed up:
BACKUP DATABASE mywind
filegroup = 'sales', filegroup='primary', filegroup = 'new_customers'
TO DISK ='c:\mywind.dmp'
WITH INIT
GO
USE mywind
GO
CREATE TABLE t1 (id int) ON new_customers
GO
BACKUP LOG mywind TO DISK='c:\mywind.dmp'
WITH NOINIT
GO
-- At some point, it becomes necessary to restore the t2 table
-- on the sales filegroup. RESTORE FILELISTONLY lists the database
-- files and the filegroups in which they reside.
-- RESTORE HEADERONLY lists the contents of the backup medium:
-- RESTORE FILELISTONLY FROM DISK='c:\mywind.dmp'
-- GO
-- RESTORE HEADERONLY FROM DISK='c:\mywind.dmp'
-- GO
-- The RESTORE DATABASE statement restores the database under a different na
me
-- and the sales filegroup using the WITH PARTIAL and NORECOVERY options.
-- In addition, the primary file and filegroup (mywind), the log (mywind_log
),
-- and all files in the restored filegroup (in this example, mywind_data_2 i
s the
-- only file in sales) are moved to a new location. The log is then recover
ed:
RESTORE DATABASE mywind_part
FILEGROUP = 'sales'
FROM DISK='c:\mywind.dmp'
WITH FILE=1,RECOVERY,PARTIAL,
-- WITH FILE=1,NORECOVERY,PARTIAL,
MOVE 'mywind' TO 'c:\mw2.pri',
MOVE 'mywind_log' TO 'c:\mw2.log',
MOVE 'mywind_data_2' TO 'c:\mw2.dat2'
GO
-- RESTORE LOG mywind_part
-- FROM DISK = 'c:\mywind.dmp'
-- WITH FILE = 2,RECOVERY
-- GO
--Notice that t2 is accessible after the partial restore operation.
SELECT COUNT(*) FROM mywind_part..t2
--Notice that t1 is not accessible after the partial log restore operation.
SELECT COUNT(*) FROM mywind_part..t1
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Paul McMillan" <paul.mcmillan@.email4u.com> wrote in message news:%23xZPXcXTEHA.2716@.tk2msft
ngp13.phx.gbl...
> Please..
> Can someone confirm that with a PARTIAL FILEGROUP SQL Server RESTORE you
> have to have a Full Database Backup (containing all the Filegroups includi
ng
> the PRIMARY) before you can do a PARTIAL filegroup RESTORE.
> Even if you have a performed a filegroup Backup which contains the PRIMARY
> filegroup and all the other filegroups then it will not work and you get t
he
> error message:
> Server: Msg 3135, Level 16, State 2, Line 1
> "was created by BACKUP DATABASE...FILE=<name> and cannot be used for this
> restore operation."
>
> All the examples I have seen on the net use a FULL Database backup as a
> starting point and I have seen the following posting which kind of confirm
s
> my suspisions from someone with a microsoft.com email address
>
&start=20&hl=en&lr=&ie=UTF
-8&selm=eJxAxlwzCHA.1576%40TK2MSFTNGP12&rnum=25" target="_blank">http://groups.google.com/groups? q=...FTNGP12&rnum=25
> Furtheremore, all the documentation in BOL is very very murky on this
> particular subject area:
> Partial Database Restore Operations
> "Partial restore operations work with database filegroups. The primary
> filegroup is always restored, along with the files that you specify and
> their corresponding filegroups. The result is a subset of the database.
> Filegroups that are not restored are marked as offline and are not
> accessible.
> Partial restore operations are accomplished with the PARTIAL clause of the
> RESTORE statement. You can also use the PARTIAL option when restoring a fu
ll
> database backup. Partial database restore of file backups is not supported
."
> What it says is "Partial database restore of file backups is not supported
"
> I think it should ALSO say "Partial database restore of filegroup and file
> backups is not supported"
> Many Thanks
> Paul McMillan
>|||Tibor
Thanks - As you get the same, it proves I am not going mad and I tried your
code with the same results. So I believe the documentation is a bit
misleading in this area...
Re: Did you perform the Books Online feedback on this (the envelope top left
in the right pane)?
No - But I have now! - outlining what I believe is unclear documentation.
Thanks again
Paul

Partial searches over a lot of fields

Hi All,
I have the following scenario. I have a table called Invoice. This
has around 30 columns of which i have to do a retrieval based on
filter conditions on 10 columns. These filters need to be partial
searches i.e. for e.g the Customer name could be 'Arun', 'Parthiv',
'Aaron', now i should be able to search the customer as 'ar' and it
should return 'Arun' and 'Parthiv'. My concern is there are 10 columns
on which this like '%x%' search has to be done and there will
practically be hudreds of thousands of rows. can anybody suggest me to
improve the performance of such a query. Currently what i am thinkin
of is
select Id, Memo, .. FROM Invoice where CustomerName like '%' + @.Name +
'%' and etc.
P.S. am using ASP.Net as the front end.ArunPrakash (arunprakashb@.yahoo.com) writes:
> I have the following scenario. I have a table called Invoice. This
> has around 30 columns of which i have to do a retrieval based on
> filter conditions on 10 columns. These filters need to be partial
> searches i.e. for e.g the Customer name could be 'Arun', 'Parthiv',
> 'Aaron', now i should be able to search the customer as 'ar' and it
> should return 'Arun' and 'Parthiv'. My concern is there are 10 columns
> on which this like '%x%' search has to be done and there will
> practically be hudreds of thousands of rows. can anybody suggest me to
> improve the performance of such a query. Currently what i am thinkin
> of is
> select Id, Memo, .. FROM Invoice where CustomerName like '%' + @.Name +
> '%' and etc.

You can use SELECT TOP or SET ROWCOUNT to restrict the number of rows
returned. A good value is probably 2000. If you get 2000 rows, you tell
the user to refine his conditions.

Also keep in mind, that there could be a great difference in performance
when searching for names that start with 'Ar', or have 'ar' anywhere in
the name. If the search column is indexed, that index can be used
for the case "starts with", but not "contains".

There are also a couple of considerations of how to compose the query
to make the search effective. You may be interested in the article
http://www.sommarskog.se/dyn-search.html on my web site.

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

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

partial search performance

To search for partial string we are using WHERE column_name LIKE '%str%'.
But its taking long time to return the results. Is there any way to improve
the performance.
What is the datatype of the column you are searching against. An index would
help majorly but it has to been an allowable type.
/*
Warren Brunk - MCITP,MCTS,MCDBA
www.techintsolutions.com
*/
"Ramu" <Ramu@.discussions.microsoft.com> wrote in message
news:2C4BB57B-48AA-41A1-9594-48DCA08AB50A@.microsoft.com...
> To search for partial string we are using WHERE column_name LIKE
> '%str%'.
> But its taking long time to return the results. Is there any way to
> improve
> the performance.
|||datatype of the column is varchar(max). This column contains the description
of the product. Users want to search for a string rather than a word. If
search by a word is the requirement, I would have gone with FTE.
"Warren Brunk" wrote:

> What is the datatype of the column you are searching against. An index would
> help majorly but it has to been an allowable type.
> --
> /*
> Warren Brunk - MCITP,MCTS,MCDBA
> www.techintsolutions.com
> */
> "Ramu" <Ramu@.discussions.microsoft.com> wrote in message
> news:2C4BB57B-48AA-41A1-9594-48DCA08AB50A@.microsoft.com...
>
>
|||using a wildcard at the start of a LIKE expression does not use an INDEX SEEK
but rather an INDEX SCAN so the query will be slow even if you created
indexes on this column
http://myitforum.com/cs2/blogs/jnelson/archive/2007/11/16/108354.aspx
Misbah Arefin
"Ramu" wrote:
[vbcol=seagreen]
> datatype of the column is varchar(max). This column contains the description
> of the product. Users want to search for a string rather than a word. If
> search by a word is the requirement, I would have gone with FTE.
>
> "Warren Brunk" wrote: