It is quite easy to give SQL a hint on which index to use for a query, but we should start with a warning: this is often not the right solution. The SQL server is likely not using your index because there is another more suitable index or it finds another way to optimise your query. In some rare scenarios an index hint may be the right solution, this post is for these rare scenarios and for testing purposes of course!
The short answer
The short answer, use the WITH keyword with an INDEX hint:
SELECT * FROM People WITH (INDEX(People_Name))
WHERE [Name] = 'Peter'
The longer answer
We start by defining a simple table of people with a name and lastname column - a casual example from school. We also add one row to it with my name and last name:
CREATE TABLE People (
[Id] INT PRIMARY KEY IDENTITY(1,1),
[Name] NVARCHAR(30) NOT NULL,
[Lastname] NVARCHAR(50) NOT NULL
)
INSERT INTO People ([Name], [Lastname]) VALUES ('Peter', 'Rasmussen')
We add an index called People_Name on the Name column, which we will later hint (force?) the SQL server to use:
CREATE INDEX People_Name ON [People] ([Name])
Using the WITH statement and providing an INDEX we can tell the SQL server to use our People_Name index, as seen below:
SELECT * FROM People WITH (INDEX(People_Name))
WHERE [Name] = 'Peter'
If you have other table hints than your index, for example lock level, you can specify these in the WITH clause as well:
SELECT * FROM People WITH (ROWLOCK, INDEX(People_Name))
WHERE [Name] = 'Peter'
In the above example it is quite silly to force it to use my index, as there is only 1 row in the database. If my database was to grow to millions of rows it might start using the index, and it most likely will start using it even without forcing it to use my index.
I hope this was what you were looking for, if it was or if it was not, let me know in the comments down below! :)