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

From my testing it seems I can only get index seek if the fields I want to pull back are also part of the index and I don't have to include all the fields in the index expression or in order

  *-- Partial match to index
  SELECT holiday FROM table WHERE company = MyComp' AND fiscal = '2009'
  *--- Returns back Index seek with corect index

  *-- Partial match to index with fields out of order
  SELECT holiday FROM table WHERE fiscal = '2009' AND company = MyComp'
  *--- Returns back Index seek with correct index

  *-- Skipping first field in index expression 
  SELECT holiday FROM table WHERE fiscal = '2009' AND holiday = 'Jan 1, 2009'
  *--- Returns back Index scan with correct index

  *-- Return fields not in index and partial match on index
  SELECT * FROM table WHERE company = 'MyComp' AND fiscal = '2009'
  *-- Returns Index Scan and doesn't use the correct index uses primary key

  *-- Return fields not in index and full match on index
  SELECT * FROM table WHERE company = 'MyComp' AND fscal = '2009' AND holiday = 'Jan 1, 2009'
  *-- Even with exact match in where still Index Scan with primary key