First of all an IDENTITY Primary Key does not a CANDIDATE KEY make.

From the table definition, you would be better off making the surrogate
IDENTITY a NON-CLUSTERED Primary Key and then make the vchName a UNIQUE
CLUSTERED CONSTRAINT; then, all of the items beginning with the same
letter would be logically stored on nearby pages.

This will make the query very fast if the rows are highly selective and
logically in 1NF--a requirement for any relational system.

Sincerely,


Anthony Thomas, MCDBA, MCSA


-----Original Message-----
From: prakash
[mailto:mssqldba-ezmlmshield-x91607615.[Email address protected]
Sent: Wednesday, July 28, 2004 11:47 PM
To: LazyDBA Discussion
Subject: How to make this query run fast !!!!1

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]






---------------------------------------------------------------------
TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
Website : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html
For additional commands, e-mail: mssqldba-[Email address protected]




***********************************************************************
NOTICE: This electronic mail message and any attached files are
confidential. The information is exclusively for the use of the
individual or entity intended as the recipient. If you are not the
intended recipient, any use, copying, printing, reviewing, retention,
disclosure, distribution or forwarding of the message or any attached
file is not authorized and is strictly prohibited. If you have received
this electronic mail message in error, please advise the sender by reply
electronic mail immediately and permanently delete the original
transmission, any attachments and any copies of this message from your
computer system.
***********************************************************************

MS Sql Server LazyDBA home page