UPDATE tab SET col1='XXX' WHERE col2='ZZZ'
UPDATE tab SET col1='XXX' FROM tab WHERE col2='ZZZ'
兩種寫法有什麼不同用處呢?
UPDATE tab SET col1='XXX' WHERE col2='ZZZ'
這種更新資料的語法, 相信大家應該都是依照如此的語法使用.
但是SQL Server 還可以使用下面的語法來達到相同的功能
UPDATE tab SET col1='XXX' FROM tab WHERE col2='ZZZ'
為什麼要這樣寫? 有什麼好處嗎?
答案是: 當執行計畫錯誤時, 執行Update Statistics後還是錯誤時, DBA可以在不修改程式的前提下, 透過Plan Guide加上Table hint來指定使用的索引
接著就讓我們來測試一下囉!
-- 首先移到測試資料庫
USE [TestDB]
GO
-- 建立測試Table
IF OBJECT_ID('tab1') IS NOT NULL DROP TABLE tab1
CREATE TABLE [dbo].[tab1](
[ID] [int] IDENTITY(1,1) NOT NULL,
[NAME] [varchar](20) NULL
)
-- 新增一些測試資料
SET NOCOUNT ON
DECLARE @LOOP INT=1, @NAME VARCHAR(20)
WHILE @LOOP <=1000
BEGIN
SET @NAME='TEST'+CONVERT(VARCHAR(3),@LOOP)
INSERT INTO tab1 VALUES (@NAME)
SET @LOOP += 1
END
SET NOCOUNT OFF
-- 建立兩個索引方便等一下查看比較執行計畫
CREATE INDEX tab1_IX1_ID ON tab1 (ID) INCLUDE (NAME)
CREATE INDEX tab1_IX2_NAME ON tab1 (NAME)
-- 測試SELECT Table Hint(記得打開實際執行計畫)
SELECT name FROM tab1 WHERE NAME='TEST10'
SELECT name FROM tab1 WHERE NAME='TEST10' OPTION (TABLE HINT(tab1, INDEX=tab1_IX1_ID))
--測試UPDATE 指令使用TABLE HINT 建立Plan Guide
--使用sp_get_query_template取得建立PlanGuide的SQL Statment及Parameters
DECLARE @stmt nvarchar(max);
DECLARE @params nvarchar(max);
EXEC sp_get_query_template
N'UPDATE tab1 SET name=''test'' WHERE name=''test1''',
@stmt OUTPUT,
@params OUTPUT;
-- @params varchar資料型態長度預設8000, 記得修改成20(請依實際欄位長度)
SELECT @stmt, @params
-- 若之前有測試資料請先刪除
EXEC sp_control_plan_guide @operation = N'DROP', @name = N'[UPDATE_tab1_planGuide]'
-- 建立 一般Table update 語法使用table hint 建立PlanGuide(會出現錯誤)
DECLARE @stmt nvarchar(1000)=N'update tab1 set name = @0 where name = @1'
DECLARE @params NVARCHAR(100) = N'@0 varchar(20),@1 varchar(20)'
EXEC sp_create_plan_guide
N'UPDATE_tab1_planGuide',
@stmt,
N'SQL',
NULL,
@params,
N'OPTION(TABLE HINT(tab1, INDEX=tab1_IX1_ID))';
發現會出現錯誤
-- 使用update from 建立 table hint 建立PlanGuide(正確)
DECLARE @stmt nvarchar(1000)=N'update tab1 set name = @0 from tab1 where name = @1'
DECLARE @params NVARCHAR(100) = N'@0 varchar(20),@1 varchar(20)'
EXEC sp_create_plan_guide
N'UPDATE_tab1_planGuide',
@stmt,
N'SQL',
NULL,
@params,
N'OPTION(TABLE HINT(tab1, INDEX=tab1_IX1_ID))';
-- 驗證是否正確用到Plne Guide建立的執行計畫(index: tab1_IX1_ID)
-- 記得打開實際執行計畫
SET STATISTICS IO, TIME ON
SET NOCOUNT OFF
exec sp_executesql N'update tab1 set name = @0 where name = @1',N'@0 varchar(20),@1 varchar(20)' ,@0='TEST99_1',@1='TEST99'
exec sp_executesql N'update tab1 set name = @0 from tab1 where name = @1',N'@0 varchar(20),@1 varchar(20)' ,@0='TEST99',@1='TEST99_1'
-- 刪除測試資料
DROP TABLE tab1
關於SQL Server查詢提示連結, 請參考