编写SQL需要注意的细节Checklist总结

/*
--注意：准备数据（可略过，非常耗时）
CREATETABLECHECK1_T1
(
IDINT,
C1CHAR(8000)
)

CREATETABLECHECK1_T2
(
IDINT,
C1CHAR(8000)
)

DECLARE@IINT
SET@I=1
WHILE@I<=10000
BEGIN
INSERTINTOCHECK1_T1SELECT@I,"C1"
INSERTINTOCHECK1_T2SELECT10000+@I,"C1"

SET@I=@I+1
END

CREATETABLECHECK2_T1
(
IDINT,
C1CHAR(8000)
)

DECLARE@IINT
SET@I=1
WHILE@I<=10000
BEGIN
INSERTINTOCHECK2_T1SELECT@I,"C1"

SET@I=@I+1
END

INSERTINTOCHECK2_T1VALUES(10001,"C2")

INSERTINTOCHECK2_T1VALUES(10002,"C1")

CREATETABLECHECK3_T1
(
IDINT,
C1CHAR(7000)
)

CREATETABLECHECK3_T2
(
IDINT,
C1CHAR(7000)
)

DECLARE@IINT
SET@I=1
WHILE@I<=20000
BEGIN
IF@I%2=0
BEGIN
INSERTINTOCHECK3_T1SELECT@I,"C1"
END
ELSE
BEGIN
INSERTINTOCHECK3_T1SELECT@I,"C2"
END

IF@I%100=0
BEGIN
INSERTINTOCHECK3_T2SELECT@I,"C1"
INSERTINTOCHECK3_T2SELECT@I+50000,"C2"
END
SET@I=@I+1
END


CREATETABLECHECK4_T1
(
IDINT,
C1CHAR(500),
)

DECLARE@IINT
SET@I=1
WHILE@I<=500000
BEGIN
IF@I%100000=0
BEGIN
INSERTINTOCHECK4_T1SELECT@I,"C2"
END
ELSE
BEGIN
INSERTINTOCHECK4_T1SELECT@I,"C1"
END

SET@I=@I+1
END
CREATENONCLUSTEREDINDEXNCIX_C1ONCHECK4_T1(C1)

CREATETABLECHECK5_T1
(
IDINT,
C1CHAR(10),
)


DECLARE@IINT
SET@I=1
WHILE@I<=10000
BEGIN
INSERTINTOCHECK5_T1SELECT@I,"C1"
IF@I%2=0
BEGIN
INSERTINTOCHECK5_T1SELECT@I,"C1"
END
SET@I=@I+1
END


*/
--=====================================
--1、Unionall代替Union

DBCCDROPCLEANBUFFERS
DBCCFREEPROCCACHE

--测试一：（26s）执行计划：表扫描->排序->合并联接
SELECTID,C1FROMCHECK1_T1--1W条数据
UNION
SELECTID,C1FROMCHECK1_T2--1W条数据

--测试二：(4s)执行计划：表扫描->表扫描串联
SELECTID,C1FROMCHECK1_T1--1W条数据
UNIONALL
SELECTID,C1FROMCHECK1_T2--1W条数据

--总结：测试一中的union排序和去重合并是相当耗时的，如果不要此功能，大数据时最好加上ALL

--=====================================
--2、Exists代替Count(*)
DBCCDROPCLEANBUFFERS
DBCCFREEPROCCACHE

----测试一:（7s）执行计划：表扫描->流聚合->计算矢量
DECLARE@COUNTINT
SELECT@COUNT=COUNT(*)FROMCHECK2_T1WHEREC1="C1"--1W条数据
IF@COUNT>0
BEGIN
PRINT"S"
END
----测试二:（0s）执行计划：常量扫描/表扫描->嵌套循环->计算标量
IFEXISTS(SELECT1FROMCHECK2_T1WHEREC1="C1")--1W条数据
BEGIN
PRINT"S"
END

--总结：判断是否存在，用Exist即可，没必要用COUNT(*)将表的所有记录统计出来，扫描一次

--=====================================
--3、IN（SelectCOL1FromTable）的代替方式
DBCCDROPCLEANBUFFERS
DBCCFREEPROCCACHE

