T-SQL常见基础疑点问答整理(2)
(1)请参见:
http://topic.csdn.net/u/20080726/23/45568f53-069f-472e-80b0-1361a72109de.html
贴子只代表个人看法和观点，仅作交流使用，如有错误，敬请指正。
/*
T-SQL常见基础疑点整理(2)
由fcuandy整理，而非原创,原创都是MS
2008-7-27
*/
IF object_id('tb') IS NOT NULL
DROP TABLE tb
GO
IF object_id('ta') IS NOT NULL
DROP TABLE ta
GOCREATE TABLE tb(id INT IDENTITY(1,1),cid INT,v VARCHAR(10))
GOINSERT tb SELECT 1,1
UNION ALL SELECT 2,'bb'
UNION ALL SELECT 3,50
UNION ALL SELECT 3,50
GOCREATE TABLE ta(id INT IDENTITY(1,1),cid INT,v VARCHAR(10))
GOINSERT ta SELECT 1,1
UNION ALL SELECT 2,'bb'
UNION ALL SELECT 3,50
GO--*************************************************
--(1)与UNION相关的常见问题
SELECT * FROM ta
UNION
SELECT * FROM tb
/*4 rows
未指定ALL,ta.row1与tb.row1重复,....而 tb.row4的id=4,无重复记录，所以是4条
*/SELECT * FROM ta
UNION ALL
SELECT * FROM tb
/*7 rows
指定ALL并集得到二表中所有行
*/
SELECT DISTINCT cid,v FROM ta
UNION
SELECT DISTINCT cid,v FROM tb
/*3 rows
ta中distinct cid,v 有三行，tb中dsitinct cid v也有三行，而这三行又一一对应重复，所以结果为三行
*/
SELECT DISTINCT cid,v FROM ta
UNION ALL
SELECT DISTINCT cid,v FROM tb
/*6 rows
ta中distinct cid,v 有三行，tb中dsitinct cid v也有三行，而这三行又一一对应重复,但指定了ALL,则二结果集全合并，得到6行
*/
SELECT cid,v FROM ta
UNION
SELECT cid,v FROM tb
/*3 rows
select cid,v from tb得到4行,其中三四两行重复。 ta得到三行，而前两行与ta的结果集前两行一一对应，第三行与tb的三四行重复，得到3行。
*/
/*
----------------------
SELECT 1,2,3
UNION
SELECT 1,32,34,4
----------------------
错，因为union进行并集时，列数需要相同，且类型一致或可隐式转换
*//*
SELECT 1,2,'a'
UNION
SELECT 2,4,1
错，'a'无法隐式转换为int, 当多个（不同类型)操作数进行运算时系统总是尝试将低精度转换为高精度，以减少转换的精度损失. 可参考改用
----------------------
SELECT 1,2,'a'
UNION
SELECT 2,4,'1'
----------------------
或者
RTRIM(1)或CAST(1 AS VARCHAR(10))等方式来强制转换。可以参见下例:
*/
DECLARE @i DECIMAL(10,2),@n INT
SELECT @i=1.00,@n=5
SELECT @n * @i
/*结果为5.00而不是5*/
DECLARE @x VARCHAR(10),@y INT
SELECT @x='2',@y=3
SELECT @x+@y
/*为何结果是5而不是23? 上面已说明了，隐式向高精确转换的问题*/
SELECT @x + RTRIM(@y)
/*这里是23*/--为何我指定了ORDER BY ，但是老报错?
/*
SELECT * FROM ta ORDER BY cid DESC
UNION ALL
SELECT * FROM tb ORDER BY cid DESC
*/
/*
union操作符中，ORDER BY 只能放在最后一个select statement后，它对合集有效.
可改写为
*/
SELECT * FROM ta
UNION ALL
SELECT * FROM tb
ORDER BY cid DESC
/*
问：这种写法是没有语法问题，但与我的本意不一致了，我是希望ta的在前面，然后才是tb的记录
以虚列解决
*/
SELECT *,idx=0 FROM ta
UNION ALL
SELECT *,idx=1 FROM tb
ORDER BY idx,cid DESC--*************************************************--*************************************************
--(2)我经常执行一些语句时，老是报错，怎么也找不到问题在哪?
/*
我执行:
EXEC master..xp_cmdshell 'bcp "select * from test.dbo.tb" queryout D:\test.txt -c -t, -T'
但是执行下面语句就报错
---------------------------
DECLARE @filePath VARCHAR(100)
SET @filePath = 'd:\test.txt'
EXEC master..xp_cmdshell 'bcp "select * from test.dbo.tb" queryout ' + @filePath + ' -c -t, -T'
---------------------------
因为xp_cmdshell后的命令字串是个常量字串，所以这样写不符合要求.如果@filePath是变量的话，可以用以下方式解决
双重exec嵌套
*/
DECLARE @filePath VARCHAR(100)
SET @filePath = 'd:\test.txt'
EXEC('
EXEC master..xp_cmdshell ''bcp "select * from test.dbo.tb" queryout ' + @filePath + ' -c -t, -T''
')
/*通常犯这类错误的还有:
----------------------
DECLARE @k INT
SET @k=1
EXEC sp_msforeachtable @command1='SELECT * FROM ? WHERE cid=' + RTRIM(@k),@whereand=' AND name IN (''ta'',''tb'')'
----------------------
@command1被要求为是常量，不能使用变量拼接,同样，采用exec嵌套
*/
DECLARE @k INT
SET @k=1
EXEC('
EXEC sp_msforeachtable @command1=''SELECT * FROM ? WHERE cid=' + @k + ''',@whereand='' AND name IN (''''ta'''',''''tb'''')''
')
/*
与此同时，还有一些问题，比如在第整理贴1里我提到sp_executeSQL,为什么这么写也报错?
----------------------
DECLARE @field VARCHAR(10),@m INT,@sql VARCHAR(2000)
SET @field='cid'
SET @sql=N'SELECT @n=COUNT(*) FROM tb WHERE [' + @field + ']=1'
EXEC sp_executeSQL @sql,N'@n INT OUTPUT',@m OUTPUT
SELECT @m
----------------------
仔细看sp_excutesql的联机手册说明,它的参数都被要求为n系参数，ntext,nvarchar,nchar等等可以转换为ntext类型的参数.
那么改写很容易:
*/
DECLARE @field VARCHAR(10),@m INT,@sql NVARCHAR(4000)
SET @field='cid'
SET @sql=N'SELECT @n=COUNT(*) FROM tb WHERE [' + @field + ']=1'
EXEC sp_executeSQL @sql,N'@n INT OUTPUT',@m OUTPUT
SELECT @m
/*
将@sql声明为nvarchar即可. 这里我将2000的长度改为4000是因为:
您将@sql定义为varchar(2000),意思是2000的varchar够长度存储要执行的命令串，但改为nvarchar后由双字节存储，存储量小了一半，如果仍是2000,那么可能不能满足要求
*//*此类问题总结:
这类问题，您需要仔细查看相关的手册细看它的要求。
至于xp_cmdshell,sp_msforeachtable为什么参数要求常，sp_executeSQL为什么必须要ntext参数, 我只能说，这是人家规定的，用人家的东西就照人家的要求做, 具体为什么，我也不知道，跟它们的内部实现机制有关吧。
当然了，sp_msforeachtable 有这个存储过程的源代码，有兴趣的可以看看找出为什么。
就像我很多年前刚学计算机时，总是入不了门，为什么 dir 就可以得到磁盘上的目录文件列表，而不是list? 数学，物理上的结果可以推算出来的，这个为什么是这样？ 想搞懂这个问题，弄得我糊涂了半年^^
*/
--*************************************************--*************************************************
--(3)我在写语句时，遇到一些问题，语法未错，也不报错，但总是结果不是我想要的,排除(2)里描述的问题
----------------------------
DECLARE @s VARCHAR
SET @s='bb'
SELECT * FROM tb WHERE v=@s
/*
明明有v='bb'的记录为什么我得不到?在这一部分，我需要提醒的是一些基础细节问题造成的问题
@s被定义为varchar,而在delcare时，未指明长度，那么默认长度为1,您相当于定义：
DECLARE @s VARCHAR(1)
SET @s='bb' --因为长度只有1,则@s='b',后面不用我说了。
*/DECLARE @t TABLE(d DATETIME)
INSERT @t SELECT GETDATE()
UNION ALL SELECT '2008-1-1'DECLARE @d DATETIME
SET @d=2008-5-1
SELECT * FROM @t WHERE DATEDIFF(yy,d,@d)=0
/*
明明有200年的记录，为什么我得不到结果?
那么在问这个问题前，您先查一下您的@d
*/
SELECT @d
/*
1905-06-26 00:00:00.000
为何是这样?
粗心，失之毫厘，差之千里,给@d赋值时少了定界符
SET @d=2008-5-1
实际上给了@d一个int值 SET @d=2002
以1900-1-1以dd为单位加上2002
*/
SELECT DATEADD(dd,2002,'1900-1-1')
/*
1905-06-26 00:00:00.000
*//*
这样的细节问题还有很多，需要自己写代码时细心加小心
*/
--*************************************************
--(4)我用了isnumeric过滤过了，但为什么还是提示我无法转换?
SELECT * FROM ta WHERE ISNUMERIC(v)=1 AND v>30
GO
/*
这里执行正常的
*/CREATE UNIQUE CLUSTERED INDEX idx_v ON ta(v)
GO
/*为什么 下面就出错了?一模一样的语句
----------------
SELECT * FROM ta WHERE ISNUMERIC(v)=1 AND v>30
----------------sqlserver执行基于成本的优化，在v上建立了聚集索引后,引发了查询计划的变更。
至于具体它如何操作的，它的执行顺序是怎么样的，您可以查看查询计划另外，这里也不是说只有建了聚集索引才会有这种情况发生，只是想说明,有时候我们想当然的认为它应该怎么样的，实际上并不是
所以，在写查询时我们一定要注意语句的适用性
*/
SELECT * FROM ta WHERE CASE WHEN ISNUMERIC(v)=1 THEN v ELSE 0 END >30
/*
除此之外，还有很多类似的情况，比如:
很多人总认为exists比in快
in(单值) 和 = 一样
等等
实际上很多情况下只有自己试了才知道,优化器在后面做了很多工作。 由具体的环境，具体的数据分布，具体的设置共同决定。
*/
--*************************************************
--*************************************************
--(4)通常一些情况，我老是弄不清。比如表值与标量值DECLARE @classes VARCHAR(100)
SET @classes='1,2'
SELECT * FROM tb WHERE v IN (@classes)
/*
@classes是个普通字串, v IN (这里应该是一个集合)
当然，一个字串，也即一个元素，也可以是一个集合，只不过集合中只有它一个元素，
也就是说 '1,2' 是做为一个整体的不可分割的最小单元(二维关系中我们不考滤集合嵌套的问题,如c#中
arraylist a 是个集合, b是a的一个元素，而b本身又是一个arraylist. 或者又如json或xml中的结构){'1,2'} 这是一个集合，它只有 '1,2' 这一个字串元素
它与 {1,2} 或 {'1','2'} 这两个集合是有本质区别的那么这时你知道了，你上面的写法相当于是 WHERE v = @classes, 所以取不到记录
SELECT * FROM tb WHERE cid=(SELECT cid FROM ta)
这句错，因为后者子查询返回一个集合,对于sql查询执行时，左侧外部查询(相对子查询而言)每一行的cid都是个标量或者说集合中的单个元素, 拿元素=集合，从逻辑来说已经错了。
而
*/
SELECT * FROM tb WHERE cid=(SELECT TOP 1 cid FROM ta)
/*
这句可以执行，因为 单个元素 与 只有单元素的集合 比较，从逻辑计算上来说是错的，但sqlserver在背后为我们做了一些工作。
*/
待续..
(1)请参见:
http://topic.csdn.net/u/20080726/23/45568f53-069f-472e-80b0-1361a72109de.html
贴子只代表个人看法和观点，仅作交流使用，如有错误，敬请指正。
/*
T-SQL常见基础疑点整理(2)
由fcuandy整理，而非原创,原创都是MS
2008-7-27
*/
IF object_id('tb') IS NOT NULL
DROP TABLE tb
GO
IF object_id('ta') IS NOT NULL
DROP TABLE ta
GOCREATE TABLE tb(id INT IDENTITY(1,1),cid INT,v VARCHAR(10))
GOINSERT tb SELECT 1,1
UNION ALL SELECT 2,'bb'
UNION ALL SELECT 3,50
UNION ALL SELECT 3,50
GOCREATE TABLE ta(id INT IDENTITY(1,1),cid INT,v VARCHAR(10))
GOINSERT ta SELECT 1,1
UNION ALL SELECT 2,'bb'
UNION ALL SELECT 3,50
GO--*************************************************
--(1)与UNION相关的常见问题
SELECT * FROM ta
UNION
SELECT * FROM tb
/*4 rows
未指定ALL,ta.row1与tb.row1重复,....而 tb.row4的id=4,无重复记录，所以是4条
*/SELECT * FROM ta
UNION ALL
SELECT * FROM tb
/*7 rows
指定ALL并集得到二表中所有行
*/
SELECT DISTINCT cid,v FROM ta
UNION
SELECT DISTINCT cid,v FROM tb
/*3 rows
ta中distinct cid,v 有三行，tb中dsitinct cid v也有三行，而这三行又一一对应重复，所以结果为三行
*/
SELECT DISTINCT cid,v FROM ta
UNION ALL
SELECT DISTINCT cid,v FROM tb
/*6 rows
ta中distinct cid,v 有三行，tb中dsitinct cid v也有三行，而这三行又一一对应重复,但指定了ALL,则二结果集全合并，得到6行
*/
SELECT cid,v FROM ta
UNION
SELECT cid,v FROM tb
/*3 rows
select cid,v from tb得到4行,其中三四两行重复。 ta得到三行，而前两行与ta的结果集前两行一一对应，第三行与tb的三四行重复，得到3行。
*/
/*
----------------------
SELECT 1,2,3
UNION
SELECT 1,32,34,4
----------------------
错，因为union进行并集时，列数需要相同，且类型一致或可隐式转换
*//*
SELECT 1,2,'a'
UNION
SELECT 2,4,1
错，'a'无法隐式转换为int, 当多个（不同类型)操作数进行运算时系统总是尝试将低精度转换为高精度，以减少转换的精度损失. 可参考改用
----------------------
SELECT 1,2,'a'
UNION
SELECT 2,4,'1'
----------------------
或者
RTRIM(1)或CAST(1 AS VARCHAR(10))等方式来强制转换。可以参见下例:
*/
DECLARE @i DECIMAL(10,2),@n INT
SELECT @i=1.00,@n=5
SELECT @n * @i
/*结果为5.00而不是5*/
DECLARE @x VARCHAR(10),@y INT
SELECT @x='2',@y=3
SELECT @x+@y
/*为何结果是5而不是23? 上面已说明了，隐式向高精确转换的问题*/
SELECT @x + RTRIM(@y)
/*这里是23*/--为何我指定了ORDER BY ，但是老报错?
/*
SELECT * FROM ta ORDER BY cid DESC
UNION ALL
SELECT * FROM tb ORDER BY cid DESC
*/
/*
union操作符中，ORDER BY 只能放在最后一个select statement后，它对合集有效.
可改写为
*/
SELECT * FROM ta
UNION ALL
SELECT * FROM tb
ORDER BY cid DESC
/*
问：这种写法是没有语法问题，但与我的本意不一致了，我是希望ta的在前面，然后才是tb的记录
以虚列解决
*/
SELECT *,idx=0 FROM ta
UNION ALL
SELECT *,idx=1 FROM tb
ORDER BY idx,cid DESC--*************************************************--*************************************************
--(2)我经常执行一些语句时，老是报错，怎么也找不到问题在哪?
/*
我执行:
EXEC master..xp_cmdshell 'bcp "select * from test.dbo.tb" queryout D:\test.txt -c -t, -T'
但是执行下面语句就报错
---------------------------
DECLARE @filePath VARCHAR(100)
SET @filePath = 'd:\test.txt'
EXEC master..xp_cmdshell 'bcp "select * from test.dbo.tb" queryout ' + @filePath + ' -c -t, -T'
---------------------------
因为xp_cmdshell后的命令字串是个常量字串，所以这样写不符合要求.如果@filePath是变量的话，可以用以下方式解决
双重exec嵌套
*/
DECLARE @filePath VARCHAR(100)
SET @filePath = 'd:\test.txt'
EXEC('
EXEC master..xp_cmdshell ''bcp "select * from test.dbo.tb" queryout ' + @filePath + ' -c -t, -T''
')
/*通常犯这类错误的还有:
----------------------
DECLARE @k INT
SET @k=1
EXEC sp_msforeachtable @command1='SELECT * FROM ? WHERE cid=' + RTRIM(@k),@whereand=' AND name IN (''ta'',''tb'')'
----------------------
@command1被要求为是常量，不能使用变量拼接,同样，采用exec嵌套
*/
DECLARE @k INT
SET @k=1
EXEC('
EXEC sp_msforeachtable @command1=''SELECT * FROM ? WHERE cid=' + @k + ''',@whereand='' AND name IN (''''ta'''',''''tb'''')''
')
/*
与此同时，还有一些问题，比如在第整理贴1里我提到sp_executeSQL,为什么这么写也报错?
----------------------
DECLARE @field VARCHAR(10),@m INT,@sql VARCHAR(2000)
SET @field='cid'
SET @sql=N'SELECT @n=COUNT(*) FROM tb WHERE [' + @field + ']=1'
EXEC sp_executeSQL @sql,N'@n INT OUTPUT',@m OUTPUT
SELECT @m
----------------------
仔细看sp_excutesql的联机手册说明,它的参数都被要求为n系参数，ntext,nvarchar,nchar等等可以转换为ntext类型的参数.
那么改写很容易:
*/
DECLARE @field VARCHAR(10),@m INT,@sql NVARCHAR(4000)
SET @field='cid'
SET @sql=N'SELECT @n=COUNT(*) FROM tb WHERE [' + @field + ']=1'
EXEC sp_executeSQL @sql,N'@n INT OUTPUT',@m OUTPUT
SELECT @m
/*
将@sql声明为nvarchar即可. 这里我将2000的长度改为4000是因为:
您将@sql定义为varchar(2000),意思是2000的varchar够长度存储要执行的命令串，但改为nvarchar后由双字节存储，存储量小了一半，如果仍是2000,那么可能不能满足要求
*//*此类问题总结:
这类问题，您需要仔细查看相关的手册细看它的要求。
至于xp_cmdshell,sp_msforeachtable为什么参数要求常，sp_executeSQL为什么必须要ntext参数, 我只能说，这是人家规定的，用人家的东西就照人家的要求做, 具体为什么，我也不知道，跟它们的内部实现机制有关吧。
当然了，sp_msforeachtable 有这个存储过程的源代码，有兴趣的可以看看找出为什么。
就像我很多年前刚学计算机时，总是入不了门，为什么 dir 就可以得到磁盘上的目录文件列表，而不是list? 数学，物理上的结果可以推算出来的，这个为什么是这样？ 想搞懂这个问题，弄得我糊涂了半年^^
*/
--*************************************************--*************************************************
--(3)我在写语句时，遇到一些问题，语法未错，也不报错，但总是结果不是我想要的,排除(2)里描述的问题
----------------------------
DECLARE @s VARCHAR
SET @s='bb'
SELECT * FROM tb WHERE v=@s
/*
明明有v='bb'的记录为什么我得不到?在这一部分，我需要提醒的是一些基础细节问题造成的问题
@s被定义为varchar,而在delcare时，未指明长度，那么默认长度为1,您相当于定义：
DECLARE @s VARCHAR(1)
SET @s='bb' --因为长度只有1,则@s='b',后面不用我说了。
*/DECLARE @t TABLE(d DATETIME)
INSERT @t SELECT GETDATE()
UNION ALL SELECT '2008-1-1'DECLARE @d DATETIME
SET @d=2008-5-1
SELECT * FROM @t WHERE DATEDIFF(yy,d,@d)=0
/*
明明有200年的记录，为什么我得不到结果?
那么在问这个问题前，您先查一下您的@d
*/
SELECT @d
/*
1905-06-26 00:00:00.000
为何是这样?
粗心，失之毫厘，差之千里,给@d赋值时少了定界符
SET @d=2008-5-1
实际上给了@d一个int值 SET @d=2002
以1900-1-1以dd为单位加上2002
*/
SELECT DATEADD(dd,2002,'1900-1-1')
/*
1905-06-26 00:00:00.000
*//*
这样的细节问题还有很多，需要自己写代码时细心加小心
*/
--*************************************************
--(4)我用了isnumeric过滤过了，但为什么还是提示我无法转换?
SELECT * FROM ta WHERE ISNUMERIC(v)=1 AND v>30
GO
/*
这里执行正常的
*/CREATE UNIQUE CLUSTERED INDEX idx_v ON ta(v)
GO
/*为什么 下面就出错了?一模一样的语句
----------------
SELECT * FROM ta WHERE ISNUMERIC(v)=1 AND v>30
----------------sqlserver执行基于成本的优化，在v上建立了聚集索引后,引发了查询计划的变更。
至于具体它如何操作的，它的执行顺序是怎么样的，您可以查看查询计划另外，这里也不是说只有建了聚集索引才会有这种情况发生，只是想说明,有时候我们想当然的认为它应该怎么样的，实际上并不是
所以，在写查询时我们一定要注意语句的适用性
*/
SELECT * FROM ta WHERE CASE WHEN ISNUMERIC(v)=1 THEN v ELSE 0 END >30
/*
除此之外，还有很多类似的情况，比如:
很多人总认为exists比in快
in(单值) 和 = 一样
等等
实际上很多情况下只有自己试了才知道,优化器在后面做了很多工作。 由具体的环境，具体的数据分布，具体的设置共同决定。
*/
--*************************************************
--*************************************************
--(4)通常一些情况，我老是弄不清。比如表值与标量值DECLARE @classes VARCHAR(100)
SET @classes='1,2'
SELECT * FROM tb WHERE v IN (@classes)
/*
@classes是个普通字串, v IN (这里应该是一个集合)
当然，一个字串，也即一个元素，也可以是一个集合，只不过集合中只有它一个元素，
也就是说 '1,2' 是做为一个整体的不可分割的最小单元(二维关系中我们不考滤集合嵌套的问题,如c#中
arraylist a 是个集合, b是a的一个元素，而b本身又是一个arraylist. 或者又如json或xml中的结构){'1,2'} 这是一个集合，它只有 '1,2' 这一个字串元素
它与 {1,2} 或 {'1','2'} 这两个集合是有本质区别的那么这时你知道了，你上面的写法相当于是 WHERE v = @classes, 所以取不到记录
SELECT * FROM tb WHERE cid=(SELECT cid FROM ta)
这句错，因为后者子查询返回一个集合,对于sql查询执行时，左侧外部查询(相对子查询而言)每一行的cid都是个标量或者说集合中的单个元素, 拿元素=集合，从逻辑来说已经错了。
而
*/
SELECT * FROM tb WHERE cid=(SELECT TOP 1 cid FROM ta)
/*
这句可以执行，因为 单个元素 与 只有单元素的集合 比较，从逻辑计算上来说是错的，但sqlserver在背后为我们做了一些工作。
*/
待续..
看下200多位电脑专家的经验
http://www.200boy.com
set @s=replicate('1234567890',5)
select @s--输出(1 位) ：1
--在变量声明中使用 char 和 varchar 数据类型时，默认值长度为 1。select cast(replicate('1234567890',5) as varchar)--输出(30位)： 123456789012345678901234567890
--在 CAST 和 CONVERT 函数中使用 char 或 varchar 数据类型时，长度的默认值为 30。
是的，我是指在declare时。所以很多细节上的东西还是要注意。
MSN：[email protected]
e_mail:[email protected]
浪曦ASP.NET企业实战系列 http://down1.langsin.com/001.rar
浪曦NUnit详解视频 http://down1.langsin.com/002.rar
浪曦Struts 2应用开发详解 http://www.verycd.com/topics/210454
VIP视频：
浪曦Java常见笔试、面试题目深度剖析 http://down1.langsin.com/003.rar
浪曦J2EE测试实用指南http://down1.langsin.com/005.rar
浪曦Lucene视频教程 http://down1.langsin.com/006.rar
浪曦电脑维护项目 http://down1.langsin.com/007.rar
培训视频：
浪曦ExtJS视频之Grid控件 http://down1.langsin.com/008.rar
业务QQ：1050429531
GOINSERT tb SELECT 1,1
UNION ALL SELECT 2,'bb'
UNION ALL SELECT 3,50
UNION ALL SELECT 3,50东西还是做细一点比较好：记得给Varchar型数据加上单引号create table td(id int identity(1,1),cid int,v varchar(10))goINSERT td select 1,'1'
UNION all select 2,'bb'
UNION ALL SELECT 3,'50'
UNION ALL SELECT 3,'50'