MSSQL - How to force mssql to use a specific index for a query using an index hint

It is quite easy to give SQL a hint on which index to use for a query. I will however, start by warning you: 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 optimize your query. In some rare scenarios an index hint may be the right solution, this post is for these rare scenarios (and perhaps for testing).

We start by defining a simple table of people with a name and lastname column - a simple school example. 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 thousands 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! :)