Query Hints – FORCE ORDER


SQL Query Optimizer basically determines most efficient way to execute given query by considering several query plans. Query Hints tell the optimizer to apply hints throughout execution of query. Here we will talk about FORCE ORDER query hint.

Example Script:

CREATE TABLE DIM_1(
ROWID INT IDENTITY(1, 1) NOT NULL,
CODE NVARCHAR(50) NULL
)
CREATE TABLE DIM_2(
ROWID INT IDENTITY(1, 1) NOT NULL,
CODE NVARCHAR(50) NULL
)

CREATE TABLE FACT(
ROWID INT IDENTITY(1, 1) NOT NULL,
VALUE_1 NVARCHAR(50) NOT NULL,
VALUE_2 NVARCHAR(50) NOT NULL,
DIM_1_ID INT,
DIM_2_ID INT
)

SELECT *
FROM FACT t1
JOIN DIM_1 t2 ON t1.DIM_1_ID = t2.ROWID
JOIN DIM_2 t3 ON t1.DIM_2_ID = t3.ROWID

Query without hint:

Capture

If you observed SQL Server optimizer rearranges joins order that it thinks will be optimal for your query to execute.

Query with hint:

SELECT *
FROM FACT t1
JOIN DIM_1 t2 ON t1.DIM_1_ID = t2.ROWID
JOIN DIM_2 t3 ON t1.DIM_2_ID = t3.ROWID
OPTION (FORCE ORDER)

Capture1.PNG

When you put this query hint on to your query, it tells SQL Server that when it executes the statement to not change the order of the joins in the query. It will join the tables in the exact order that is specified in the query.

The FORCE ORDER query hint is only used when you want to override the way that SQL Server wants to execute this query.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s