--测试一：（3s）执行计划：表扫描->哈希匹配
SELECTID,C1FROMCHECK3_T2--400行
WHEREIDIN(SELECTIDFROMCHECK3_T1WHEREC1="C1")--2W行

--测试二：（1s）执行计划：表扫描->并行度->位图->排序->合并联接->并行度
SELECTA.ID,A.C1FROMCHECK3_T2A
INNERJOINCHECK3_T1BONA.ID=B.IDWHEREB.C1="C1"

--测试三：（3s）执行计划：表扫描->哈希匹配
SELECTA.ID,A.C1FROMCHECK3_T2A
WHEREEXISTS(SELECT1FROMCHECK3_T1BWHEREB.ID=A.IDANDB.C1="C1")

--总结：能用INNERJOIN尽量用它，SQLSERVER在查询时会将关联表进行优化

--=====================================
--4、NotExists代替NotIn
--测试一：（8s）执行计划：表扫描->嵌套循环->哈希匹配
SELECTID,C1FROMCHECK3_T1--2W行
WHEREIDNOTIN(SELECTIDFROMCHECK3_T2WHEREC1="C1")--400行

--测试二：（4s）执行计划：表扫描->哈希匹配
SELECTA.ID,A.C1FROMCHECK3_T1A
WHERENOTEXISTS(SELECT1FROMCHECK3_T2BWHEREB.ID=A.IDANDB.C1="C1")

--总结：尽量不使用NOTIN，因为会调用嵌套循环，建议使用NOTEXISTS代替NOTIN

--=====================================
--5、避免在条件列上使用任何函数

DROPTABLECHECK4_T1

CREATENONCLUSTEREDINDEXNCIX_C1ONCHECK4_T1(C1)--加上非聚集索引

---测试一：（4s）执行计划：索引扫描
SELECT*FROMCHECK4_T1WHERERTRIM(C1)="C2"

---测试二：（0s）执行计划：索引查找
SELECT*FROMCHECK4_T1WHEREC1="C2"

--总结：where条件里对索引字段使用了函数，会使索引查找变成索引扫描，从而查询效率大幅下降

--=====================================
--6、用sp_executesql执行动态sql

DBCCDROPCLEANBUFFERS
DBCCFREEPROCCACHE

CREATEPROCUP_CHECK5_T1(
@IDINT
)
AS
SETNOCOUNTON

DECLARE@countINT,
@sqlNVARCHAR(4000)

SET@sql="SELECT@count=count(*)FROMCHECK5_T1WHEREID=@ID"

EXECsp_executesql@sql,
N"@countINTOUTPUT,@IDint",
@countOUTPUT,
@ID

PRINT@count


CREATEPROCUP_CHECK5_T2(
@IDINT
)
AS
SETNOCOUNTON

DECLARE@sqlNVARCHAR(4000)

SET@sql="DECLARE@countINT;SELECT@count=count(*)FROMCHECK5_T1WHEREID="+CAST(@IDASVARCHAR(10))+";PRINT@count"

EXEC(@sql)


---测试一:瞬时
DECLARE@NINT
SET@N=1
WHILE@N<=1000
BEGIN
EXECUP_CHECK5_T1@N
SET@N=@N+1
END

---测试二:2s
DECLARE@NINT
SET@N=1
WHILE@N<=1000
BEGIN
EXECUP_CHECK5_T2@N
SET@N=@N+1
END

CREATECLUSTEREDINDEXCIX_IDONCHECK5_T1(ID)

DBCCDROPCLEANBUFFERS
DBCCFREEPROCCACHE

--查看缓存计划
SELECTa.size_in_bytes"占用字节数",
total_elapsed_time/execution_count"平均时间",
total_logical_reads/execution_count"逻辑读",
usecounts"重用次数",
SUBSTRING(d.text,(statement_start_offset/2)+1,((CASEstatement_end_offset
WHEN-1THENDATALENGTH(text)
ELSEstatement_end_offset
END-statement_start_offset)/2)+1)"语句"
FROMsys.dm_exec_cached_plansa
CROSSapplysys.dm_exec_query_plan(a.plan_handle)c,
sys.dm_exec_query_statsb
CROSSapplysys.dm_exec_sql_text(b.sql_handle)d
WHEREa.plan_handle=b.plan_handle
ORDERBYtotal_elapsed_time/execution_countDESC;

