--A.函数转换
--使用FULL JOIN,以前碰到的环境:有时写视图时将两表合并使用FULL JOIN 
--示例:
DECLARE @A TABLE(NAME VARCHAR(10) NOT NULL PRIMARY KEY,ID INT) 
INSERT @A 
SELECT 
 CASE WHEN NUMBER%2=0 THEN 'A'+LTRIM(NUMBER) ELSE 'B'+LTRIM(NUMBER) END , 
 NUMBER 
FROM 
 MASTER..SPT_VALUES 

WHERE TYPE='P' AND NUMBER BETWEEN 1 AND 10 

DECLARE @B TABLE(NAME VARCHAR(10) NOT NULL PRIMARY KEY,ID INT) 
INSERT @B 
SELECT 
 CASE WHEN NUMBER%2=0 THEN 'A'+LTRIM(NUMBER) ELSE 'C'+LTRIM(NUMBER) END, 
 NUMBER 
FROM 
 MASTER..SPT_VALUES 

WHERE TYPE='P' AND NUMBER BETWEEN 1 AND 20 

--1.先SUM统计后再FULL JOIN 连接（这里没有重复所以没SUM了，测试用） 
SELECT ISNULL(A.NAME,B.NAME) NAME,A.ID,B.ID 
FROM @A A FULL JOIN @B B ON A.NAME=B.NAME WHERE (A.NAME='A10' OR B.NAME='A10') 

SELECT ISNULL(A.NAME,B.NAME) NAME,A.ID,B.ID 
FROM @A A FULL JOIN @B B ON A.NAME=B.NAME WHERE ISNULL(A.NAME,B.NAME)='A10' 

--上面两语句逻辑相同,因函数转换导致失效(一般建立好视图后才查询SELECT * FROM V_NAME WHERE NAME=@NAME)

--解决办法:使用UNION ALL(上面两个NAME 列类型相同)

--2.用UNION ALL后再SUM 
SELECT NAME,SUM(AID) AID,SUM(BID) 
FROM 
( 
 SELECT NAME,ID AS AID,0 AS BID FROM @A 
 UNION ALL 
 SELECT NAME,0 ,ID FROM @B 
) T 
WHERE NAME='A10' 
GROUP BY NAME 
--------------------------------


--B.列类型转换
--1使用UNION ALL,以前遇到的环境如上示例
--示例:
SET SHOWPLAN_TEXT  ON
GO
DECLARE @A TABLE(COL VARCHAR(3) NOT NULL PRIMARY KEY)
INSERT @A 
SELECT 'A'
UNION ALL SELECT 'B'

--列长度 和 @A 不一致
DECLARE @B TABLE(COL VARCHAR(5) NOT NULL PRIMARY KEY)
INSERT @B 
SELECT 'ABCCC' UNION ALL SELECT 'BCFDF'

--列长度 和 @A 一致
DECLARE @C TABLE(COL VARCHAR(3) NOT NULL PRIMARY KEY)
INSERT @C 
SELECT 'ADD' UNION ALL SELECT 'BAD'


SELECT * FROM
(
 SELECT COL FROM @A
 UNION ALL
 SELECT COL FROM @B
)T
WHERE COL LIKE 'A%'

SELECT * FROM
(
 SELECT COL FROM @A
 UNION ALL
 SELECT COL FROM @C
)T
WHERE COL LIKE 'A%'
GO
SET SHOWPLAN_TEXT  ON
--结果:列类型不一致会导致类型转换,低向高精度转换,最后引起索引失效
GO
/*
StmtText                                                                                             
---------------------------------------------------------------- 

SELECT * FROM
(
 SELECT COL FROM @A
 UNION ALL
 SELECT COL FROM @B
)T
WHERE COL LIKE 'A%'

（所影响的行数为 1 行）

StmtText                                                                                                                                     
-------------------------------------------------------------------- 
  |--Concatenation
       |--Filter(WHERE:(like([Expr1002], 'A%', NULL)))
       |    |--Compute Scalar(DEFINE:([Expr1002]=Convert(@A.[COL])))
       |         |--Clustered Index Scan(OBJECT:(@A))
       |--Clustered Index Seek(OBJECT:(@B), SEEK:(@B.[COL] >= 'A' AND @B.[COL] < 'B'),  WHERE:(like(@B.[COL], 'A%', NULL)) ORDERED FORWARD)

（所影响的行数为 5 行）

StmtText                                                                                            
--------------------------------------------------------------- 

SELECT * FROM
(
 SELECT COL FROM @A
 UNION ALL
 SELECT COL FROM @C
)T
WHERE COL LIKE 'A%'

（所影响的行数为 1 行）

StmtText                                                                                                                                     
-------------------------------------------------------------------- 
  |--Concatenation
       |--Clustered Index Seek(OBJECT:(@A), SEEK:(@A.[COL] >= 'A' AND @A.[COL] < 'B'),  WHERE:(like(@A.[COL], 'A%', NULL)) ORDERED FORWARD)
       |--Clustered Index Seek(OBJECT:(@C), SEEK:(@C.[COL] >= 'A' AND @C.[COL] < 'B'),  WHERE:(like(@C.[COL], 'A%', NULL)) ORDERED FORWARD)

（所影响的行数为 3 行）

StmtText                 
------------------------ 
SET SHOWPLAN_TEXT  ON
*/


--2使用SQL语句时数值传参数处理
--示例:
SET SHOWPLAN_TEXT ON
GO
DECLARE @T TABLE(ID DECIMAL(10,2) PRIMARY KEY)
INSERT @T SELECT 1.23 UNION ALL SELECT 2.3

DECLARE @PARA DEC(12,2)
SET @PARA=2.3

SELECT * FROM @T WHERE ID=@PARA
SELECT * FROM @T WHERE ID=CONVERT(DEC(10,2),@PARA)
GO
SET SHOWPLAN