When to use with clause in sql

Question

Can Anybody tell me when to use with clause.

Answer 1

The WITH keyword is used to create a temporary named result set. These are called Common Table Expressions.

A very basic, self-explanatory example:

WITH Administrators (Name, Surname)
AS
(
    SELECT Name, Surname FROM Users WHERE AccessRights = 'Admin'
)
SELECT * FROM Administrators 

---

For further reading and more examples, I suggest starting out with the following MSDN article:

• Common Table Expressions by John Papa

Answer 2

In SQL Server you sometimes need the WITH clause to force a query to use an Index. This is often a necessity in spatial queries that can reduce query time from 1 minute to a few seconds.

select * from MyTable with(index(MySpatialIndex)) where...