Two index of a table can be activated at the same time

Check out the estimated or actual execution plan in SSMS to see which index is being used:
Display the Estimated Execution Plan | Microsoft Docs[^]
Display an Actual Execution Plan | Microsoft Docs[^]

You can create an index on multiple columns, which might help. However, since you're selecting every column from the table, the query optimizer might decide - possibly incorrectly - that it's more efficient to scan the entire table, rather than seeking on the index and then performing a RID-lookup for each row.

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