Sql-server – Does the order for clustered index have the same constraints as non-clustered index on search

clustered-indexindexindex-tuningMySQLsql server

I have a table filled with a bunch of data, and I want to use the following clustered index:

[account_id, group_id, run_id, page_id, date]

For a non-clustered index, if I were to to use WHERE filters involving account_id, page_id, and date_time, I know that the index won't work because I'm not also including group_id and run_id. Does the same thing apply when using a clustered index? I often want to make a query in the form:

SELECT * 
FROM my_table
WHERE ad_account_id = %d
  AND page_id = %d
  AND date >= DATE(%s)
  AND date <= DATE(%s)

But sometimes I have queries such as:

SELECT * 
FROM my_table
WHERE ad_account_id = %d
  AND group_id = %d
  AND date >= DATE(%s)
  AND date <= DATE(%s)

or 

SELECT * 
FROM my_table
WHERE ad_account_id = %d
  AND group_id = %d
  AND run_id = %d
  AND date >= DATE(%s)
  AND date <= DATE(%s)

It's not obvious whether any of these take advantage of the clustered index past the one given on the account_id (I'm pretty sure this isn't doing a full table scan but even there not sure).

Best Answer

Just think left-to-right, until you hit a gap.

For Q1, SQL server can search for ad_account_id (assuming that was in the index and you had a typo in the index definition), and then scan those with that value.

For Q2, SQL server can search for ad_account_id and group_id and then scan those.

For Q3, SQL server can seek for all columns in the WHERE clause.

So, yes, the same principals applies to a clustered index as for a non-clustered index.

Related Question