Query Timeouts--Indexing Questions

I am helping a client with query timeout issues. Their tables have an
identity field used as a primary key, with a clustered index on the primary
key. This key really has no functional significance; it is just a number.
One example is that they search for customers by phone number, with an
additional index on the phone number. When I check the execution plan, it
shows a clustered index scan on the clustered index. Isn't a clustered index
s the most efficient?
My question is whether I should drop the primary key and clustered index on
the identity column and create a clustered index on something of physical
significance like a phone number to get better performance?
Any ideas?
Larry Menzin
American Techsystems Corp.Place all the non-cluster index in a separate filegroup.
"Larry Menzin" wrote:

> I am helping a client with query timeout issues. Their tables have an
> identity field used as a primary key, with a clustered index on the primar
y
> key. This key really has no functional significance; it is just a number.
> One example is that they search for customers by phone number, with an
> additional index on the phone number. When I check the execution plan, it
> shows a clustered index scan on the clustered index. Isn't a clustered ind
ex
> s the most efficient?
> My question is whether I should drop the primary key and clustered index o
n
> the identity column and create a clustered index on something of physical
> significance like a phone number to get better performance?
> Any ideas?
> --
> Larry Menzin
> American Techsystems Corp.|||What indexes are used, and how they are used, depend on the columns returned
by the select statement and the nature of the WHERE condition.
SELECT * FROM [YourTable] WHERE [PhoneNumber] = '916-867-5309'
should result in a s of the nonclustered index on [PhoneNumber] with a
bookmark lookup on the clustered index. But, if you changed it to
SELECT * FROM [YourTable] WHERE [PhoneNumber] LIKE '%916-867-5309%'
you will most likely get a clustered index scan. Why? Because the SELECT is
looking for columns not covered by the non-clustered index.
If your application performs queries like
SELECT [CustomerName] FROM [YourTable]
WHERE [PhoneNumber] LIKE '%916-867-5309%'
consider using a composite non-clustered index using both [CustomerName] and
[PhoneNumber] columns.
"Larry Menzin" wrote:

> I am helping a client with query timeout issues. Their tables have an
> identity field used as a primary key, with a clustered index on the primar
y
> key. This key really has no functional significance; it is just a number.
> One example is that they search for customers by phone number, with an
> additional index on the phone number. When I check the execution plan, it
> shows a clustered index scan on the clustered index. Isn't a clustered ind
ex
> s the most efficient?
> My question is whether I should drop the primary key and clustered index o
n
> the identity column and create a clustered index on something of physical
> significance like a phone number to get better performance?
> Any ideas?
> --
> Larry Menzin
> American Techsystems Corp.|||My client is using some wildcards so that indexes are not used properly.
Since there can only be one clustered index per table and my client is using
it on the identity column, I'm stuck using non-clustered indexes.
What strategy is there for sealing with wildcards?
Larry Menzin
American Techsystems Corp.
"Mark Williams" wrote:
> What indexes are used, and how they are used, depend on the columns return
ed
> by the select statement and the nature of the WHERE condition.
> SELECT * FROM [YourTable] WHERE [PhoneNumber] = '916-867-5309'
> should result in a s of the nonclustered index on [PhoneNumber] with a
> bookmark lookup on the clustered index. But, if you changed it to
> SELECT * FROM [YourTable] WHERE [PhoneNumber] LIKE '%916-867-5309%'
> you will most likely get a clustered index scan. Why? Because the SELECT i
s
> looking for columns not covered by the non-clustered index.
> If your application performs queries like
> SELECT [CustomerName] FROM [YourTable]
> WHERE [PhoneNumber] LIKE '%916-867-5309%'
> consider using a composite non-clustered index using both [CustomerName] and
> [PhoneNumber] columns.
> --
>
> "Larry Menzin" wrote:
>|||You won't be able to avoid a scan of an index when using wildcards in the
WHERE condition.
Ideally, you would want an index *s* on a non-clusted index, with a
bookmark lookup to the clustered index. (There are some conditions, believe
it or not, where a scan of the clustered index will be faster, but that's
another story).
Again, your best option would be to create a composite non-clustered index
using the two columns involved in the customer phone - number lookup query.
"Larry Menzin" wrote:
> My client is using some wildcards so that indexes are not used properly.
> Since there can only be one clustered index per table and my client is usi
ng
> it on the identity column, I'm stuck using non-clustered indexes.
> What strategy is there for sealing with wildcards?
> --
> Larry Menzin
> American Techsystems Corp.
>
> "Mark Williams" wrote:
>