T-SQL常见基础疑点问答总结

SQL code

--建立测试环境
IF object_id('tb') IS NOT NULL
    DROP TABLE tb
GO

CREATE TABLE tb(id INT IDENTITY(1,1),v VARCHAR(10))
GO
INSERT tb SELECT 'a'
UNION ALL SELECT 'b'
INSERT tb SELECT 'x'
UNION ALL SELECT 'z'
GO

(1)字串变量当数据库对象用

SQL code

--=**********************************************
--为什么我执行下面的语句选不到正确数据
CREATE PROC p
(
@field VARCHAR(10),--字段名
@value VARCHAR(10) --值
)
AS
    SELECT * FROM tb WHERE @field=@value
GO

EXEC p 'v','a'
GO
DROP PROC p
GO

--为什么我执行下面语句报错

SQL code

CREATE PROC p
(
@table VARCHAR(10),--表名
@value VARCHAR(10) --值
)
AS
    SELECT * FROM @table WHERE v=@value

说明:
在这二个存储过程中，@table,@field,@value都被定义为varchar
第一个实际上执行的是两个变量的比较，它的作用相当于
IF @field=@value
SELECT * FROM tb
语法未错，意思上却大错特错

第二个实际上执行的是
SELECT * FROM 一个字串 WHERE v=@value
如何能从一个字串中查询结果集呢？错误的把字串当成表对象来理解.
请记住@table是个表名,它是个字段，而非表对象,不是object

可以用EXEC执行动态语句来解决，比如

SQL code

EXEC('SELECT * FROM [' + @table + '] WHERE v=''' + @value + '''')

(2)为什么我在执行一个批语句(可能是存储过程，
      也可能是个FUNCTION,也可能只是几条语句的组合)时，
      提示错误，我照着提示的错误，检查，但是那里没有报错啊
--=**********************************************
比如，上面的第二个存储过程,@table明明是存储过程的输入参数，它为什么提示我@table未定义
在上面，我已经讲了这句为什么出错的原因，
当然@table如果是表变量的话，那么那句select是不会有问题的，
但表变量不能用做输入参数。但它为什么这样提示呢?
这与sql内部机制有关,sql查询语句执行前先由命令解析器进行语法检查，如果语法检查未通过，
会扔出错误信息（通常这里的提示是精确的），
当语法检查通过，则将其编译为可执行的内部格式(查询树),
而非语法错误时，因为是执行时报错,执行期间是内部格式代码，只能扔出个大致错误信息.
了解了这一点，当您的sql语句报错后，先检查是否语法错误，
如果不是，那么需要仔细检查了，因为按着错误提示去找，很有可能兜圈子。
--=**********************************************

(3)为什么我明明定义了@n变量，却提示我变量不存在?

SQL code

--=**********************************************
DECLARE @field VARCHAR(10),@n INT
SET @field='v'
EXEC('SELECT @n=COUNT(*) FROM tb WHERE [' + @field + ']=''a''')

上面有三条语句:
rows语句
1定义两变量
2给@field赋值
3执行动态语句

那么这三条语句，在执行时进行语法检查和编译,大至为:

SQL code

DECLARE
SET
EXEC

这是编译后要执行三步操作，它们在一进程空间中进行编译。
随着语句的运行，exec内部的语句被购造成'select @n=count(*) from tb where v=''a'''
这时，EXEC要执行的这条语句，继续要被命令解析器进行检查和编译,
它的编译内存空间与外部这个批不同，术语我可能表达不太清楚，
只想说明一点，动态语句在执行过程中才被首次编译,所以在这个空间中，提示@n未定义.

解决方式，sqlserver为我们提供了sp_executesql来完成这个操作。
上面要完成的操作，可以改写为:

SQL code

DECLARE @field VARCHAR(10),@n INT,@sql NVARCHAR(4000)
SET @field='v'
SET @sql=N'SELECT @n=COUNT(*) FROM tb WHERE [' + @field + ']=''a'''
EXEC sp_executeSQL @sql,N'@n INT OUTPUT',@n OUTPUT
SELECT @n
--=**********************************************

(4)以下语句为什么提示我临时表不存在，明明我生成了临时表的
--=**********************************************

SQL code

EXEC('SELECT * INTO # FROM tb')
SELECT * FROM #

说明:
在上面解释@n为何未定义时说了关于编译时进程内空间的问题。
我们再联合局部临时表的生名周期，局部临时表在当间会话进程中有效
EXEC内部的语句是新开辟的一个进程空间执行的，所以当它执行完毕后，
生命周期结束，因此外部无法再访问

