>>I know how to make SQL statements querying against foxpro data fast using the indexes but how does it work for SQL Server. There is table that holds holidays with and index on company Char(10), fiscal Char(4), holiday datetime.
>>
>>How do I write a SQL Query that will use this index? I don't think I can pass up company+fiscal+holiday in the SQL Query because of the mismatch in datatypes. Also can I use partial matches to get optimization as well. For example if I just want to match the company and fiscal in foxpro I could still query using the entire index and just put the partial value to match and it would use the index to retive the data.
>
>In SQL Server (newer version) look up at Covering indexes in Help.
>
>What indexes do you have so far there? What is the statement you have been trying?

Right now the where clause is

WHERE company + fiscal = ?lc_company + ? lc_fiscal AND holiday > ?ld_begin AND holiday < ?ld_holiday

I know this wouldn't be optimized in FoxPro was looking to see if there is a way to optimize the where clause for SQL with the existing index. All I see online is how to create index to match your query but I was hoping to go the other way. Make my query try and use the existing indexes. From reading the help it seems like I wouldn't want to concatenate company + fiscal in the where clause

Create Multiple-Column Indexes
Multiple-column indexes are natural extensions of single-column indexes. Multiple-column indexes are useful for evaluating filter expressions that match a prefix set of key columns. For example, the composite index CREATE INDEX Idx_Emp_Name ON Employees ("Last Name" ASC, "First Name" ASC) helps evaluate the following queries:

... WHERE "Last Name" = 'Doe'
... WHERE "Last Name" = 'Doe' AND "First Name" = 'John'
... WHERE "First Name" = 'John' AND "Last Name" = 'Doe'

However, it is not useful for this query:

... WHERE "First Name" = 'John'

When you create a multiple-column index, you should put the most selective columns leftmost in the key. This makes the index more selective when matching several expressions.