--总结：通过执行下面缓存计划可以看出，第一种完全使用了缓存计划，查询达到了很好的效果；
--而第二种则将缓存计划浪费了，导致缓存很快被占满，这种做法是相当不可取的

--=====================================
--7、LeftJoin的替代法
--测试一执行计划：表扫描->哈希匹配
SELECTA.ID,A.C1FROMCHECK3_T1A--2W行
LEFTJOINCHECK3_T2BONA.ID=B.IDWHEREB.C1="C1"--400行

--测试二执行计划：表扫描->哈希匹配
SELECTA.ID,A.C1FROMCHECK3_T1A
RIGHTJOINCHECK3_T2BONA.ID=B.IDWHEREa.C1="C1"

--测试三执行计划：表扫描->哈希匹配
SELECTA.ID,A.C1FROMCHECK3_T1A
INNERJOINCHECK3_T2BONA.ID=B.IDWHEREB.C1="C1"

--总结：三条语句，在执行计划上完全一样，都是走的INNERJOIN的计划，
--因为测试一和测试二中，WHERE语句都包含了LEFT和RIGHT表的字段，SQLSERVER若发现只要有这个表的字段，则会自动按照INNERJOIN进行处理

--补充测试：（1s）执行计划：表扫描->并行度->位图->排序->合并联接->并行度
SELECTA.ID,A.C1FROMCHECK3_T2A--400行
INNERJOINCHECK3_T1BONA.ID=B.IDWHEREA.C1="C1"--2W行
--总结：这里有一个比较有趣的地方，若主表和关联表数据差别很大时，走的执行计划走的另一条路

--=====================================
--8、ON(a.id=b.idANDa.tag=3)
--测试一
SELECTA.ID,A.C1FROMCHECK3_T1A
INNERJOINCHECK3_T2BONA.ID=B.IDANDA.C1="C1"

--测试二
SELECTA.ID,A.C1FROMCHECK3_T1A
INNERJOINCHECK3_T2BONA.ID=B.IDWHEREA.C1="C1"

--总结：内连接：无论是左表和右表的筛选条件都可以放到WHERE子句中

--测试一
SELECTA.ID,A.C1,B.C1FROMCHECK3_T1A
LEFTJOINCHECK3_T2BONA.ID=B.IDANDB.C1="C1"

--测试二
SELECTA.ID,A.C1,B.C1FROMCHECK3_T1A
LEFTJOINCHECK3_T2BONA.ID=B.IDWHEREB.C1="C1"

--总结：左外连接:当右表中的过滤条件放入ON子句后和WHERE子句后的结果不一样

--=====================================
--9、赋值给变量，加Top1
--测试一：（3s）执行计划：表扫描
DECLARE@IDINT
SELECT@ID=IDFROMCHECK1_T1WHEREC1="C1"
SELECT@ID

--测试二：（0s）执行计划：表扫描->前几行
DECLARE@IDINT
SELECTTOP1@ID=IDFROMCHECK1_T1WHEREC1="C1"
SELECT@ID

--总结：给变量赋值最好都加上TOP1，一从查询效率上增强，二为了准确性，若表CHECK1_T1有多个值，则会取最后一条记录赋给@ID

--=====================================
--10、考虑是否适合用CASE语句
DECLARE@SINT=1
SELECT*FROMCHECK5_T1
WHEREC1=(CASE@SWHEN1THENC1ELSE"C2"END)

SELECT*FROMCHECK5_T1
WHERE@S=1ORC1="C2"


/*--=====================================
、检查语句是否需要Distinct.执行计划：表扫描->哈希匹配->并行度->排序
selectdistinctc1fromCHECK3_T1
、禁用Select*，指定具体列名
selectc1fromCHECK4_T1
select*fromCHECK4_T1
、InsertintoTable（*）,指定具体的列名
、Isnull,没有必要的时候不要对字段使用isnull，同样会产生无法有效利用索引的问题，
和避免在筛选列上使用函数同样的原理。
、嵌套子查询，加上查询条件，确保子查询的结果集最小
--=====================================*/