The SET statement can set SQL Server query-processing options for the duration of your work session, or for the duration of a running trigger or a stored procedure.
The SET FORCEPLAN ON statement forces the optimizer to process joins in the same order as the tables appear in the FROM clause, similar to the ORDERED hint used with the Oracle optimizer.
The SET SHOWPLAN_ALL and SET SHOWPLAN_TEXT statements return only query or statement execution plan information and do not execute the query or statement. To execute the query or statement, set the appropriate showplan statement OFF. The query or statement will then execute. The SHOWPLAN option provides results similar to the Oracle EXPLAIN PLAN tool.
With SET STATISTICS PROFILE ON, each executed query returns its regular result set, followed by an additional result set that shows a profile of the query execution. Other options include SET STATISTICS IO and SET STATISTICS TIME.
Transact-SQL statement processing consists of two phases, compilation and execution. The NOEXEC option compiles each query but does not execute it. After NOEXEC is set ON, no subsequent statements are executed (including other SET statements) until NOEXEC is set OFF.
SET SHOWPLAN ON
SET NOEXEC ON
go
SELECT * FROM DEPT_ADMIN.DEPT,
STUDENT_ADMIN.STUDENT
WHERE MAJOR = DEPT
go
STEP 1
The type of query is SETON
STEP 1
The type of query is SETON
STEP 1
The type of query is SELECT
FROM TABLE
DEPT_ADMIN.DEPT
Nested iteration
Table Scan
FROM TABLE
STUDENT_ADMIN.STUDENT
Nested iteration
Table Scan