那为什么下面的又可以呢?

SQL code

SELECT * INTO # FROM tb
EXEC('SELECT * FROM #')
PRINT 'aa'
DROP TABLE #
--=********************************************

这个很明显,上面执行了三行，在一个批中,直到PRINT 'aa'结束后，
进程结束，#临时表才会被销毁，因为它的生命周期在整个会话中.
所以当EXEC执行时它仍存在

(5)为什么我给一个变量赋值，得到的不是我想要的,即为什么@v的值不是a,而是z

SQL code

--=**********************************************
DECLARE @v VARCHAR(10)
SELECT @v=v FROM tb
SELECT @v

说明
SELECT 变量=字段 FROM tb
这种赋值与SET赋值的主要区别:
a, SET赋值，一次只能给一个变量赋值，而SELECT 则可多个
b, SET赋值语义更明确，是赋值。而SELECT 可能是赋值也可能是数据查询
c, 最重要的一个,SELECT 可以从表中取值，而SET不能。
说到这有人会说 SET @v=(SELECT TOP 1 v FROM tb) 也可以,
这样确实可以，但实际上它还是调用SELECT来完成.
d, SELECT赋值时是滚动赋值(或许用词不科学），我来说明一下我的'滚动赋值'指的什么
即，当SELECT v FROM tb有多个结果时，
SELECT @v=v FROM tb 在产生结果集的过程中，每得到一条记录，
@v都被赋一次值，也就是说，语句会滚动结果集，每次都对@v赋值。
这样，也就产生了递规查询变量:"

SQL code

SELECT @v=@v+v FROM tb

这样就解释了为什么您的语句得到的结果为z而不是a
try:

SQL code

SELECT TOP 3 @v=v FROM tb --滚动到结果集的第三行,或者说，结果集中只有三行，取最后一行
--=**********************************************

(6)我要获得tb上新插入记录的标识值，为什么我的@@identity全局函数取的值不对?

SQL code

--=**********************************************
IF object_id('ta') IS NOT NULL
    DROP TABLE ta
CREATE TABLE ta(id INT IDENTITY(1,1),b_id INT,logTime DATETIME NOT NULL DEFAULT GETDATE())
GO
CREATE TRIGGER t ON tb
FOR INSERT
AS
    INSERT ta (b_id) SELECT id FROM INSERTED
GO

INSERT tb SELECT 'dd'
SELECT @@identity  --为什么这里得到的不是5,而是1?

说明:
@@identity是个全局函数，返回当前会话所有作用域最后产生的标识值(执行SELECT @@identity之前的最后)
我们可以看到ta上有insert触发器 t,触发器里对ta进行了insert，所以这里得到的是ta中新增的标识值
建议使用 SCOPE_IDENTITY

SQL code

INSERT tb SELECT 'ww'
SELECT SCOPE_IDENTITY() --这里得到6,因为它返回当前会话，当前作用域最后产生的标识值
--=**********************************************

(7)为什么我执行distinct去重复后没有效果?如下，我想得到v值不同的记录，也即结果集中每个v值只出现一次

SQL code

--=**********************************************
INSERT tb SELECT 'a'
UNION ALL SELECT 'b'
SELECT * FROM tb  -- 8 rows
SELECT DISTINCT v,id FROM tb --8 rows
--=**********************************************

出现这个意外，您需要注意两点:
1,DISTINCT 有效范围是后面的字段列表,而非紧告其的第一个字段
2,为什么DISTINCT 会有这么个奇怪的特点？这与二维关系统有关
就说tb中v=a的记录id有1,7
DISTINCT v,id 如果仅对v有效，那么引id有1,7,在一行一列中如何填充数据？是用1还是用7?
您太难为它了，因为它不知道如何为您将id填充到结果集中.
其实这个问题就是常见的同组取一条，那么你需要指定多一个条件，
每组(v相同的记录为一组)取id最大的或最小的，那么这样的语句，写的很多，论坛上问的也很多了，我就不再累赘了。

SQL code

/*
    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
GO

CREATE TABLE tb(id INT IDENTITY(1,1),cid INT,v VARCHAR(10))
GO

INSERT tb SELECT 1,1
UNION ALL SELECT 2,'bb'
UNION ALL SELECT 3,50
UNION ALL SELECT 3,50
GO

CREATE TABLE ta(id INT IDENTITY(1,1),cid INT,v VARCHAR(10))
GO

INSERT 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在背后为我们做了一些工作。
*/