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

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?
Maybe. If possible begin the % with a leading character and try creating a
NC index on the NOTES column. Or might also consider creating a Full-Text
index on the NOTES column and then use CONTAINS or FREETEXT.
HTH
Jerry
"nywebmaster" <scgwebmaster@.yahoo.com> wrote in message
news:1129306144.751022.186010@.o13g2000cwo.googlegr oups.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.

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

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?
Full Text index this table and run a full population.
The query would look like this
select * from database where contains(NOTES,'something')
Use the wizard to build the FTS index on your table and make sure you run a
full population.
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
<scgwebmaster@.yahoo.com> wrote in message
news:1129307133.782947.259880@.f14g2000cwb.googlegr oups.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?
>

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

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?Maybe. If possible begin the % with a leading character and try creating a
NC index on the NOTES column. Or might also consider creating a Full-Text
index on the NOTES column and then use CONTAINS or FREETEXT.
HTH
Jerry
"nywebmaster" <scgwebmaster@.yahoo.com> wrote in message
news:1129306144.751022.186010@.o13g2000cwo.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.sql

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

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

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?A few posts below is a remarkably similar question, only the table has
300,000+ rows. I think it applies to your case as well.
<scgwebmaster@.yahoo.com> wrote in message
news:1129305896.814659.100590@.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?
>