Hi all,

I have a friend who was talking with me this last weekend on the phone regarding a problem he was having with SQL 7.0. I suggested he email me a synopsis and I'd post it here (and the SQL forum) to see if anyone could help. Any help would be appreciated!

Thanks!

DD

*************

Doug,

It was nice to chat with you on Saturday. I hope all is going well for you
and Karol. Below is a summary of my SQL problem, suitable for cutting and
pasting.

Mark

-------------------------

I am running SQL Server 7 (version 7.00.623) running on Windows NT Small
Business Back Office Server 4.5 with SP 5 installed.

I have a table in a database that has about 760,000 records. Two of the
fields are ID and NAME_LICENSEE. There is no relationship between these
fields (i.e. the ID is not derived from the NAME_LICENSEE). ID is unique and
is setup as the primary (clustered) index for the table. NAME_LICENSEE is
not unique, but is setup as a non-clustered index. NAME_LICENSEE contains a
persons' name in the format "LAST, FIRST".

To allow the user to "browse" the database, I issue the query like "select *
from (table) where NAME_LICENSEE like "erb%". I found that for some values
of the search criteria, still the same format with the % at the end and no
embedded or leading wildcards, the query does not use the index on
NAME_LICENSEE. I've seen this by using the query analyzer. For many of my
test cases, it does, but for some it does not and instead uses the primary
index, a filter and a sort. The performance of this plan is obviously slow
(when it uses the index, the query returns in < 1 sec, when it doesn't, it
takes about 18 seconds).

When I first discovered this, I found some specific cases where it uses the
NAME index and some where it doesn't. i.e. for "erb%" it used the NAME
index, but for "van bu%" it did not. Both search criteria return a similar
number of records. The index had been created when I setup the table with
the syntax "CREATE INDEX name ON appl (name_licensee) ". I dropped the index
and, as expected, now all search criteria resulted in lookups using the
primary index. I ran the index analysis option in query analyzer and it said
that it would work better if it had an index on NAME_LICENSEE (no suprise
here) and offered to create it. The only difference was that it specified
"NONCLUSTERED" and used a different name for the index. I let it create the
index. Now, both "erb%" and "van bu%" result in index searches. I thought my
problem was solved, but with continued testing, I discovered another query,
"tab%" that still didn't use the index, resulting in slow results.

-------------------------