MS SQL Server 2000 - Search a table with 300,000+ records in less then a second or tw

I have one table with 300,000 records and 30 columns.
For example columns are ID, COMPANY, PhONE, NOTES ...
---
ID - nvarchar length-9
COMPANY - nvarchar length-30
NOTES - nvarchar length-250
----
Select * from database
where NOTES like '%something%'
Is there a way to get results from this query in less then 1-2 second
and how?You need to use a Full Text Index to do that kind of search quickly. Look
it up in Books Online.
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
--
"nywebmaster" <scgwebmaster@.yahoo.com> wrote in message
news:1129306366.597973.143180@.g43g2000cwa.googlegroups.com...
> I have one table with 300,000 records and 30 columns.
> For example columns are ID, COMPANY, PhONE, NOTES ...
> ---
> ID - nvarchar length-9
> COMPANY - nvarchar length-30
> NOTES - nvarchar length-250
> ----
> Select * from database
> where NOTES like '%something%'
> Is there a way to get results from this query in less then 1-2 second
> and how?
>|||Pattern matched searching when the wild card is prefixed to the parameter
cannot use an index and so in most cases, a table scan in employed. If this
is something critical, you might want to look into full text indexing
options.
If you know the pattern upfront, one trick you can use like create a
computed column representing the part of the string and indexing the column.
Anith|||To begin with, insure that NOTES is indexed.
http://www.microsoft.com/technet/pr...s/c0618260.mspx
Performing a LIKE search on '%something%' will not efficeintly utilize an
index on NOTES, however, 'something%' would.
http://msdn.microsoft.com/library/d...dcharacters.asp
If you need to perform fast 'wildcard' type searches, then consider
implemeting Index Server and full-text search. It is a service that runs
along side SQL Server. Just remember that the predicates CONTAINS and
FREETEXT are used for free-text searches, so it will involve making
revisions to some of your queries.
http://msdn.microsoft.com/library/d...r />
_3rqg.asp
"nywebmaster" <scgwebmaster@.yahoo.com> wrote in message
news:1129306366.597973.143180@.g43g2000cwa.googlegroups.com...
> I have one table with 300,000 records and 30 columns.
> For example columns are ID, COMPANY, PhONE, NOTES ...
> ---
> ID - nvarchar length-9
> COMPANY - nvarchar length-30
> NOTES - nvarchar length-250
> ----
> Select * from database
> where NOTES like '%something%'
> Is there a way to get results from this query in less then 1-2 second
> and how?
>|||Thanks