Are table hints a sub-type of query hints?

Question

Are table hints a sub-type of query hints?

Golfer22

SSCrazy

Points: 2256
More actions
February 14, 2012 at 10:43 am

#256694

A table hint would be used in a T-SQL query. So table hints would then be a sub-type of query hint, right?

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Yes. Why are you asking?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Paul White SSC Guru Points: 150467 More actions · Answer 2

Rowles (2/14/2012)
A table hint would be used in a T-SQL query. So table hints would then be a sub-type of query hint, right?

I'm not sure I would agree. See Query Hints (Transact-SQL) and Table Hints (Transact-SQL) for some of the differences. Also:

CREATE TABLE #t (col1 integer UNIQUE);

INSERT #t (col1) VALUES (1), (2), (3);

-- Query hint specifies a table hint

SELECT * FROM #t AS t WHERE t.col1 = 2

OPTION (TABLE HINT (t, FORCESEEK));

-- Error 8722

-- Cannot execute query. Semantic affecting hint 'tablock' appears

-- in the 'TABLE HINT' clause of object 't' but not in the corresponding

-- 'WITH' clause. Change the OPTION (TABLE HINTS...) clause so the

-- semantic affecting hints match the WITH clause.

SELECT * FROM #t AS t WHERE t.col1 = 2

OPTION (TABLE HINT (t, FORCESEEK, TABLOCK));

-- No error

SELECT * FROM #t AS t WITH (TABLOCK) WHERE t.col1 = 2

OPTION (TABLE HINT (t, FORCESEEK, TABLOCK));

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

drew.allen SSC Guru Points: 76985 More actions · Answer 3

SQL Kiwi (2/16/2012)
Rowles (2/14/2012)
A table hint would be used in a T-SQL query. So table hints would then be a sub-type of query hint, right?
I'm not sure I would agree. See Query Hints (Transact-SQL) and Table Hints (Transact-SQL) for some of the differences

Well, quoting from your own link. (Emphasis added.)

<query_hint > ::=
{ { HASH | ORDER } GROUP
| { CONCAT | HASH | MERGE } UNION
| { LOOP | MERGE | HASH } JOIN
| FAST number_rows
| FORCE ORDER
| MAXDOP number_of_processors
| OPTIMIZE FOR ( @variable_name { UNKNOWN | = literal_constant } [ , ...n ] )
| OPTIMIZE FOR UNKNOWN
| PARAMETERIZATION { SIMPLE | FORCED }
| RECOMPILE
| ROBUST PLAN
| KEEP PLAN
| KEEPFIXED PLAN
| EXPAND VIEWS
| MAXRECURSION number
| USE PLAN N'xml_plan'
| TABLE HINT ( exposed_object_name [ , <table_hint> [ [, ]...n ] ] )
}

It seems pretty clear that a table hint is a type of query hint.

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

Paul White SSC Guru Points: 150467 More actions · Answer 4

drew.allen (2/20/2012)
Well, quoting from your own link [...] It seems pretty clear that a table hint is a type of query hint.

Well, before SQL Server 2008, there was no TABLE HINT query hint option, so was it still 'pretty clear' then? Besides, just because we can (now) specify (most) table hints as query hints does not make the former a sub-type of the latter. I provided an example of one of the limitations to this new-ish usage, others include table hints that can only be used with INSERT...BULK (e.g. the KEEPIDENTITY table hint).

My own view is that table and query hints are sufficiently different things that I do not regard them as existing in a hierarchy (another question would be where join hints fit in this supposed hierarchy - Books Online does not arrange things that way).

Feel free to disagree, the question asked for opinions.

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi