Indexing question

I have a basic rudimentary question concerning creating indexes.
Assuming a table :
FieldA varchar(1)
FieldB varchar(254)
FieldC varchar(25)
FieldD int (identity)
Now assuming we want to query
Select * from table1 where fieldb like '%b%' and fieldA is null
Should I create an index on FieldA and a separate index on FieldB
OR
create an index with FieldA AND FieldB
Basically do I create several individual indexes or create one index for
each type of query (as I may run several different kinds on the same table)
How does SQLServer know to use which index..'
Sorry for the newbieness...
Thanks,
-CraigCraig,
First of all, the only advantage of including fieldB in an index is
if you can thereby have a covering index either for the full WHERE
clause or for the SELECT list. Since you have SELECT *, and the table
has columns besides fieldA and fieldB, the entire table will have to be
accessed in any case. I would suggest these three as the most
reasonable possibilities:
Clustered index on (FieldA) or (FieldA, other columns)
This will always help unless (fieldA is null) is true for most of the
table, but since you can have only one clustered index on the table, it
only makes sense if no other clustered index is more compelling.
or
Nonclustered index on (fieldA, fieldB)
This can only help if the number of rows for which (fieldA is null) and
(fieldB like '%b%') is relatively small - definitely it would have to be
less than the number of data pages in the entire table, which could mean
between about 1-4% of the rows of the table or fewer.
or
Nonclustered index on (fieldA, fieldB, fieldC, fieldD)
This can help unless (fieldA is null) is true for most of the rows, and
allows a clustered index on some other column(s).
Other considerations include the activity on the table. For example, if
fieldC or fieldD is frequently updated, any index including those
columns will result in extra work from the updates.
There are no simple answers - the index tuning wizard may help you out,
and you could look through some books, such as Ken Henderson's Guru's
Guide to Transact-SQL or Kalen Delaney's Inside SQL Server 2000.
SK
Craig Stadler wrote:

quote:

---

>I have a basic rudimentary question concerning creating indexes.
>Assuming a table :
>FieldA varchar(1)
>FieldB varchar(254)
>FieldC varchar(25)
>FieldD int (identity)
>Now assuming we want to query
>Select * from table1 where fieldb like '%b%' and fieldA is null
>Should I create an index on FieldA and a separate index on FieldB
>OR
>create an index with FieldA AND FieldB
>Basically do I create several individual indexes or create one index for
>each type of query (as I may run several different kinds on the same table)
>How does SQLServer know to use which index..'
>Sorry for the newbieness...
>Thanks,
>-Craig
>
>

---

|||Craig,
Creating a clustered index on FieldA (or FieldA and some other
column(s)) is about the only way to slightly improve performance for
this query. This is because:
- the predicate FieldB LIKE '%b%' cannot use partial index scan or index
seek
- any index on FieldB will be almost as big as the table itself
A nonclustered index on FieldA could be an option if there are very few
rows where FieldA IS NULL (let's say, less than 3% of all rows).
Hope this helps,
Gert-Jan
Craig Stadler wrote:

quote:

---

> I have a basic rudimentary question concerning creating indexes.
> Assuming a table :
> FieldA varchar(1)
> FieldB varchar(254)
> FieldC varchar(25)
> FieldD int (identity)
> Now assuming we want to query
> Select * from table1 where fieldb like '%b%' and fieldA is null
> Should I create an index on FieldA and a separate index on FieldB
> OR
> create an index with FieldA AND FieldB
> Basically do I create several individual indexes or create one index for
> each type of query (as I may run several different kinds on the same table
)
> How does SQLServer know to use which index..'
> Sorry for the newbieness...
> Thanks,
> -Craig

---

sql