I would create three indexes, one for each column, and use a WHERE clause in SELECT, similar to this one:
WHERE empno = ?m.empno AND kdate = ?m.kdate AND shiftno = ?m.shiftno Note: using functions in expressions used in where is a performance killer because those functions get evaluated for each and every record. Index the columns and use WHERE properly. Character columns should be defined as VARCHAR(n) and make sure no extra spaces go to server with the actual values. In VFP this could be easily accomplished by using SET VARCHARMAPPING. There's one more issue which you may need to take care of: the ThisForm.txtName.Value expression contains extra spaces at the end, which need to be trimmed out. This could be done programatically, before sending the values to server, or directly from the interface, by setting the Format property to "F". Hope this helps. On Wed, Sep 21, 2011 at 3:54 PM, Stephen Russell <[email protected]> wrote: > On Wed, Sep 21, 2011 at 7:37 AM, Ajoy Khaund <[email protected]> wrote: >> Hi, >> >> I have a composite key in VFP like >> >> Padl(empno,4,'0') + DTOS(kdate) + Str(shiftno,1) >> >> I use this index to do a seek to find out if a record exists for a >> particular employee on a particular date in a particular shift. >> >> How can I do that in sql. I am going to use cursor adaptors. >> > ------------- > > Do you know how to create indexs in SQL Server tables that span > multiple columns? > > CREATE UNIQUE NONCLUSTERED INDEX newKey ON YourTable > ( > empNumber, > kDate, > shiftNo > ) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, > ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] > GO > > > In your where clause : > Where empNumber = YourmakeshiftValue > and kDatebetween StartOfDay and EndOfDay > and shftno = 1 > > As a heads up. This index may never get used if the table is joined > so all of this is mute. > > > -- > Stephen Russell > > Unified Health Services > 60 Germantown Court > Suite 220 > Cordova, TN 38018 > > Telephone: 888.510.2667 > > 901.246-0159 cell > [excessive quoting removed by server] _______________________________________________ Post Messages to: [email protected] Subscription Maintenance: http://leafe.com/mailman/listinfo/profox OT-free version of this list: http://leafe.com/mailman/listinfo/profoxtech Searchable Archive: http://leafe.com/archives/search/profox This message: http://leafe.com/archives/byMID/profox/CAH=cqdjtoyc5ik14cldb1dkofhvs0gujgvy8xvgt65jaygc...@mail.gmail.com ** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.
