SQL技巧总结

1. Extracting query executor plan from Procedure Cache

WITH XMLNAMESPACES ('http://schemas.microsoft.com/sqlserver/2004/07/showplan' AS sql)
SELECT top 100
  C.value('@StatementId','INT') AS [No],
  C.value('(./@StatementText)','NVARCHAR(MAX)') AS [Statement Text],
  qplan.query_plan AS [Query Plan]
FROM (SELECT DISTINCT plan_handle FROM sys.dm_exec_query_stats) AS qstats
  CROSS APPLY sys.dm_exec_query_plan(qstats.plan_handle) AS qplan
  CROSS APPLY query_plan.nodes('/sql:ShowPlanXML/sql:BatchSequence/sql:Batch/sql:Statements/
descendant::*[attribute::StatementText]')
    AS T(C)
ORDER BY plan_handle, [No];

2. Get Index States:

---查看数据库中缺失的索引
select distinct DB_NAME(database_id) as DataBaseName,
       statement as table_name,
       equality_columns,
       inequality_columns,
       included_columns
from sys.dm_db_missing_index_details
where DB_NAME(database_id)='ASGDM'
order by statement,equality_columns,included_columns


---查看数据库中表索引使用情况
select DB_Name(database_id) as dbname,c.name as tablename,b.name as indexname,
       a.user_seeks,a.user_scans,a.user_lookups,
       a.last_user_seek,a.last_user_scan,a.last_user_lookup
from sys.dm_db_index_usage_stats a
inner join sys.indexes b on a.index_id=b.index_id and a.object_id=b.object_id
inner join sys.objects c on a.object_id=c.object_id
where DB_Name(database_id)='ASGDM' and b.index_id>=1
order by c.name

3. SMSS启用丢弃结果：

4. 修改数据库的兼容级别
exec sp_dbcmptlevel dbname, dbversion;
5. 1.*impression/click --> int 转化为 float

6. select into 快于 insert select；left join is null  = not exists

7.  控制执行顺序：拆分、括号、Hint

8. 递归CTE：对CTE名称的内部引用代表“前一个结果集”

9.  实际执行计划也不一定可靠
    执行计划不会捕捉下面语句的执行    set @comm = 'select ......'    insert into     exec(@comm)

　 执行计划的显示的percentage有时和实际不相符，最好还是利用Set statistics time on来获取执行时间

10. 最先应用最有效的过滤/连接

11. TABLESAMPLE select * from orders TABLESAMPLE (1000 rows)

12. HINT
   with index     

   option( use plan ...

13. Except和Not Exist对Null值处理逻辑不同；

14. Order By应用Case：

Order by col1,
case when col1 = 1 then orderID end,
case when col1 = 2 then orderdate end desc;

15. Insert/Update/Delete Output

--Insert
INSERT INTO dbo.CustomersDim(CustomerID, CompanyName)
    OUTPUT inserted.CustomerID, inserted.KeyCol
    INTO @NewCusts
    -- OUTPUT inserted.CustomerID, inserted.KeyCol
  SELECT CustomerID, CompanyName
  FROM Northwind.dbo.Customers
  WHERE Country = N'UK';

SELECT CustomerID, KeyCol FROM @NewCusts;

--Update
UPDATE TOP(@n) dbo.Messages WITH(READPAST) SET status = 'open'
    OUTPUT inserted.msgid, inserted.msgdate, inserted.msg INTO @Msgs
    OUTPUT inserted.msgid, inserted.msgdate, inserted.msg
  WHERE status = 'new';

--Delete
DELETE TOP(5000) FROM dbo.LargeOrders
      OUTPUT deleted.* INTO dbo.OrdersArchive
    WHERE OrderDate < '20010101';

16. Delete Top --一次删除太多记录比较慢，使用DELETE TOP可以一部分一部分删除

WHILE 1=1
BEGIN
  BEGIN TRAN
    DELETE TOP(5000) FROM dbo.LargeOrders
      OUTPUT deleted.* INTO dbo.OrdersArchive
    WHERE OrderDate < '20010101';
    IF @@rowcount < 5000
    BEGIN
      COMMIT TRAN
      BREAK;
    END
  COMMIT TRAN
END

17. CHECKSUM函数。如果我们需要在一个较大的字符串列上建立索引，我们可以使用CHECKSUM函数。CHECKSUM可以根据字符串产生一个4字节的整数，我们可以把索引建立在CHECKSUM的计算列上。当然，CHECKSUM并不能保证完全唯一，所以在查询时需要用到字符串列；

18. 最好把最具选择性的列放到索引最前面。