First of all, you don't activate indexes. SQL Server optimizer makes the decision if an index (or several indexes) will be used or not. Your job is to ensure that you have proper indexes in place, but also that you don't over-index since that would degrade performance for modifications.
Having that said, back to your actual question:
Yes, a single query on a single table may use multiple indexes if the optimizer sees it reasonable. This is done by 'joining indexes'. In other words fetching rowid's separately from two indexes and then making an inner join with the results.
This can be tested with a simple test. First let's create a table
CREATE TABLE IndexTest (
col1 int,
col2 date
);
GO
And then add a reasonable amount of data into it. The data must contain repetitive values so that a single index isn't too selective
SET NOCOUNT ON;
DECLARE
@counter1 int,
@counter2 int,
@col1 int;
BEGIN
SET @counter1 = 0;
WHILE @counter1 <= 1000 BEGIN
SET @col1 = @counter1;
SET @counter2 = 1;
WHILE @counter2 <= 1000 BEGIN
INSERT INTO IndexTest (col1, col2) VALUES (@col1, GETDATE() + ROUND(RAND()*100,0))
SET @counter2 = @counter2 + 1;
END;
SET @counter1 = @counter1 + 1;
END;
END;
GO
Now we have a little bit over 1'000'000 rows.
Let's create our indexes
CREATE INDEX X1 ON IndexTest (col1);
CREATE INDEX X2 ON IndexTest (col2);
Now consider the following query
SELECT *
FROM IndexTest
WHERE col1 = 123
AND col2 = CAST((GETDATE() + 2) AS DATE)
Both conditions would return thousand or more rows separately but the actual result is likely to be much smaller. In my test run the query returned 13 rows.
So let's take a look how the query was run. The execution plan looks like this
|--Hash Match(Inner Join, HASH:([Bmk1000])=([Bmk1000]), RESIDUAL:([Bmk1000] = [Bmk1000]))
|--Index Seek(OBJECT:([IndexTest].[X1]), SEEK:([IndexTest].[col1]=(123)) ORDERED FORWARD)
|--Index Seek(OBJECT:([IndexTest].[X2]), SEEK:([IndexTest].[col2]=CONVERT(date,getdate()+CONVERT_IMPLICIT(datetime,[@2],0),0)) ORDERED FORWARD)
So as you can see, two separate index seeks are used, one on X1 and another one on X2. After the seeks a hash join is used to combine the results.
However, bear in mind that it's impossible to say if this is the correct approach for you. It depends on several factors like:
- What is estimated cardinality for your actual tables and columns
- What other kinds of statements you use
- Are the actual queries using joins and in which order
- And so on...