mssql sqlserver option关键字应用说明


摘要:
option关键字的用途,为select delete update merge查询语句指定查询提示,如下所示:
实验环境:sql server 2008 R2



option关键字语法说明

OPTION ( [ ,…n ] )

::=
LABEL = label_name |

::=
HASH JOIN
| LOOP JOIN
| MERGE JOIN
| FORCE ORDER
| { FORCE | DISABLE } EXTERNALPUSHDOWN
——————–参数说明———————————
query_hint
关键字,指示优化器提示用于自定义数据库引擎处理语句的方式



option关键字应用

   create table [maomao365.com_A]
(keyId int,info varchar(30),
qty int) 


insert into [maomao365.com_A]
(keyId,info,qty)values
(1,'A',1),(1,'B',2),(2,'A',8),(3,'A',10),(3,'B',12)
go

SELECT a.keyId,a.info, SUM(a.qty) AS qtyAll  
FROM  [maomao365.com_A]  a  
WHERE qty >1    
GROUP BY a.keyId,a.info 
ORDER BY a.keyId,a.info
OPTION (HASH GROUP,FAST 10);

go
truncate table [maomao365.com_A]
drop     table [maomao365.com_A]
    


option关键字注意事项:
option关键字每次查询只能指定一次,但可多指定多个查询提示