>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.

To use an index your where clause must match the fields in index. Remember the position of the field is very important. So if you have index based on these 3 fields you must use WHERE with AND:

WHERE Company = 'something'  AND Fiscal = 'other' AND Holiday = SomeDateTime

That is valid ONLY if the fields use in your index are positioned in that order.

To see if the query use the index you could try your query in SSMS and add Actual execution plan in query. then you could see if the SQL Server use Index Seek, Index scan or table scan. Try to write your query that way so SQL to use Index Seek.