Hi
I have one problem with the index selection on query.


I have a table called Item . The table description is as follows:-

CREATE TABLE [Items] (
[autItemID] [int] IDENTITY (1, 1) NOT NULL ,
[vchItemNumber] [varchar] (255) NOT NULL ,
[vchName] [varchar] (255) NOT NULL ,
[vchDescription] [varchar] (1000) NULL ,
[intState] [tinyint] NOT NULL CONSTRAINT [DF_WD_Items_intState] DEFAULT
(1),
[datCreatedOn] [smalldatetime] NOT NULL CONSTRAINT
[DF__WD_Items_datCreatedOn] DEFAULT (getdate()),
[datModifiedOn] [smalldatetime] NULL ,
CONSTRAINT [PK_WD_Items] PRIMARY KEY CLUSTERED
(
[autItemID]
)
)
GO


All queries are working fine. And giving me index seeks. But I have one
query which searches for the items on vchName field.

SELECT autItemID,vchItemNumber,vchName,vchDescription,intState
FROM DBo.Items
WHERE vchName LIKE @vchLetter + '%'

This query does the index scan. And I don't want index scan as there are
more than 1,000,000 items in the table. I know this will do index scan
because vchName don't have any Index. I tried creating non clustered index
on vchName and this will also will not work because the query is range
query.

So, can anybody give me some alternate solution to make the query fast.


Prakash Zalkikar
SQL DBA
WebDirekt India Pvt LTD
C 1/19 Kumar City, Kalyani Nagar
Pune - 411014
Phone(O):- 91-20-27031240
Phone(R):- 91-20-27034557
Mobile:- 9422314041
mailto:- p.[Email address protected]





MS Sql Server LazyDBA home page