Elaborating on what Erland's said, "it depends" much on the queries as to what type of index is most beneficial and why. The optimizer generally chooses the index and plan that can return results as efficiently as possible, considering the overall costs from the candidate plans.
Consider this simple table and query examples.
CREATE TABLE dbo.YourTable(
YourTableID int NOT NULL
CONSTRAINT PK_YourTable PRIMARY KEY CLUSTERED
,CreateTimestamp datetime2(7) NOT NULL
INDEX idx_YourTable_CreateTimestamp NONCLUSTERED
,Name varchar(30) NOT NULL
);
GO
WITH
t10 AS (SELECT n FROM (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) t(n))
,t10k AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 0)) AS row_num FROM t10 AS a CROSS JOIN t10 AS b CROSS JOIN t10 AS c CROSS JOIN t10 AS d)
INSERT INTO dbo.YourTable(YourTableID, CreateTimestamp, Name)
SELECT row_num, DATEADD(minute, (row_num -1) / 5, '2021-01-01T00:00:00'), 'Name' + CAST(row_num AS varchar(10))
FROM t10k;
UPDATE STATISTICS dbo.YourTable;
GO
Without a WHERE clause, SQL Server chooses the narrowest index that covers the query:
--Clustered index scan to return all rows and columns. Clustered index scan avoids the key lookup for each row to retrieve the Name column if the non-clustered index were scanned.
SELECT YourTableID, CreateTimestamp, Name FROM dbo.YourTable;
--Nonclustered index scan to return subset of columns because the leaf nodes include the CreateTimestamp index key plus the clustered index key as the row locator (covering index).
SELECT YourTableID, Name FROM dbo.YourTable;
When a WHERE clause is used, the optimizer estimates row counts and costs of candidate plans using avaialble indexes and chooses the one it estimates to be least costly:
--Clustered index seek to return a single row with all columns by primary key. Clustered index seek avoids the key lookup to retrieve the Name column if the non-clustered index were used.
SELECT YourTableID, CreateTimestamp, Name FROM dbo.YourTable WHERE YourTableID = 1;
--Clustered index scan to return a single row with all columns. No useful index exists on Name. Clustered index scan avoids the key lookup for each row to retrieve the Name column if the non-clustered index were scanned.
SELECT YourTableID, CreateTimestamp, Name FROM dbo.YourTable WHERE Name = 'Name1';
--Non-clustered index seek plus key lookup to return 5 rows with all columns. The key lookup is less costly with few rows.
SELECT YourTableID, CreateTimestamp, Name FROM dbo.YourTable WHERE CreateTimestamp = '2021-01-01T00:00:00';
--Non-clustered index seek plus key lookup to return 10 rows with all columns. The key lookup is less costly with few rows.
SELECT * FROM dbo.YourTable WHERE CreateTimestamp BETWEEN '2021-01-01T00:00:00' AND '2021-01-01T00:01:00';
--Clustered index scan to return 30 rows with all columns. Scan avoids costly key lookup when more rows are returned.
SELECT * FROM dbo.YourTable WHERE CreateTimestamp BETWEEN '2021-01-01T00:00:00' AND '2021-01-01T00:05:00';
A good indexing strategy needs to consider commonly executed queries and the overall workload. There are tradeoffs involved in storage and maintenance costs. Again, "it depends".